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

Copper Contributor

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

 

15 Replies
I'm just bringing this to the top hoping for some kind of help,

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

"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






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

Also on this, it will always be just me using this application in two different locations.

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

 

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

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   

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

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.

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

@Karl Donaubauer 

 

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

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

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.

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

 

It seems to be working fine.

I want to thank so much for your time and effort on this.

If I run into any issues I'll let you know,