By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,973 Members | 1,528 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,973 IT Pros & Developers. It's quick & easy.

Refreshing a Linked ODBC Table

P: 1
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function RefreshTable()
  3. Dim strLocalTableName As String
  4. Dim strConnection As String
  5. Dim daoTableDef As DAO.TableDef
  6. Dim dbs As Variant
  8. strLocalTable = "EDW_PROD_MTH_CUSTOMER"
  9. strConnection = _
  12. Set dbs = CurrentDb()
  14. Set daoTableDef = dbs.TableDefs(strLocalTable)
  15. daoTableDef.Connect = strConnection
  16. daoTableDef.RefreshLink
  18. Set daoTableDef = Nothing
  20. End Function
Aug 2 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,397
Please use the "[code]...[/code]" tags around your code.

See if the following will help:
Expand|Select|Wrap|Line Numbers
  1. 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.

Aug 12 '12 #2

Expert Mod 15k+
P: 31,662
Check out Relinking ODBC Tables using VBA.

If it's an Access table, why are you using ODBC anyway?
Aug 12 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.