How to get rid of bad entries in MSysObjects

[Petrol]

Does anyone know how to delete corrupt entries from MSysObjects?

My front end can connect to any one of several back ends. (Think of them as different departments in the one enterprise, each having its own set of data in similar tables. There is also another back end, containing tables of enterprise-wide data common to all departments). A form in the front end allows me to choose the department to connect to, whereupon code relinks all the tables of departmental data but ignores the tables of shared data.

The problem is that somehow MSysObjects has got corrupted. One of the tables, which should be linked to the shared back end, has a valid entry connecting it to the shared back end, but there are two more entries purporting to connect that table to the departmental back end. Now when I try to change departments it comes to those bad entries and fails because the B/E of new department of course does not have that table, nor should it.

I can open and look at MSysObjects, but I can't delete or change the invalid entries.

[Petrol]

OK, I have a solution. The spurious MSysObjects all had my table name as their "ForeignNam e" but their actual names were those of temporary files, named "~TMPCLP" followed by a six-digit number. Apparently these TMPCLP entries are created when broken links occur.
Anyway, although I could not delete them from the keyboard, following the example of a gentleman called "ohmydataba se" on another forum I was able to delete them in the Immediate window using
DoCmd.DeleteObj ect acTable, "~TMPCLP385 211" etc.

[isladogs]

Yes I also have a more detailed solution on my website to fix all sorts of errors in MSysObjects. Due to forum rules, I'm not allowed to provide a link but if you do a Google search for "Remove deleted objects from MSysObjects Mendip Data Systems", it will probably be the first item on the search list

Hope that helps

[twinnyfo]

isladogs,

Very interesting! Do you have any idea why it is possible to delete these objects through a code procedure, but not directly through a DeleteObject statement?

Very useful for us who care about these annoying "phantom" object, as you call them!

Thanks!

[Petrol]

Yes, thanks for the info about MSysObjects - I had wondered how it works.
However, note the last line of my own solution post. I removed the spurious table entries using the code that you said doesn't work for forms entries!

[isladogs]

@twinnyfo
Sometimes it is difficult to explain why one method works in Access when another fails and I don't have a direct answer to the question.
However see my reply to the OP below

Similarly, all ACCDB files contain at least 10 deep hidden system tables whose names can be viewed in MSysObjects. Some of these are associated with complex datatypes in a standard table.

For example "F_longstringhe re_Data" is a deep hidden system table associated with MSysResources and used to store attachment field data in a fully normalised way.

Not only are these deep hidden tables impossible to view in the navigation pane but trying to do so using a query will result in an error. Why Access makes it so difficult to view them I cannot say.

However it is possible to see their contents by a rather obscure method and in many cases they are then directly editable!

If you look at my article Purpose of System Files on the same website, you will see several screenshots of such tables. Unable to attach here unfortunately or again nor can I post the link.

@Petrol
I did see the comment in your post and have been able to do that for some ~TMPCLP objects as you described when using A365. When I wrote the article I was using A2010 and it didn't work for me ... or for many others who tried it.

Maybe something has changed since A2010? Which version are you using?

If interested, perhaps you could try and delete some of the other 'phantom objects' from my example database suppled with that article

[Petrol]

Yes, I'm using 365...

[isladogs]

Thanks for the reply.
Did you notice the other even easier method of deleting ~TMPCLP items which needs no code

Just replace the 'phantom' object with a real one! Amazingly this works!

Create a new form/report (etc) with the same name.
Save it and replace the existing form when prompted
Close the new form.

It is automatically deleted along with the TMPCLP item