Access db "pings" and displays hourglass circle at odd times (1 Viewer)

Rastro_Jetson

Registered User.
Local time
Today, 17:39
Joined
Mar 1, 2017
Messages
12
Hello folks,

I inherited a giant MS ACCDB. It has a little bit of everything in it; local tables, odbc tables, linked network XLSX data sources, zillions of macros, zillions of queries, etc. Currently, I pull the db down to my C:\ drive but all those other links are still active.

When I pull up a SELECT query and start designing it... if I click, say in the criteria line of a field I've added to the query, the db clocks. Spinning circle... Maybe just 5-10 seconds. Next field, click in the criteria line... it clocks for 5-10 seconds.

The db is not running anything that I can see. I've never seen this and it's highly annoying. Any advice would be greatly appreciated. :^)
 

Minty

AWF VIP
Local time
Today, 22:39
Joined
Jul 26, 2013
Messages
10,355
Check all the forms for Timer events.
If you have any form open with a timer event strange things can happen in design mode!
 

Ranman256

Well-known member
Local time
Today, 18:39
Joined
Apr 9, 2015
Messages
4,339
never seen such a thing happening on a local drive.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:39
Joined
Feb 28, 2001
Messages
27,001
You say that you have some ODBC situations and that they stay intact even though you have copied the DB to your local system.

So... my first question is, when you open a query for design purposes, does this query involve one of the ODBC databases or XLSX sources? Because Access has to verify the properties of anything that you could change (or DID change), and that involves a network operation if the external tables are included in the design operation.

Saying 5-10 seconds is an issue of network speed AND complexity of the tables. Can't do anything about complexity at all, but we COULD test the network speed.

If you can get to the command prompt and if you know the name of the server hosting your external connection, you can try

PING name-of-database-server

and see how long it takes to respond. The answers from PING will be in milliseconds. A good number is anything under 50 msec. A bad number (and the instant answer to your question) is anything over 1000 msec. An abysmal number would be over 2500 msec.

There is also this little-known and totally under-appreciated fact: Every layer of directory you have to traverse to get to the file in question on the remote host adds a network transaction. So if the files are in

C:/Data/database-component-file

then that is a faster connection than

C:/Share/A/B/C/D/E/F/database-component-file

If you have the longer path, you pay for it because EVERY INTERMEDIATE FOLDER in that path is another network transaction because you have to go through security access arbitration for EACH FOLDER. Even if you are allowed access, it has to check each time. And in design mode, you probably will NOT have a persistent connection to each of those sources to allow you to "ride" the connection for free. The combination of a deep path plus a slow network segment will be frustrating on the best day of your life and will drive you to drink on the bad days.
 

Rastro_Jetson

Registered User.
Local time
Today, 17:39
Joined
Mar 1, 2017
Messages
12
Thank you, folks. I'll check these items and report back for others with a similar situation. I do know the network is spotty at times... so, this actually doesn't surprise me. Just wasn't thinking it had to re-ping the odbc table each time, etc.
 

Users who are viewing this thread

Top Bottom