I thought it might be worth expanding on my previous answers.
My approach has much in common with that outlined by Minty
It is several years since I last used the wizard to setup file or machine data source ODBC connections 'manually' - I just have no reason to do so
I always use DSN-less connection strings as that was how I first learned to manage split databases for distribution to clients many years ago.
Until recently, I thought this was the standard approach and to me it is far easier than setting up ODBC connections manually on each machine. In fact it would be impossible to work any other way without visiting each of my clients' sites. I have several clients across the UK who I have never visited because it isn't necessary
To manage the external links I have 2 local tables:
1. tblTableLinkTypes - lists details of all connections to external databases
2. tblTableLinks - lists all the linked tables and the external databases for each
Typically, my FE databases will have links to 2 or more external databases
a) a SQL Server and/or Access BE datafile
b) an Access configuration datafile (used to store settings for each client e.g. school/company)
Some apps also have links to Excel files and/or csv files
However, many have more link types. The screenshot shows the relink tables form for a database with 8 external links (including 2 web databases - now deprecated) though in this example not all are in use at any time
The next screenshot shows a form where details for each link type can be edited
As you can see, this shows 35 records as it includes all link details for each client
Another form is used to edit the details of each linked table.
In this case there are 147 linked tables
The largest database has about 330 linked tables - almost all are in the SQL datafile
If linked tables are added/deleted or renamed e.g. to manage new features in a version update, this is easily handled using this form
Before apps are distributed to clients via my website, all links are removed from the FE.
For a new install, clients are guided through the process of adding link types (SQL or Access) and the form in the first screenshot is used to relink all tables.
Details of the link types for that client are added to the Access configuration file and are automatically emailed to me for inclusion in future updates
For a version update, the link types details are recovered from the config file and the links recreated.
Relinking takes less than 30 seconds for the largest database of around 330 tables
Of course, the relinking needs to be done on one PC only and the new/updated FE is then distributed to users' workstations without any additional configuration needed on each user's workstation. From memory, I have only once needed to assist a client with obtaining new SQL Server drivers. Normally, the required drivers are already installed on the client PC so the process is brainless
To me, this is so straightforward that I find it hard to understand why it isn't the standard approach used by all developers. If there is an easier way of managing split databases for use with with many clients each having multiple workstations, I would like to know about it!
HTH
