Compact and repair: Could not find MSysComplexColumns

[Extra Mile Data]

I am attempting to compact and repair a local Microsoft Access 2016 database that is being used as a back-end. Nine out of 10 times, the compact and repair process gives this message: The Microsoft Access database engine could not find the object ‘MSysComplexColumns’. I am not certain, but I suspect that the compact and repair has failed at that point and does not complete properly.

MSysComplexColumns does exist in the database. It had two records: one for the Data field in MSysResources related to an office theme, and the other was for an attachment field in one of my tables. I removed the attachments from my table, then removed the attachment field from that table. Now MSysComplexColumns only has the record for MSysResources. A compact and repair continues to display the same error.

I cleared all the data from all of the non-system tables and did a compact and repair. The error did not occur in this case.

I created a new database and, using VBA code, recreated all of the tables, relationships, and indexes, and then moved the data to the tables in the new database. When I did a compact and repair, I got the same error message again.

I cannot find anything on the internet about this issue. Can someone shed some light on this for me?

 

[theDBguy]

Hi. I think whenever I run into that issue, it usually meant the data file is corrupted. You might have to import the tables into a new database file, just to be safe.

 

[Extra Mile Data]

Yes, in my post, I believe I described already doing just that.

 

[vba_php]

probably just like guy has said, corruption is the issue. however, on microsoft's many million webpages of content lurks a possible alternative answer:

https://support.microsoft.com/en-us...ess-database-engine-could-not-find-the-object

they say that's a known issue, but the results from google yield a lot of content for sure:

https://www.google.com/search?q=The...ould+not+find+the+object+MSys+Complex+Columns

 

[Extra Mile Data]

Really? Did you even read my post? And did you read any of the stuff from the links you sent me? They do not apply.

 

[vba_php]

Really? Did you even read my post? And did you read any of the stuff from the links you sent me? They do not apply.

I thought I did. I'm just googling for fun, and out of curiosity. I'll step out. Sorry about that.

 

[theDBguy]

Yes, in my post, I believe I described already doing just that.

Hi. What I suggested is different than using code to recreate all tables and relationships and then moving the data. When I do it manually, I don't get the error anymore. In your case, when you used code, you still got the error. However, I cannot guarantee trying my suggestion will get rid of the error. I am just saying what I have experienced.

 

[Extra Mile Data]

I see; you are correct. That is a different approach.

I did not mention in the post that, in a different attempt, I had also created a new database and used the Import option to import in all the objects from the old database. That was actually the first thing that I did, and that did not fix the error either.

 

[theDBguy]

I see; you are correct. That is a different approach.

I did not mention in the post that, in a different attempt, I had also created a new database and used the Import option to import in all the objects from the old database. That was actually the first thing that I did, and that did not fix the error either.

Okay, so the only other difference at this point, that I can think of, is you're using/doing these on the same machine. So, my next suggestion would be maybe try using a different machine to see if it's possibly a corrupted/broken installation of Access on your machine. Just a thought...

 

[Extra Mile Data]

That is a good next step.

 

[theDBguy]

That is a good next step.

Good luck! I am not sure I have any more ideas to try after that. Cheers!

 

[isladogs]

As already stated, this issue indicates corruption. In your case it would appear to be corruption related to your attachments.

For information, there is no Data field in the MSysResources system table.

The contents of the MSysComplexColumns system table actually refer to deep hidden system tables - these are tables that you cannot view

It is used as a summary of all deep hidden system tables containing complex data - Multivalue fields, attachment fields and column history in memo fields.

MSysComplexColumns will always contain at least one record with ColumnName = Data. This refers to a deep hidden table f_...(long string here)..._Data which is associated with the MSysResources table as that contains an attachment field. The ColumnName field is the last part of that F_..._Data table

There will be additional records also related to other complex data. For example if you have a table with a multivalue field called 'MVF' there will be another deep hidden system table called f_........._MVF and the MSysComplexColumns table has an extra record with ColumnName field =MVF.
Similarly for tables with attachment fields etc.

This query may help explain what I'm referring to:

SELECT MSysObjects.Name AS TableNamE, MSysComplexColumns.FlatTableID, MSysComplexColumns.ColumnName
FROM MSysObjects INNER JOIN MSysComplexColumns ON MSysObjects.Id = MSysComplexColumns.FlatTableID
WHERE (((MSysObjects.Type)=1));

When you imported everything into a new database including those attachment fields, all those system tables will be recreated and the corruption will recur. Remove the corrupted attachment data and the problem is solved.

So as I see it you have two choices
1. The first method will enable you to identify the culprits(s) causing corruption. It will also be incredibly tedious to do:
Reimport everything except for the table with the attachment field. Recreate that table with the same structure but no data. Import all records except for the attachment data. Backup & compact. Assuming all is OK (it should be), now add your attachments one at a time and after each is added, backup & compact again. Continue till it gets corrupted. Return to your backup and add remaining records (but not the corrupted one)

2. Use this as an opportunity to get rid of all attachment fields which are bad news anyway as they cause database file bloat. Instead store the file paths to those attachments in a text field.
Similarly scrap any other multivalue fields as they are also bad news
Your database will be all the better for making those changes

 

[Extra Mile Data]

You are the second person to answer my post that did not actually read my post. I spent time writing it carefully to explain the process that I've already been through.

As I stated in the post, I removed the attachments and the attachment field in the one table that held them.

And, yes, the attachment field in the MSysResources table in my database is referred to by MSysComplexColumns as the Data field.

 

[isladogs]

I did read your post carefully & spent some time writing a factually correct response to points that you made including errors in what the data in MSysComplexColumns actually refers to. My answer was meant not only for your information but also for anyone else reading this thread

In your case, it may be that there is corruption in your database theme - hence the error occurs due to the Data record in the MSysComplexColumns table.
You may choose to ignore my suggestion but, having done a lot of research into system tables, I do know what I'm talking about in this case

Good luck finding your own solution

 

[isladogs]

For info, this is cross posted at https://social.msdn.microsoft.com/Forums/en-US/39287e36-25b7-418b-b4c1-d86a5df9c698/compact-and-repair-could-not-find-msyscomplexcolumns?forum=accessdev#b1377cfb-e66e-4922-b4ec-52e5a14b6bec and https://www.utteraccess.com/forum/index.php?showtopic=2056195&hl=

 

[NauticalGent]

You are the second person to answer my post that did not actually read my post. I spent time writing it carefully to explain the process that I've already been through.

You have a strange way of saying "thank you for taking time out of your schedule to help me with my problem". Best of luck to you going forward.

 

[Gasman]

Hmm, UtterAccess veteran from 2009, MS since 2014 yet still crospposts without mentioning it?

Damn, there is a lot of spam in that MS forum.

 

[Uncle Gizmo]

Definitely a "Cross Post"!

Sent from Newbury UK

 

[Oriel Tzvi Shaer]

In my case it was simply a case of insufficient memory in the server I was running the Compact and Repair.
Clearing some memory solved the problem.

 

[Pat Hartman]

In my case it was simply a case of insufficient memory in the server I was running the Compact and Repair.
Clearing some memory solved the problem.

Do not run C&R on a database on the server. If you have to compact that database, copy it locally, compact it, put it back.