Relationships Disappeared (1 Viewer)

[morganw]

I have a split access database that's been in use for almost two years. The database resides on a computer which I access remotely via Remote Utilities, where I transfer the db to my local PC, work on it, then transfer back to the remote machine. We use EaseUS Todo Backups to create an image file every 30 minutes of the database file. I am currently in the process of doing some refactoring and have run into the following issue:

All of the relationships in the database have somehow disappeared. Here is what is strange about this:

About a week prior to discovering this issue I had taken a copy of the database and did not have this issue.
The relationships are gone whether I open it on my local machine or the remote machine.
Upon finding this, the first thing I did was try to restore a backup to see if the relationships were there -- they were not.

This is what I can't figure out -- I had copied the file, everything was OK, then a week later no relationships were found in either the current copy, nor any of the backups from before there was no issue.
I have tried the following to resolve this:

Updating Access on both machines.
Hiding all tables then adding back and showing 'all relationships' in relationships tab.
Looking for relationships in the database documenter.
Restoring old backups as mentioned.

I'm sure this could be a result of corruption -- but how could this corruption extend to the .pbd image files generated by EaseUS, that were created before the issue occurred?

 

[theDBguy]

Hi. Just a wild guess since I can't see your setup and what's really happening; but, the file and the backups could be getting affected right at the point of opening it. There have been some issues lately on a recent Windows update. I wonder if this might have anything to do with what's happening on your end. Just a thought...

 

[Pat Hartman]

The relationships exist only in the BE. Sometimes people also create them in the FE but in the FE they are meaningless and RI cannot be enforced.

Click on the show all relationships icon to see if that brings them back. Also, scroll all the way to the right and down to see if they moved off the upper left area somehow.

And finally, in the BE, if you show the system tables, you should have one that is probably called MSysRelationships. That is where they are stored. If that is empty, I would be concerned about corruption.

 

[morganw]

The Windows update issue is interesting -- I may try opening it on a PC with a different OS and see what comes up.

I did indeed check the MSysRelationships table, and unfortunately nothing there. Corruption was my first thought, and I did do a Compact/Repair... but if the corruption was the issue, would it have extended to past image files that were already created? That's what seems so bizarre to me about this, it was fine a week prior, but then after I discovered the issue even the backups from before it was working have the same issue.

 

[theDBguy]

The Windows update issue is interesting -- I may try opening it on a PC with a different OS and see what comes up.

I did indeed check the MSysRelationships table, and unfortunately nothing there. Corruption was my first thought, and I did do a Compact/Repair... but if the corruption was the issue, would it have extended to past image files that were already created? That's what seems so bizarre to me about this, it was fine a week prior, but then after I discovered the issue even the backups from before it was working have the same issue.

Hi. Trying it out on a different computer sounds like a good plan. Good luck!

 

[Pat Hartman]

even the backups from before

You know when you discovered the issue but you don't know when it happened. The indexes may have been gone for a while. I would start by exporting all the data to .csv files and then creating a new database with properly defined tables. You can import the .csv files INCLUDING the autonumbers so that no relationships get broken. You just have to do it in a logical order so that the highest level tables get loaded first. Since you may have to do this more than once, I would create a procedure that runs the append queries so you don't accidentally forget one.

 

[Steve Larwood]

Was anything eventually discovered as the cause?
I have a few Access 2007 databases sent to me by a customer.
If I open them on my PC I can see what the customer gets where all relationships in each database have disappeared.
If I open the original master databases on the same PC all of the relationships are present and correct.

 

[Pat Hartman]

Are you sure the relationships are in the BE? That is where they belong and that is where they are enforced. You won't see them in the FE unless you reproduce them there. However, remember that no relationship defined in the FE can actually be enforced.

 

[The_Doc_Man]

@Steve Larwood - if the FE and BE involved linked tables and you moved the DB someplace else AND OPENED IT, the fact that the links are no longer valid would cause the BE relationships to go away. I've see that in a couple of posts over the years.

Other than that, got no clue.

 

[Steve Larwood]

Thanks for the comments.
It is not a FE/BE application but just a single database used by a .NET application.
Like MorganW the database has been in use for nearly two years but is only recently that the relationships have been impacted.
I have not yet been able to get information on what Microsoft Office Security updates have been installed and when.

 

[Pat Hartman]

Not sure why you would be using Jet/ACE as the BE for a .Net app. Did you try pressing the show all relationships button? You never said what you tried.

 

[Steve Larwood]

The fault appears to be after a failed Compact of the database.

The MSysCompactError table has the following entry
ErrorCode: -3012
ErrorDescription: Undefined function 'Cstr' in expression
ErrorRecid: <null>
ErrorTable: MSysRelationships

I am currently trying to determine which version of Access the customer had installed.
I know there were a few 'duff' Office Security updates during late 2019 and early 2020.

 

[CJ_London]

Cstr is a reserved word - perhaps you have used it as a field or table name used in your relationships - or you have used sql to create a relationship using cstr to provide a relationship name that hasn't worked properly.

It is well known that as time goes on, access has become less tolerant of things like reserved words

 

[Steve Larwood]

Thanks for the thoughts.
We have over 700 installations, going back over 10+ years, with the same basic database structure from Access 2007 through to Access 2016.
This fault has only been seen on a few systems and all happened in the early months of this year.
All were systems that had been running fine for 2+ years.
I cannot yet determine which versions of Access were in use as I need to get back to the end users.
There are 5 databases used and on the PCs where the fault occurred it impacted each compacted database.
One database only has a single relationship and we definitely did not have any reserved words.