Manipulating TableDefs

[Shane Suebsahakarn]

Hi all,

Does anyone have any idea why creating a table link across a network should
be so slow? My front end app follows these steps when opening:

* Retrieve all linked tables in the current database as a recordset from the
MSysObjects table.
* For each record:
Check the existance of the mdb file that the table resides in.
If the mdb file exists, check the table exists by opening the target
mdb's MSysObjects table with the table name as a criteria.

I was originally doing this by looping through the TableDefs collection, but
that was even slower. It works just fine if there is only one user accessing
the back end database(es) - it takes a few seconds under these
circumstances, but more than one results in nearly 3-4 minutes to check 100
tables. The front ends reside on Windows 2000 Pro boxes, the back end on a
Windows 2003 Server machine. The recordsets are all using DAO in Access
2002.

Any ideas would be much appreciated.

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

[Allen Browne]

Re: Manipulating TableDefs

Several possiblities

1. File access
It takes time to open the mdb over the network, and possibly the mdw, and
create the ldb. If most of the TableDefs are from one or two mdb's, consider
opening the database directly and holding it open until the routine is
finished:
Dim dbFile1 As DAO.Database
Set dbFile1 = OpenDatabase(\\ MyServer\MyPath \MyFile.mdb)
Although you do not actually use this object, just holding it open will
speed the access dramatically. (You may need a Collection of these objects.)

2. Length of path string
It it much faster to access if the path in the Connect string is less than
128 characters.

3. Parse the server out of the path
Windows waits for a long time for a server to respond when it is not
available. Once you know that it is off line, you can avoid further lengthy
delays by not asking for it again in any further tables that access that
machine.

4. Settings.
Uncheck the the Name AutoCorrect boxes (Tools | Options | General), and set
SubdatasheetNam e to [None] (Properties box in table design). In different
settings these also slow things down dramatically.

More suggestions:

http://www.granite.ab.ca/access/performancefaq.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Shane Suebsahakarn" <shane@REMOVETH ISgcicom.net> wrote in message
news:cgjk4i$m0e $1@hercules.bti nternet.com...[color=blue]
>
> Does anyone have any idea why creating a table link across a network
> should
> be so slow? My front end app follows these steps when opening:
>
> * Retrieve all linked tables in the current database as a recordset from
> the
> MSysObjects table.
> * For each record:
> Check the existance of the mdb file that the table resides in.
> If the mdb file exists, check the table exists by opening the target
> mdb's MSysObjects table with the table name as a criteria.
>
> I was originally doing this by looping through the TableDefs collection,
> but
> that was even slower. It works just fine if there is only one user
> accessing
> the back end database(es) - it takes a few seconds under these
> circumstances, but more than one results in nearly 3-4 minutes to check
> 100
> tables. The front ends reside on Windows 2000 Pro boxes, the back end on a
> Windows 2003 Server machine. The recordsets are all using DAO in Access
> 2002.
>
> Any ideas would be much appreciated.
>
> --
> Shane Suebsahakarn
> ----
> Head of IT
> PAN Telecom
> Tel: +44 (0) 870 757 7001[/color]

[Shane Suebsahakarn]

Re: Manipulating TableDefs

Hi Allen, thanks for the response.

The first tip sounds like a good possibility. I'm thinking that it could be
some kind of issue with NT security, since a copy running on a peer-to-peer
seems to run much faster. The slowdown only happens for the second and
subsequent user as well, so I suspect it could be something to do with
accessing the ldb file.

One of the first things I did in attempting to speed it up was to go through
pretty much everything on the performance faq :)

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:412d6e00$0 $22842$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> Several possiblities
>
> 1. File access
> It takes time to open the mdb over the network, and possibly the mdw, and
> create the ldb. If most of the TableDefs are from one or two mdb's,[/color]
consider[color=blue]
> opening the database directly and holding it open until the routine is
> finished:
> Dim dbFile1 As DAO.Database
> Set dbFile1 = OpenDatabase(\\ MyServer\MyPath \MyFile.mdb)
> Although you do not actually use this object, just holding it open will
> speed the access dramatically. (You may need a Collection of these[/color]
objects.)[color=blue]
>
> 2. Length of path string
> It it much faster to access if the path in the Connect string is less than
> 128 characters.
>
> 3. Parse the server out of the path
> Windows waits for a long time for a server to respond when it is not
> available. Once you know that it is off line, you can avoid further[/color]
lengthy[color=blue]
> delays by not asking for it again in any further tables that access that
> machine.
>
> 4. Settings.
> Uncheck the the Name AutoCorrect boxes (Tools | Options | General), and[/color]
set[color=blue]
> SubdatasheetNam e to [None] (Properties box in table design). In different
> settings these also slow things down dramatically.
>
> More suggestions:
> http://www.granite.ab.ca/access/performancefaq.htm
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Shane Suebsahakarn" <shane@REMOVETH ISgcicom.net> wrote in message
> news:cgjk4i$m0e $1@hercules.bti nternet.com...[color=green]
> >
> > Does anyone have any idea why creating a table link across a network
> > should
> > be so slow? My front end app follows these steps when opening:
> >
> > * Retrieve all linked tables in the current database as a recordset from
> > the
> > MSysObjects table.
> > * For each record:
> > Check the existance of the mdb file that the table resides in.
> > If the mdb file exists, check the table exists by opening the target
> > mdb's MSysObjects table with the table name as a criteria.
> >
> > I was originally doing this by looping through the TableDefs collection,
> > but
> > that was even slower. It works just fine if there is only one user
> > accessing
> > the back end database(es) - it takes a few seconds under these
> > circumstances, but more than one results in nearly 3-4 minutes to check
> > 100
> > tables. The front ends reside on Windows 2000 Pro boxes, the back end on[/color][/color]
a[color=blue][color=green]
> > Windows 2003 Server machine. The recordsets are all using DAO in Access
> > 2002.
> >
> > Any ideas would be much appreciated.
> >
> > --
> > Shane Suebsahakarn
> > ----
> > Head of IT
> > PAN Telecom
> > Tel: +44 (0) 870 757 7001[/color]
>
>[/color]