refreshing a linked table via a macro/module code 1

[R9772]

i want to update/refresh a linked table through the use of a macro. but, can not find anything on how to do this. can anyone help me with this?

 

[VicM]

R9772:

I do something very similar. The code below is what I use. It is DAO coding as opposed to ADO. Be sure you have the Microsoft DAO 3.6 Object Library installed in your references. Put the code in a database Module, not in a form or report code sheet.

Sub ChngLink()
Dim strLink As String, tblLnkdDb As DAO.TableDef, tDefs As DAO.TableDefs

strLink = ";DATABASE={Your fully qualified path here}"

For Each tblLnkdDb In CurrentDb.tDefs
If InStr(1, tblLnkdDb.Name, "msys&quot = 0 Then
tblLnkdDb.Properties(4).Value = strLink
tblLnkdDb.RefreshLink
End If
Next
End Sub

Microsoft uses MSys as a prefix for all hidden system tables. You DO NOT want to link the system tables to a backend database. Each database has its own system tables.

You'll need to make modifications if only some of your tables are linked. Probably with a Select statement.

One other warning: Be sure the path to your backend is fully qualified. By that I mean, use only the absolute address of the server and the complete directory path to the backend db. Do not use a path beginning with a drive letter, as not every user may have the same drive letter mapped to that server or mapped from the same starting point on the server.

Hope this helps,

Vic