We have an Access db that has around 300 linked tables (to SQL Server tables). To switch environments, we run some VBA code that re-links all these tables to the relevant server. But this seems to be very slow. Approximately 0.25 seconds for each table, so the whole process can take almost 2 minutes.
I thought that I could use the Split Database feature to create a backend db that has linked tables that link to the dev environment, and another backend db that has linked tables to the live environement. Then the process of changing environments would be to programmatically tell the front-end db which back-end db to use.
However, when I tried the Split Database wizard, the back-end db it created has no linked tables, only the local tables.
Has anyone got some suggestion as to achieve that I'm after?