Forum Discussion

Copper Contributor

May 22, 2023

Solved

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 s...

access

office 365

Karl_Donaubauer

May 25, 2023

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

"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

WayneCrimi1
Copper Contributor
May 24, 2023
"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."

Yes. I am talking about linked tables in other accdb files. It's not hard coded in the queries.

I guess I am trying to avoid manually changing the link file locations depending on whether I am working on the main computer or laptop at another location. To be honest, it has been so long since I set this up and did that (2014 to be exact), I'd have to relearn how to do it. But if that's the best solution, I may have to do it that way.
- WayneCrimi1
  Copper Contributor
  May 24, 2023
  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
  - Karl_Donaubauer
    MVP
    May 25, 2023
    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