MSYS tables (mystery tables)

[fishhead]

I have an application that is running very slowly and i noticed there are BE tables that i have not created.
when i open these tables they are clearly created by MSAccess itself as they are not anything i would have created.

I took a copy of the BE files and deleted the data in the tables (that i could - as some of the tables would not allow me to delete anything)
and the application seems to be working lighting fast now!

I'm afraid If I do this to my main BE tables i may cause a problem i can't fix so i'd like someones feedback on what the following are and what the implications are if i delete the records (that i am allowed to delete) in these tables:

MSysAccessStorage
MsysAccessXML
MsysACEs
MSysComplexColumns
MSysNameMap
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects


MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships
MSysResources

again i didn't create any of these and would like to know the implications of me deleting the records contained in these tables (as when i do on test data the application functions very fast (as it should).

thanks,
STeve

[isladogs]

System tables are created by Access and are normally hidden.
They are needed for Access to function correctly and most cannot be edited.
Even if you can edit them, it is a mistake to do so unless you understand exactly what you are doing which clearly isn't the case here.

In certain cases, any data you've edited will be automatically restored anyway.
In other cases, deleted records will cause issues at some point in the future when you need the item they referenced.

What you've done is almost certainly not the cause of any performance improvement.

If you want to know almost everything about system tables (and there are far more than those you've listed), read the Purpose of System Tables article on my website
http://www.mendipdatasystems.co.uk/p...les/4594446646

I would also suggest that in future you research before taking drastic action!

[fishhead]

System tables are created by Access and are normally hidden.
They are needed for Access to function correctly and most cannot be edited.
Even if you can edit them, it is a mistake to do so unless you understand exactly what you are doing which clearly isn't the case here.

In certain cases, any data you've edited will be automatically restored anyway.
In other cases, deleted records will cause issues at some point in the future when you need the item they referenced.

What you've done is almost certainly not the cause of any performance improvement.

If you want to know almost everything about system tables (and there are far more than those you've listed), read the Purpose of System Tables article on my website
http://www.mendipdatasystems.co.uk/p...les/4594446646

I would also suggest that in future you research before taking drastic action!

thanks for the feedback. I only deleted them from a COPY of the files in question. I didn't do anything with my actual "live" data (yet).
Can you tell me what the worst case scenario would be if i deleted these though? (in 1 table there is over 20,000 records)

[isladogs]

Worst case scenario? Your database would become permanently corrupted / unuseable
Depends which tables you delete records from.

[fishhead]

Worst case scenario? Your database would become permanently corrupted / unuseable
Depends which tables you delete records from.

ok you just scared me into submission.... : )

[isladogs]

Excellent. That was the intention.
You would suffer little damage with a few of the system tables but it isn't worth the risk ...and there are almost no advantages.
If you look at my article there are actually about 10 system tables that are so protected you can't even view them in the nav pane (except by very devious means) and its even harder to view the contents.
For example f_....(long string here)..._Data and other tables used for memo field column history, attachment fields & MVFs
Despite all that protection, those tables are actually editable if you know how to view them! Go figure!

[Minty]

As Colin stated just don't fiddle with them.

Your performance issues will not be down to system tables.
What is the nature of the issue, a certain form takes ages to load?
A query times out or takes minutes to run?

What is your setup - where is the BE stored relative to the FE that are accessing it?

[rpeare]

if your database is suffering performance issues and deleting items from the system tables helped, I would be shocked unless you got extremely lucky. As others have mentioned you really do not want to edit these tables unless you know exactly what you're doing.

I would I would suspect is you accidentally did something akin to a compact/repair on a table that was going to get repopulated when you re-opened the database.

I would suggest you use the compact/repair tool on it's own on a copy of your database and see if you see the same performance increase. If that works and nothing bad happens to your live data perform the same action on your live data. Access bloats over time (up to about 2gb in size) and as it gets bigger you may have real performance issues. Compact/repair should be a regular part of your database maintenance if it is not already.

[Missinglinq]

...where is the BE stored relative to the FE that are accessing it?

I suspect that this may very well have something to do with your problem.

Also, where were the copies of the BE files located when the app ran so much faster...on the same drive as the originals...on a different drive...on the same drive as the FE?

Linq ;0)>