Using Access Database on Two Computers [Case #:1053617826] Microsoft Support

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

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

WayneCrimi1
Copper Contributor
May 24, 2023
I'm just bringing this to the top hoping for some kind of help,
Karl_Donaubauer
MVP
May 24, 2023
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
- WayneCrimi1
  Copper Contributor
  May 24, 2023
  "Can you rephrase this in order to make clear which objects "link" in which way to which files or whatever?"
  
  Thanks for the response.
  
  On my main computer I have 3 access databases. One has some data and most of the queries I run and the other two have data. I link the main one with the others so they can share some information. I then run queries from the main one.
  
  When databases are linked together like that, they have to know where each other resides.
  
  On the main computer all the databases are located at: "C:\users\admin\OneDrive\........
  
  I just bought a laptop to be used in another location.
  
  I have access to those same databases via OneDrive on the new laptop, but that computer was set up as. C:\users\wcrim\OneDrive\........... That's where I see them.
  
  So what's happing is that I can get to each of the 3 individual databases, but they can't find each other when I try to run a query because they are pointing to C:\users\admin\OneDrive\ as the location.
  
  I hope that helps explain the situation.
  
  Thanks
  - Karl_Donaubauer
    MVP
    May 24, 2023
    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

Forum Discussion

Using Access Database on Two Computers [Case #:1053617826] Microsoft Support

Share

Resources