FredixBlog


Just my log about anything I could find enjoyable.



Create a linked table with VBA

author Posted by: fredometro on date Feb 1st, 2004 | filed 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

tags