1

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?

Improve this question
4
  • 1
    The table cannot be re-linked into one database via another.
    – Akina
    Jul 22, 2019 at 13:40
  • The purpose of Split Database is only to move Access tables to another backend database file. Having linked tables to a server is already "split" in that sense.
    – C Perkins
    Jul 22, 2019 at 15:44
  • If you're linking so many tables and switching "environments", I'd try rethinking the overall design. Unless you have very simple direct binding to individual tables and only editing individual rows, I can't imagine that the design is optimal. Access SQL is rather limited and queries with many joins on linked tables are likely not efficient. I'm surprised that the only slowdown is with 2 minute re-linking. When switching environments, is the database / Access UI used identically? Or are there different processes, like only updating the server with "offline" data?
    – C Perkins
    Jul 22, 2019 at 15:52
  • Thank you for your comments
    – Steve
    Jul 23, 2019 at 15:22

1 Answer 1

Reset to default
1

I have solved the problem. Within the loop through the 300 tables, the code was creating a new ADOX.Catalog object, appending a new linked table, then setting the catalog object to nothing.

I made one simple change: create the catalog object before the loop, and set it to nothing after the loop. After this change, the relinking takes only about 10 seconds.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.