http://www.accessmvp.com/djsteele/DSNLessLinks.html
This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.
One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.
Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.
Use this code to loop over all tables and queries to set the connection string:
Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String
Set db = DBEngine(0)(0)
strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"
' reconnect pass-through queries
For Each qd In db.QueryDefs
With qd
If Left$(.Connect, 4) = "ODBC" Then
' note: for a passthrough query the uid and pwd are always stored
.Connect = strODBC
.Close
End If
End With
Next qd
' reconnect linked tables
For Each td In db.TableDefs
With td
If Left$(.Connect, 4) = "ODBC" Then
' note: for a linked table the uid and pwd are only stored when this property is set!
.Attributes = dbAttachSavePWD
.Connect = strODBC
.RefreshLink
End If
End With
Next td
db.Close
Set db = Nothing
Notes:
- Use DBEngine(0)(0) in stead of CurrentDB(); the implementation is 1000x faster
- Use Left$ in stead of Left; the implementation is faster
- This particular code assumes that DAO is used, not ADO
- The connection uses a SQL Server Authentication instead of Windows Authentication
- The connection string value is assigned to variable strODBC
- The connection string has to start with the text "ODBC;" both for the linked table as the pass-through query
- If the connection is to a default instance of SQL Server, the string "\SQLEXPRESS" can be removed
- By default, the username and password are stored for a pass-through query, but not for the linked table. This appears to be a bug, it either should be set for both or for neither. Setting the uid/pwd parameters for a linked table does, however, not result in an error
- ... but if the code td.Attributes = dbAttachSavePWD is used, then the uid/pwd are also stored for a linked table. At least for MS Access 2010 on Windows 7 x64, this is enough. In some cases two registry settings need to be set for this to work:
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
"DisablePwdCaching"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
"DisablePwdCaching"=dword:00000000 - After the code has run, the connection string has been set for all the objects, but is not yet effective! For this, MS Access needs to be closed and reopened
- I found that the reconnecting loop using DRIVER=SQL Server in stead of
DRIVER={SQL Server}or
PROVIDER={SQL Server Native Client 10.0 ODBC Driver}
was much faster - Close and destroy the reference to the database object to prevent memory leaks
Related errors might include:
"login failed. The login is from untrusted domain and cannot be used with windows authentication"
