attach tables SLOWWWW when on network

[cglisc]

Hello,

I am looking for advice as to how to speed up attaching
tables from an mdb on the network. Our application
is separated in 2 parts: an MDE acting as the user interface
and an mdb database for data. At startup of the mde, through VB code, we refresh the links to the tables in the MDB. We have about 30 tables, small to merdium size. (max 1000 rows)

When the mdb is on a file share on the network, refresh of links is *very* slow. I would appreciate if anyone can offer any tips on how to speed this up.

Thanks
Chris.

 

[VBslammer]

Make sure you're not validating each table's connection in a loop. With only one backend database, if you can get the first table to connect, then you can assume the rest will connect using the same connection string.

I built a class module based roughly on the msdn documentation for relinking tables and it tried to retrieve a value from the first record of each table in order to verify the connection. On certain systems that can cost a lot of time. So to remedy, I just check the first table for a valid connection, and relink the rest with the same connection info - much faster.

VBSlammer

Unemployed in Houston, Texas

 

[cglisc]

I forgot to mention that we do not refresh the links, but we actually recreate them. This is because due to
security reasons we are required to remove the links as
soon as the MDE exists. Probably this is the peoblem?

 

[VBslammer]

Not sure. Anyway, here's the code I was talking about. If you follow msdn's recommended approach for counting bad links, this snippet runs when you relink:

   varRet = db.TableDefs(td.Name).Fields(0).Name
   If Err.Number <> 0 Then   'If error - table is not there
       lBadLink = lBadLink + 1
       Err.Clear
   End If

This can be time-consuming. You could link using ODBC and store the connection info in a DSN file.


VBSlammer

Unemployed in Houston, Texas

 

[PROXI]

Where would you put that code to speed up the process and also is tabledefs the name of your database or is that a generic variable?

 

[VBslammer]

That's the code I would eliminate if it's being used, since it's unnecessary.

The TableDefs collection is a member of Database and contains all of your local Table objects.

VBSlammer

Unemployed in Houston, Texas