By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 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()
  2.  
  3. Dim strLocalTableName As String
  4. Dim strConnection As String
  5. Dim daoTableDef As DAO.TableDef
  6. Dim dbs As Variant
  7.  
  8. strLocalTable = "EDW_PROD_MTH_CUSTOMER"
  9. strConnection = _
  10. "ODBC;DSN=EDWPRO;APP=Microsoft Office Access 2010;DATABASE=EDWPRO;TABLE=EDW_PROD.MTH_CUSTOMER"
  11.  
  12. Set dbs = CurrentDb()
  13.  
  14. Set daoTableDef = dbs.TableDefs(strLocalTable)
  15. daoTableDef.Connect = strConnection
  16. daoTableDef.RefreshLink
  17.  
  18. Set daoTableDef = Nothing
  19.  
  20. End Function
Aug 2 '12 #1
Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,287
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.

-z
Aug 12 '12 #2

NeoPa
Expert Mod 15k+
P: 31,261
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.