May 22 2023 11:56 AM - edited May 22 2023 11:58 AM
I have several Access databases that are linked together for various queries I run. They are located on my OneDrive. I want to use them on 2 different computers.
My main desktop computer was set up as "C:\users\admin\OneDrive\ " The databases are linked and all the queries have worked fine for many years.
I just bought a brand new laptop to keep at another location. It was automatically set up as "C:\users\wcrim\OneDrive\".
I can access all my OneDrive files on the new computer individually. However, my databases have linkages for many queries that point to the original "C:\users\admin\OneDrive\" locations for other the databases. So on the new computer, they can't be found. On the new computer it's set up as "C:\users\wcrim\OneDrive\"
I keep getting not found.
I was hoping to just be able to change the user name directory on the new computer to "admin", but it appears that can't done, at least not easily. That's what I was told.
What can be done so I can use these databases and all the queries on my new laptop computer also. I'm looking for an uncomplicated solution.
Thank You,
Wayne Crimi [Case #:1053617826] Microsoft Support
May 24 2023 07:27 AM
May 24 2023 08:09 AM
Hi,
1) Maybe one reason for not getting answers is that the description of the core problem isn't very clear:
> ... my databases have linkages for many queries that point to the original "C:\users\admin\OneDrive\" locations for other the databases.
Can you rephrase this in order to make clear which objects "link" in which way to which files or whatever?
Two general annotations:
2) You should not use Onedrive for concurrent user access because the caching is not suitable for Access databases. It should work if you are the only user and/or data is only written from 1 place at a time.
3) If there is a problem with different paths consider splitting the application in 2 files, which is a best practice anyway: The data=tables sit in the backend.accdb, all other parts of the application sit in frontend.accdbs. e.g. you could create 2 frontends with different paths to the backend for the 2 machines where you use the application. If this is the problem (?)... see item 1).
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
May 24 2023 08:47 AM
May 24 2023 08:49 AM
May 24 2023 09:05 AM
Hi,
> I hope that helps explain the situation.
Not really as you didn't answer my questions about the technical details of this "links". Therefore I can only guess that you are talking about linked tables that point to other accdb files.
If that's the case then you should be able to use the usual 2 methods for linked tables in frontend files: use the linked table manager to manually relink the tables or use some VBA code to do this automatically. There are many VBA examples for this in the web.
If instead you are talking about "links" really in the queries, i.e. hard coded references to external tables in the SQL text, then it may be a bit more difficult but also doable by code.
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
May 24 2023 09:17 AM
May 24 2023 09:21 AM - edited May 24 2023 09:25 AM
Here's what it looks like on the main computer (see attached).
I really only need a couple of those linked at the new location, but I'm reluctant to start messing around too much and potentially screwing things up at both locations and having to redo it. I have limited expertise in this linkage feature beyond this application. I don't want to create new problems. Thanks for your help so far. Wayne
May 25 2023 01:41 AM - edited May 25 2023 05:58 AM
Hi,
I have a client who has his frontend and backend always in the same folder but sometimes copies both files to different computers and thus different paths. So we relink the backend tables every time he starts the application to the folder path of the frontend. Your problem seems similar and could probably be solved = automated with a similar technique:
1. Create a new VBA module in the frontend.
2. Copy this function into the module:
Public Function fctLinkedTablePaths()
On Error GoTo myError
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strDbFile As String
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
strDbFile = Mid(tdf.Connect, InStrRev(tdf.Connect, "\"))
tdf.Connect = ";database=" & CurrentProject.Path & strDbFile
tdf.RefreshLink
End If
Next tdf
myExit:
Exit Function
myError:
Select Case Err.Number
Case 9999
'trap specific errors
Case Else
MsgBox "Exception No. " & Err.Number & ". " & Err.Description
Resume myExit
End Select
End Function
3. Create a new macro called: AutoExec
4. In the macro choose the action: RunCode
and as Function Name: =fctLinkedTablePaths()
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
May 25 2023 09:18 AM - edited May 25 2023 09:41 AM
Thanks for the reply.
VBA is over my head. I'm going to have to try to understand it first so I know if I have to change anything, but that sounds like a good solution for automating it.
Thanks for your help.
Wayne.
May 26 2023 12:22 AM
Hi,
You could create a copy of your frontend/main application file, do what I described and thus test if it works for you. The code only does things in this file. So the worst thing that can happen is that sth doesn't work in that copy.
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
May 26 2023 08:03 AM
I more or less understand how it's using the current frontend database and looking for the linkages, but I don't understand how it knows what to change them to. Is is basically just assuming everything its looking for is in the same directory as the main database?
Just trying to understand what its doing before I start testing.
Thanks
May 26 2023 08:27 AM - edited May 26 2023 08:29 AM
Hi,
> Is is basically just assuming everything its looking for is in the same directory as the main database?
Yes, the code sets the path of all linked tables in the current database to the path of the current database file where it is located and running (CurrentProject.Path).
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
May 27 2023 09:40 AM - edited May 27 2023 09:42 AM
I think I'm getting close to solving the problem.
I created the VBA and the Macro.
When I tried to execute it, it didn't find one of the databases that is linked to and errored out. I only needed a couple of them and didn't download all of them. So that makes sense. What it didn't do was continue and work for the ones that were available. I guess it just stopped. Can I assume that it was supposed to error out like that and once I download the others so everything is available and found it all should work?
I really really appreciate all the help you have given me. If there's something I can do in return please let me know.
May 31 2023 03:17 AM
Hi,
> Can I assume that it was supposed to error out like that and once I download the others so everything is available and found it all should work?
Yes. The error handling at the end of the code shows a message box with error number and message and then exits. You can change this behaviour in the part where it says:
Select Case Err.Number
Case 9999
'trap specific errors
by telling it to continue in case of the "table not available" error like this:
Select Case Err.Number
Case HereYouWriteTheErrorNumberYouSeeInTheMessagebox
Resume Next
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
Jun 02 2023 09:31 AM