Access VBA Re-Linking Tables with Password is SLOW

donaldfisher3 · 2024-06-10T14:18:11.442Z

I have a function that iterates through linked tables and links them to a specified back-end using the following line:

db.TableDefs(i).Connect = "MS Access;PWD=Password;Database=" + strPath

Where i = an iterated table and strPath = the path to a back-end.

This works great and fast when the ‘MS Access;PWD=Password;’ portion is left out and the back-end is not encrypted with a password; however, it is very slow when the back-end is encrypted and this portion is added.

Are there other options that might increase the performance of this particular line?

stefan-pulseway · 2024-06-11T15:09:14.301Z

You could open a persistent connection to the back-end database at the beginning of your process and keep it open while you are linking the tables. This way, the authentication is performed only once. Also make sure that you are only updating the linked tables and not the system tables or local tables as this will reduce the number of iterations and potentially speed up the process.