Refreshing Linked Tables in VBA

[jpatchak]

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

[Scott Price]

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

[puppydogbuddy]

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.

Public Function RefreshLinks(strFilename As String) As Boolean
' Refresh table links to a backend database - strFilename (full path)
' Returns True if successful. 
 
___Dim dbs As Database
___Dim tdf As TableDef
 
___' Loop through all tables in the database.
___Set dbs = CurrentDb
______For Each tdf In dbs.TableDefs
_________' If the table has a connect string, it's a linked table.
_________If Len(tdf.Connect) > 0 Then
____________tdf.Connect = ";DATABASE=" & strFilename
____________Err = 0
____________On Error Resume Next
____________tdf.RefreshLink ' Relink the table.
_______________ If Err <> 0 Then
__________________RefreshLinks = False
__________________Exit Function
_______________ End If
_________End If
______Next tdf
 
___RefreshLinks = True ' Relinking complete.
 
End Function