Slow opening local tables in Access 2003

Hello all,

I have an annoyance that is common to all of the Access 2003
applications I manage. All of the applications are split front end/
back end with a front end copy on each client workstation and back
ends on a common server. They are all secured with workgroup files
also residing on the server. Each front end has a mix of linked
tables and local tables. The annoyance I have noticed is that it
sometimes takes 10 to 15 seconds to simply open one of the local
tables in a copy of the front end by double clicking on it in the
database window. When I double click on one of these local tables, I
notice a flurry of network traffic indicated by the LAN connection
status icon in the system tray, then 10 to 15 seconds later the table
opens. However, if I open the front end database, then disable my LAN
connection, these tables open nearly instaneously. All of these local
tables are small lookup tables (one example has only two fields and
120 records). I have all of the Name Autocorrect options turned off.
The FE's are in Access 2000 format. I have compacted, repaired,
decompiled, recompiled all to no avail. At first I thought it might
have something to do with authenticating me against the workgroup file
on the server, but even when I copy the workgroup file to my
workstation and connect to it there the same behavior occurs. Why
would these small local tables take so long to open when I am
connected to the network yet open nearly instantaneously when I'm not?

Bruce

Re: Slow opening local tables in Access 2003

Hi, Bruce.

> The annoyance I have noticed is that it
> sometimes takes 10 to 15 seconds to simply open one of the local
> tables in a copy of the front end by double clicking on it in the
> database window.

Open these tables one by one in Design View, right-click on the table's
Title Bar and select "Properties" from the pop-up window. Change the
Subdatasheet Name Property to None and then save the table. You can do this
all at once programmatically with Access MVP Allen Browne's code in the
"Tables: SubdatasheetName" section on the following Web page:

http://allenbrowne.com/bug-09.html

Also, ensure that you have a persistent connection to the back end database.
The easiest way to do this is to have a startup form's OnOpen event open a
hidden form that connects to a small, one-row table in the back end database
in its OnOpen event and that disconnects from that table in the hidden
form's OnClose event. The user never sees the hidden form, and when the
database closes, it closes that hidden form beforehand, triggering the
disconnect from the persistent connection to the back end.

> Why
> would these small local tables take so long to open when I am
> connected to the network yet open nearly instantaneously when I'm not?

They have to connect to the database across the network to get the listing
of all tables and queries and get the data for specific tables or queries
named in the table's Subdatasheet Name Property or for all related tables if
"Auto" is selected. It takes a lot of time to do this if you have a lot of
tables in the database, especially if you don't have a persistent connection
to the back end database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Re: Slow opening local tables in Access 2003

On Oct 27, 2:46 am, "'69 Camaro" 69Cam...@Spameater.orgZERO_SPAM> wrote:
> Hi, Bruce.
>
> > The annoyance I have noticed is that it
> > sometimes takes 10 to 15 seconds to simply open one of the local
> > tables in a copy of the front end by double clicking on it in the
> > database window.
>
> Open these tables one by one in Design View, right-click on the table's
> Title Bar and select "Properties" from the pop-up window. Change the
> Subdatasheet Name Property to None and then save the table. You can do this
> all at once programmatically with Access MVP Allen Browne's code in the
> "Tables: SubdatasheetName" section on the following Web page:
>
> http://allenbrowne.com/bug-09.html
>
> Also, ensure that you have a persistent connection to the back end database.
> The easiest way to do this is to have a startup form's OnOpen event open a
> hidden form that connects to a small, one-row table in the back end database
> in its OnOpen event and that disconnects from that table in the hidden
> form's OnClose event. The user never sees the hidden form, and when the
> database closes, it closes that hidden form beforehand, triggering the
> disconnect from the persistent connection to the back end.
>
> > Why
> > would these small local tables take so long to open when I am
> > connected to the network yet open nearly instantaneously when I'm not?
>
> They have to connect to the database across the network to get the listing
> of all tables and queries and get the data for specific tables or queries
> named in the table's Subdatasheet Name Property or for all related tables if
> "Auto" is selected. It takes a lot of time to do this if you have a lot of
> tables in the database, especially if you don't have a persistent connection
> to the back end database.
>
> HTH.
> Gunny
>
> Seehttp://www.QBuilt.comfor all your database needs.
> Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
> Blogs:www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpo t.comhttp://www.Access.QBuilt.com/html/expert_contributors2. htmlfor contact
> info.

Yes, that is indeed the problem. Thanks for the very helpful reply!

Bruce

Re: Slow opening local tables in Access 2003

> Yes, that is indeed the problem. Thanks for the very helpful reply!

You're very welcome. Glad it solved the problem.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Re: Slow opening local tables in Access 2003

On Oct 30, 12:20 am, "'69 Camaro" 69Cam...@Spameater.orgZERO_SPAM> wrote:
> > Yes, that is indeed the problem. Thanks for the very helpful reply!
>
> You're very welcome. Glad it solved the problem.
>
> Gunny
>
> Seehttp://www.QBuilt.comfor all your database needs.
> Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
> Blogs:www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpo t.comhttp://www.Access.QBuilt.com/html/expert_contributors2. htmlfor contact
> info.

Is it possible (and if so, how) to set the default value of the
SubDataSheet Name property to None? In way new tables will not have
to be altere.

Jim