Linking from code

[Domac]

Hi,

I am currently linking using method below:

while rst.eof=false

Set tdef = CurrentDb.CreateTableDef(rst!TableName)

tdef.Connect = rst!LinkToBase
tdef.SourceTableName = rst!LinkTableName

CurrentDb.TableDefs.Append tdef


wend

Problem is that I am linking about 80 tables each time user log in! It takes
about 1minute for linking to finish!

Can I append those tableDefs to some Collection object and then append whole
collection to CurrentDb.TableDefs???

Would it be faster???

Users are 'killing' me ! (30 of them)!

Please help me to make linking faster!


Thanks,
Domagoj

 

[Allen Browne]

One trick is to open a database variable on the back end before your link.
Although the code does not actually use the variable, it holds the file
open, and speeds up the process considerably. This kind of thing:
Dim dbData As DAO.Database
Set dbData = OpenDatabase("C:\MyPath\MyFile.mdb")
'your linking code here
dbData.Close
Set dbData = Nothing

A more usual approach is to only link the tables that need linking, i.e.
don't link them all again unless there are specific new tables that have to
be added, linked tables that have to be dropped, of the path is different
(in which place you can just change the Connect string of the TableDef.)

 

[Domac]

Solution you have suggested to me works!!

It is about 8x faster.

I have tried before to change only the "Connect" property of tabledef object
in currentdb.tabledefs collection, but it didn't linked to it.
Old table data was shown instead of "new" data from another database!

Is there usable solution for changing only the "Connect" property??


You have helped me a lot , my users are now satisfied!!!



Thanks!
Domagoj

 

[Allen Browne]

Good news!

Here's an example of re-linking by changing the Connect property:
http://allenbrowne.com/ser-13.html