import system tables (e.g. MSysAccessStorage)

[John Q.]

Due to a recent "phantom compiler-error" diagnosis of a "user-defined type
not defined" error message, I had to re-create my mdb by creating a new db
and then importing all objects into it. It fixed the problem after many,
many hours of efforts previous to it and things appear to be fine.

However, most of the system tables were imported as copies (with a "1"
suffix). I need to delete 5 tables (which are not exact duplicates) although
when I tried to delete the first in the list below, "MSysAccessStorage", it
would not let me, nor would it allow me to edit the table (copy records from
"MSysAccessStorage1" to "MSysAccessStorage"). THe message said that someone
else was using the file, though there were no objects open.

Things seem to be working just fine but aside from good housekeeping in
wanting to remove these "extra" tables, these "duplicated" filenames have
ballooned my program 180%.

I have dealt with "MSysIMEXColumns" and "MSysIMEXSpecs" (which both were
not "duplicated") in dealing with Import/Export specs, but these tables are
much larger and more complicated.

Does it make a difference if I keep the original 5 tables or if I keep the 5
<tablenames1>?

Does this method of copying a database fall short because of the system
tables (and subsequent "duplication")? Any advice on this process would be
appreciated.

Original table created in new db1 (Imported Copy from previous db in
parentheses):

MSysAccessStorage (MSysAccessStorage1)
MSysACEs (MSysACEs1)
MSysObjects (MSysObjects1)
MSysQueries (MSysQueries1)
MSysRelationships (MSysRelationships1)


These files imported fine - no copies:

MSysIMEXColumns
MSysIMEXSpecs

Thanks,
JQ

 

[John Vinson]

Does it make a difference if I keep the original 5 tables or if I keep the 5
<tablenames1>?

I'd start over with a NEW database, and import everything *except* the
system tables. Access maintains them for itself (the Access database
engine is "the other user" it's complaining about); you cannot and
should not write to, modify, or otherwise mess with any of the system
tables!

If this isn't feasible, BACK IT UP (duh!) and delete the "1" suffix
tables.

John W. Vinson[MVP]

 

[John Q.]

re:

I'd start over with a NEW database, and import everything *except* the
system tables. ... should not write,modify,mess with system tables!

If this isn't feasible, BACK IT UP (duh!) and delete the "1" suffix
tables.

What I have done thus far is literally your 2nd suggestion, deleting the 5
<imported1> tables and things seem to work OK. But a concern was raised by
the fact that instead of the db being 180% of its typical size (with those 5
"duplicated" tables), the db is now around 88% of its typical size when the 5
are removed.

I may try your first suggestion to see how that compares in behavior and size.

Thanks,
JQ

 

[John Vinson]

What I have done thus far is literally your 2nd suggestion, deleting the 5
<imported1> tables and things seem to work OK. But a concern was raised by
the fact that instead of the db being 180% of its typical size (with those 5
"duplicated" tables), the db is now around 88% of its typical size when the 5
are removed.

That's about what I'd expect. Importing duplicate copies of the system
tables would simply add a whole lot of bloat for no benefit; deleting
the duplicates and compacting would shrink it down. Your previous
database undoubtedly had some "trash" sitting around which Compacting
failed to remove, but importing all the objects succeeded in leaving
it behind; so 88% of your previous size sounds like things are working
well.

John W. Vinson[MVP]