When initially linking over 50 tables within a client MDE to a server MDE
using VBA / Access 2003 it takes an amazing amount of time.  I am successful
using either the docmd method or the ADOX method.  The problem is that it
takes quite a bit of time (1-3) minutes to perform this task.  I think this
is because it opens and closes the Server mde for each individual table link,
which in my case would be a ton of wasted overhead.

If I use the manual method of File > Get External Data > Link  Tables >
Select All Files, the time is cut down to 10-15 seconds.  So there must be a
better way! Is anyone aware of a fast version using VBA.  Is there a way to
open/close the Server MDB and perform all the Links in one pass?

I would appreciate anyone’s opinion/help on this issue.

These are the two methods I have used

DoCmd Method
   DoCmd.TransferDatabase acLink, "Microsoft Access", FullServerName,
acTable, TableName, TableName

ADOX Method                
Set tblLink = New ADOX.Table
With tblLink
        ' Name the new Table and set its ParentCatalog property to the
               ' open Catalog to allow access to the Properties collection.
               .Name = TableName
               Set .ParentCatalog = cat1
           
               'Set the properties to create the link.
               .Properties("Jet OLEDB:Create Link") = True
               .Properties("Jet OLEDB:Link Datasource") = FullServerName
               .Properties("Jet OLEDB:Remote Table Name") = StrLinkTable
           End With
           ' Append the table to the Tables collection.
           cat1.Tables.Append tblLink