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

Refreshing Linked Tables in VBA

P: 76
Hello All,

Is there a way to refresh a linked table programatically through VBA (as part of an error handler). I have an Access database with several tables linked through ODBC. User forms are in a front end MDB file. Sometimes, I must make changes to the data model that don't necessarily affect the front end forms for most of my users. When this happens, I have to refresh the tables and send a new copy of the MDB file out to all my users - even if there is no new functionality in the forms. This is an obvious pain. People get confused about which version of the front end file to be using and results in a lot of unnecessary troubleshooting.

I was wondering if there was a way to maybe "version" the tables, look up in another table the most recent version number of each table, and refresh only those tables that do not have the most recent version number.

Is this possible? If so, can someone please point me in the right direction?

Thanks,
Josh
Aug 10 '07 #1
Share this Question
Share on Google+
2 Replies


Scott Price
Expert 100+
P: 1,384
Hi Josh,

Have a look at this post:

http://www.thescripts.com/forum/thread688654.html

You'll likely have to modify the code contained there, but it might be the nudge in the right direction you need...

Regards,
Scott
Aug 10 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Hello All,

Is there a way to refresh a linked table programatically through VBA (as part of an error handler). I have an Access database with several tables linked through ODBC. User forms are in a front end MDB file. Sometimes, I must make changes to the data model that don't necessarily affect the front end forms for most of my users. When this happens, I have to refresh the tables and send a new copy of the MDB file out to all my users - even if there is no new functionality in the forms. This is an obvious pain. People get confused about which version of the front end file to be using and results in a lot of unnecessary troubleshooting.

I was wondering if there was a way to maybe "version" the tables, look up in another table the most recent version number of each table, and refresh only those tables that do not have the most recent version number.

Is this possible? If so, can someone please point me in the right direction?

Thanks,
Josh
Josh,

This code from the tips page of www.aadconsulting.com seems to be what you are looking for. Hope it helps.
Expand|Select|Wrap|Line Numbers
  1. Public Function RefreshLinks(strFilename As String) As Boolean
  2. ' Refresh table links to a backend database - strFilename (full path)
  3. ' Returns True if successful. 
  4.  
  5. ___Dim dbs As Database
  6. ___Dim tdf As TableDef
  7.  
  8. ___' Loop through all tables in the database.
  9. ___Set dbs = CurrentDb
  10. ______For Each tdf In dbs.TableDefs
  11. _________' If the table has a connect string, it's a linked table.
  12. _________If Len(tdf.Connect) > 0 Then
  13. ____________tdf.Connect = ";DATABASE=" & strFilename
  14. ____________Err = 0
  15. ____________On Error Resume Next
  16. ____________tdf.RefreshLink ' Relink the table.
  17. _______________ If Err <> 0 Then
  18. __________________RefreshLinks = False
  19. __________________Exit Function
  20. _______________ End If
  21. _________End If
  22. ______Next tdf
  23.  
  24. ___RefreshLinks = True ' Relinking complete.
  25.  
  26. End Function
Aug 11 '07 #3

Post your reply

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