We help IT Professionals succeed at work.

Access VBA automaticcally relink to backend database when split database solution is copied to other machines

Murray Brown
Murray Brown asked
on
Hi
I have an Access database with linked tables in another Access file that is in the same folder called "Backend1.accdb".
When I distribute the Access database I want to automatically loop through and relink the tables to the new location of the backend
which will always be in the same folder as the current database. What VBA code would I use?
Comment
Watch Question

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

Murray,


Durring development, it's OK to have the FE and BE in the same folder, preferably on your PC, or you can put the FE on your computer and the BE on a network file server.  But when you deploy the application, every user should have their own copy of the FE of the split database, on their PC.  The BE should be on a network file server, and when you link the FE to the BE, you should use the fully qualified network name:


\\ServerName\Folder\folder\appname_be.accdb


as the path.  That way, when you copy the FE to each users computer, you will not have to worry about them having the wrong drive name mapped to a particular letter.