mSysIMEXColumns - why does it grow? (1 Viewer)

Access 2013 (or 2007). Recently got a 'Property Too Large' error when I was trying to run an updated make table query. Is there a size limit to the 'mSysIMEXColumns' table? I am regularly importing an Excel file, each time with a different name, into a new table but I do not save the import spec. It looks, though, like this system table is growing with each import. Is that supposed to happen? If so, how can I prevent this error? A 'Compact and Repair' is regularly done.

Thanks.

This link explains the -IMEX- tables:

http://www.opengatesw.net/ms-access-tutorials/Access-Articles/Microsoft-Access-System-Tables.htm

I looked at the tables in one of my databases. They are just tables. Protected system tables ... but tables. There is no more a size limit (that I can see) than there is to any other table. You should be able to save hundreds or even thousands of such records. Unless you have an unusually long column name in one of the Excel column headers, I don't see where you would get that error. The column name is limited to 64 characters. But if I recall correctly, Excel also has that limit.

The structure APPEARS to be that mSysIMEXSpecs is the parent and mSysIMEXColumns is the child, with the ...Specs!SpecID as the linking field. The actual PK of the columns table is a compound of the spec ID number and the spreadsheet's column name.

Since SpecID is an Autonumber, I would expect that it would be largest for the most recent specs. (It isn't the PK, however - the spec's NAME is the PK.) So maybe you could open the table before an import to see what is there from prior imports and see if any of the numbers look exceeding large for some reason. Also check the highest-numbered entries in the ...IMEXSpecs table to see if any of the numbers look huge.

If you don't tell it to save, I wonder if there is some sort of improper cleanup going on? Since you say you are not saving specs, check for specs with a synthesized name, which you can recognize because the typical such name will start with a tilde (~) character. See if there ARE any, which should not be the case if you aren't saving.

I looked at database properties and offhand didn't see one relating to "always save import spec" or anything with a similar name, but I could have missed it.

As an extreme attempt, you MIGHT consider this a corrupted database and try to create a new database, importing your content from the one that is misbehaving.

Or you could try a different extremity: First make a copy of the database to protect yourself in case something goes bonkers on you. Then go into those tables and delete ALL ENTRIES from the mSysIMEXColumns and mSysIMEXSpecs tables (in that order, since they are child & parent so have Relational Integrity issues).

I have a database that has never done an import. For me, those tables are empty, so it doesn't seem to hurt anything that they ARE empty. If you don't have a "legit" import spec that you wanted to keep, that might help too. Unfortunately, if it is due to corruption, might NOT help a whit!

Thanks for your reply. As a test on a copy of the database, I deleted all of the records in the 'specs' and 'columns' tables. One of them, I forgot which, had 1998 records and I thought there was perhaps a 2000 record limit. Anyway, even after deleting the records, I still got the error. Only after doing a compact & repair was I able to update the 'make table' query and run it. To update this database weekly, I import a new Excel file whose sheet name is a week-ending date which becomes the new table name. Thirteen tables, representing the rolling last 13 weeks of data, are used by the query. So, when I import a new week, I delete the oldest table. Four columns, representing the new week, are then inserted into the query while in design mode.

As I stated before, after every database update, a compact and repair is done, so I don't know why all of a sudden I have to do a compact and repair before running the query.

By the way, after deleted the records in the 'specs' and 'columns' tables, the import specs that I previously saved still run fine.

Any reason you are using 13 tables instead of one? Does the type of data you are dealing with change on a weekly basis?

If not, I'd personally just use one table to hold your information. Append to it when you import the new week and set up queries to mimic the 13 tables you are currently using.

Thanks.

Each table represents a weeks worth of data. The data comes from an external site once a week. The system has been in place forever and changing at this point is not an option. Too many programs would need to be re-written.

I found something though on the internet that was plausible so I tried it and it appears to have fixed the problem. The 'make-table' query is modified every week in order to remove the oldest table and add the newest. So I thought that maybe editing the query every week somehow is causing its size to grow to some limit. I copied the SQL, deleted the query, created a new query, pasted the SQL and named same as the one deleted. Problem gone.