Create Many Linked tbls Quickly

[DaveMac]

Need VBA to link tables to many identical DBs
I have 24 servers that are running the same application. I would like to connect to these via code rather than having to create 240 linked tables.
Need: To link to 10 tables on 24 servers
Good news: I have a local table that has all 24 server names and ip address. All the DB names are the same and the tables are the same.
Example: 24 servers with a DB called Northwind. Now link all tables into my local DB as tbl_Orders_10.2.2.2 or as tbl_orders_SVRNY by looping through the list of servers tbl.

 

[SkipVought]

Hi,

I suspect that this is MS Access. Please post MS Access VBA questions in forum705 for better answers.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Public Sub linkTables()
  Const tblName = "tblLinks"
  Const fldPath = "databasePath"
  Const fldSourceTableName = "SourceTableName"
  Const fldLinkTableName = "LinkTableName"
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Set db = CurrentDb
  Set rs = CurrentDb.OpenRecordset(tblName)
  Do While Not rs.EOF
    createLink db, rs.Fields(fldSourceTableName), rs.Fields(fldLinkTableName), rs.Fields(fldPath)
    rs.MoveNext
  Loop
End Sub

Public Sub createLink(db As DAO.Database, SourceTableName As String, LinkTableName As String, path As String)
   Dim tdf As TableDef
   Set tdf = db.CreateTableDef(LinkTableName)
   tdf.Connect = ";DATABASE=" & path
   tdf.SourceTableName = SourceTableName
   db.TableDefs.Append tdf
End Sub

my table tblLinks has the path to the database, the source table name, and the new name