Using VBA to Update Linked Tables 1

[bestof89]

My app uses a number of linked tables and I want to have a routine that updates the linked tables when the path of the app is changed.

I'm able to change the
tabledef.connect property but this does not seem to do anything.

Is there a way to do this without using the linked table manager? Or use the Linked Table Manager with VBA?

Thanks,

-J

 

[JimInKS]

Here is the routine I use for this:

Function LinkAccessTable(LinkTblName As String, SrcTblName As String, DBPath As String)

'-------------------------------------------------------------
' Parameters
'-----------
' LinkTblName (String): Name of the Linked table in current db
' SrcTblName (String) : Name of the table in the backend db
' DBPath (String) : Pathname of backend db
'-------------------------------------------------------------
Dim db As Database
Dim tdf As TableDef

On Error GoTo HandleErr

LinkAccessTable = -1

Set db = CurrentDb()
Set tdf = db.CreateTableDef(LinkTblName)
tdf.Connect = ";DATABASE=" & DBPath
tdf.SourceTableName = SrcTblName
db.TableDefs.Delete LinkTblName
db.TableDefs.Append tdf
db.TableDefs.Refresh
LinkAccessTable = 0
Exit Function

HandleErr:
Select Case Err.Number
Case 3265 ' Table does not currently exist
Resume Next
Case Else
ErrorMsg
End Select
End Function

 

[bestof89]

Very nice thanks.

So the upshot is that you can't change the connect, but you can delete and re-add on the fly.

Thanks again

 

[JPeters]

What if the table is linked to a text file instead of a database table?

------------------
JPeters
------------------