Slow queries on objects: possibly old object refs in MSysObjects?

[April15Hater]

Hello,

I have a db that is having some strange speed issues on some very basic queries. Objects that have a rowsource/recordsource with a SQL statment in particular are really slow to populate. Likewise on deactivate/activate the report query seems to reload. I've put breakpoints in every object event with no stops out of the ordinary, and nothing seems unusual.

While I know MSysObjects is a user read-only table, I came across some records that reference objects that no longer exist in the front-end. Is this table cumulative? Is there any way to purge the deprecated records? I tried to turn on then turn off autocorrect features hoping that would trigger it to repopulate, but no luck.

I'm thinking this may be the source of the issue. Perhaps if it is looking for an object that it can not find (especially the ~sq_ unsaved queries) then it's looking to the wrong place on a lostfocus/activate/deactivate or similar event.

Some history on this file, it has been through many versions of Access starting with 2003, which I think is part of the issue as well. Shy of looping every object and replicating the read-write properties, I'm not sure where else to go with this.

Any ideas, suggestions, glimmers of hope...?

Thanks,

Joe

 

[CJ_London]

tried to turn on then turn off autocorrect features hoping that would trigger it to repopulate, but no luck.

In itself, this won't work, create a new db, turn off autocorrect then copy your old db into the new db.

Some other things to try:

• I presume you have tried compacting? If not, do!
• Are you linking to tables on the network and it is the network that is slow?
• Are your tables properly indexed? if not, make it so
• Do you have linked tables where the source no longer exists? if so delete the tables

 

[April15Hater]

In itself, this won't work, create a new db, turn off autocorrect then copy your old db into the new db.

I presume you have tried compacting? If not, do!
Are you linking to tables on the network and it is the network that is slow?
Are your tables properly indexed? if not, make it so
Do you have linked tables where the source no longer exists? if so delete the tables

I tried to copy into new db, but I must have left autonaming on when I did because now they are gone. I wish M$ would just disable the darn thing altogether. So that's the good news. The bad news is that my hunch was wrong. Still slow....

-Definetely compacted...I've gone as far as decompiled, recompiled, then compacted using a script written by Michael D Lueck from this board.

-Regarding the network, files transfer pretty fast so I'm hesitant to point the finger that way. However you gave me an idea.... It may be worth sniffing the data with wireshark to track the amount of data transfer and at what intervals. This will at least tell me if it's a form issue, query issue or data issue.

-3 & 4 are both in line as well. I've indexed my PK/FK's, and anything that should be linked, is.

I'll see if I can get WS to run and report back
Thanks!

Joe