Refreshing a Linked ODBC Table

[jgier]

Hello Everyone,

Looking for some guidance on an issue I'm having in regards to a Linked ODBC table. I have linked the ODBC table and am looking to refresh the connection upon open of the database. I'm hoping to have this pop up as soon as it opens so the user will not have to log in at a later point in time in the process. The code works to a certain extent. It will refresh the linked table but than when I try to access the link table it asks for user password 3 additional times. Any thoughts? This is based on an AutoExec macro to run the code upon open.

Public Function RefreshTable()

Dim strLocalTableName As String
Dim strConnection As String
Dim daoTableDef As DAO.TableDef
Dim dbs As Variant

strLocalTable = "EDW_PROD_MTH_CUSTOMER"
strConnection = _
"ODBC;DSN=EDWPRO;APP=Microsoft Office Access 2010;DATABASE=EDWPRO;TABLE=EDW_PROD.MTH_CUSTOMER"

Set dbs = CurrentDb()

Set daoTableDef = dbs.TableDefs(strLocalTable)
daoTableDef.Connect = strConnection
daoTableDef.RefreshLink

Set daoTableDef = Nothing

End Function

[zmbd]

Please use the "[code]...[/code]" tags around your code.

See if the following will help:

sConnect = "MS Access;PWD=" & sPassword & ";" & "DATABASE=" & sPath & sDatabaseName

for more detail... click on the ".connect" protion of your code in the VBA editor and [f1] for context help.

-z

[NeoPa]

Check out Relinking ODBC Tables using VBA.

If it's an Access table, why are you using ODBC anyway?