Im trying to have my program automatically refresh the
linked tables when user enters the program.
The path for source tables will not change so I do not
need nor wish the multiple users to Refresh the links on
their own. I've played with the tabledef.RefreshLink code
but am having issues.
Please do you have a sample code where all I need to do is
insert the names of my tables to be refreshed (approx. 10).
You can cycle through a recordset based on the following SQL statement to
retrieve the names of all the linked tables and their connect strings:
SELECT CStr([Database]) AS DB, [Name] & "" AS TblName
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));
As you cycle through this recordset, store the retrieved values and then use the
following function to recreate the link:
'*********FUNCTION START
Function faq_ConnectLink(strTable As String, strSourceDB As String)
' This function can be run by any user who has OpenRun permission
' on the source database. It works equally well to link tables
' from scratch or to relink previously attached tables. In-line
' error handling is used to ignore any errors
' Parameters:
' strTable
' Name of the table to be linked
' strSourceDB
' Fully-qualified path and filename of the source db
'
' Comments: You need a reference to the DAO Object Library to
' use this code
' Modified: 05/19/2003 by BMT to set objects to "Nothing"
' Original is from the MS Access Security FAQ
On Error Resume Next
Dim ws As Workspace
Dim db As Database
Dim tdf As TableDef
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
' Delete the link if it already exists
db.TableDefs.Delete strTable
' Create new link
Set tdf = db.CreateTableDef(strTable)
' Set the properties of the new link
' and append to the tabledefs collection
tdf.SourceTableName = strTable
tdf.Connect = ";DATABASE=" & strSourceDB
db.TableDefs.Append tdf
Set db = Nothing
Set ws = Nothing
Err = 0
End Function
'*********FUNCTION END
