Create a linked table with VBA
Posted by: fredometro on 
Feb 1st, 2004 | 
Filed under: MS-Access
Usually we would just need to refresh the existing linked tables, but in some cases, it is necessary to create a linked table from scratch.
This is useful when linking a text file or Excel sheet which name changes over the time.
public sub ReconnectTable(sTable, sConnect)
Dim db as Database
Dim td As TableDef
on error goto Err_Reconnect
Set db = currentdb()
on error resume next
‘ Delete the table
db.TableDefs.Delete sTable
on error goto Err_Reconnect
‘ Create the table def.
Set td = db.CreateTableDef(sTable)
‘ Connect the table
td.Connect = “;DATABASE=” & sConnect & “;”
‘ Set the source table name
td.SourceTableName = sTable
‘ Add the table def to the tabledefs collection
db.TableDefs.Append td
‘ refresh the link
td.RefreshLink
db.close
exit sub
Err_Reconnect:
msgbox “Error while reconnecting table ” & sTable & “: ” & error$
end sub
Random Posts
Comments (15) 