Fast Linking of tables to a back end MDE

[Rick Roberts]

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

 

[TomU]

Have you set Track Name AutoCorrect off for each table in the BE? I've found
this to make a substantial difference during startup.

TomU

 

[Marshall Barton]

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

I don't use ADO, but the common approach to this issue is to
open something in the backend (a recordset?) and leave it
open until all the links are updated.

 

[Rick Roberts]

I found that the best answer in ADO is to leave the catalog to the BE open.
That along with turning off Name AutoCorrect has made this a non issue.

Thanks to all!