Question Rename Database and Corruption Error Goes Away?

[swat]

I posted this same issue back in 2014 and never found a definite solution. I am working with a government agency that uses hundreds of Access databases. We are upgrading from Windows 7 to Windows 10 as well as Office 2010 to Office 2013. It appears that the problem has resurfaced with users after they have been upgraded. Here is what's happening.

1. Users open a database and get a "Access was previously opened with a serious error. Do you want to open?"
2. I make a copy of the database (e.g. MyDatabase.accdb to MyDatabaseCopy.accdb)
3. I open the copied database and run Compact and Repair.
4. I test the updated database several times and can open it without error.
5. I delete the original database.
6. I rename MyDatabaseCopy.accdb to MyDatabase.accdb. (Now it should be back in to full production mode).
7. When we open MyDatabase.accdb we get the corruption error.
8. If I change the name back to MyDatabaseCopy.accdb, it opens without error.

So it ends up that we need to make the renamed database the new production database. Any users that have shortcuts need to recreate their shortcuts. If there is VB code inside that references the database name, that code needs to be changed. If the corrupted database is a back-end for other databases, the links in the front-ends need to be changed.

Why would a corruption error appear and disappear merely by changing the name of the database? Has anyone else run in to this problem?

 

[The_Doc_Man]

When you do the rename FROM the old name, do you at the same time delete the .LDB file? Or otherwise verify that it is no longer there?

Might not be the .ACCDB that is corrupt.

 

[CJ_London]

not come across it but one of the other things to do is to decompile the code.

Also, perhaps you have some code that runs when the db is opened that is intended to prevent users from opening a front end on the server instead of their local machine - so it throws out this message?

 

[swat]

Interesting idea. But, yes, the ldb does not exist when I perform the rename and copy. Thanks for thinking of it.

 

[swat]

not come across it but one of the other things to do is to decompile the code.

Also, perhaps you have some code that runs when the db is opened that is intended to prevent users from opening a front end on the server instead of their local machine - so it throws out this message?

Thanks CJ!

I'm sure the second option is not happening, but a very good point about decompiling the code. I have not yet done that. I know that I have done it before and have an explanation somewhere. But would you happen to have instructions on how to do that? It's a command line statement, is it not?

 

[isladogs]

PMFBI but CJL appears to be offline and I have a link to hand which explains what decompiling does and how to do it. Yes its a command line switch: /decompile.
See http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

 

[Cronk]

@swat
Are users all opening the same database? That is, are they opening a front end copy of the database on their individual C drives?

 

[swat]

@swat
Are users all opening the same database? That is, are they opening a front end copy of the database on their individual C drives?

We have a couple scenarios (the problem is affecting multiple databases).

Scenario 1: The database is a single database and all users access that database on the network drive.

Scenario 2: The database is a split database and all users access the single front-end on the network drive.

I suspect your idea is to provide all users with their own front-ends on their local drives, which actually is not a bad idea. However, our institution users access several workstations in any given day, 24 hours a day. So making sure they always have a current copy, if/when changes are made/ would be a nightmare. And these users are not specifically tech savvy. (e.g. some users try to create shortcuts on their desktop but actually make a copy of the database on their desktop)

 

[isladogs]

Both scenarios are bad and WILL cause corruption.

You need to move to each multiuser database being split with each user having their own cop of the FE on their own computer with a shortcut on their desktop.

Don't rely on users doing this themselves. Network staff can set this up so it happens the next time each user logs in. They can also remove all existing items - both the actual databases and any shortcuts.

In addition, new versions can be automatically downloaded from the network when available

Doing the above should be done as a priority ASAP.

 

[jleach]

I strongly agree with ridders... this is one of the first things we learn about corruption prevention: the db gets split, everyone gets their own copy of the fontend to use (this is usually step 2, with step 1 being to turn off AutoCorrupt).

Having some sort of update/distribution scheme is essential to a multiuser environment. Nightmare it may be at first, but everything is hard before it's easy.

Cheers

 

[swat]

Both scenarios are bad and WILL cause corruption.

You need to move to each multiuser database being split with each user having their own cop of the FE on their own computer with a shortcut on their desktop.

Don't rely on users doing this themselves. Network staff can set this up so it happens the next time each user logs in. They can also remove all existing items - both the actual databases and any shortcuts.

In addition, new versions can be automatically downloaded from the network when available

Doing the above should be done as a priority ASAP.

Definitely agree and understood. However this would be an insurmountable task. We currently have 449 databases registered and upgraded and we are only half way through the upgrade process. These databases are developed by end users (some actually by inmates) and we currently have no control over when and how they are developed. Developing a Access development standard and process would take months, if not years. But perhaps we need to try this format with the current problem databases and begin the standard. Thanks for your feedback.

 

[jleach]

>> However this would be an insurmountable task <<

Can't be much worse than constant corruption and data loss issues, can it?

If nothing else, split all the databases first: this is usually a relatively easy process that's not generally risky in terms of breaking a bunch of things. That'll at least somewhat shield your data file from multiple people opening it at once (concurrency will go through a better channel at that point). That'd be my first step.

I'm not at all surprised to be in thread about corruption with this setup.

 

[swat]

>> However this would be an insurmountable task <<

Can't be much worse than constant corruption and data loss issues, can it?

If nothing else, split all the databases first: this is usually a relatively easy process that's not generally risky in terms of breaking a bunch of things. That'll at least somewhat shield your data file from multiple people opening it at once (concurrency will go through a better channel at that point). That'd be my first step.

I'm not at all surprised to be in thread about corruption with this setup.

Thanks, everyone, for your continued comments. I am in the process of spitting the database with the intentions of giving each user their own front-end. I think we all agree that this is the best way to go and will most likely eliminate the corruption.

I also wanted to let you know that I am working with Microsoft to look at the issue and, so far, they have been responsive and helpful. I will post the final outcome once we determine what that is.

 

[swat]

Thought I would post a final response from Microsoft after working with them rather extensively on this issue. There actually IS a known problem for this issue that Microsoft is working to resolve. I have attached 2 email correspondences that I had with Microsoft that explain 1) how to prevent database corruption and 2) the current known problem and explanation on how to resolve.
I hope this may help someone else out there.
Neil

 

[The_Doc_Man]

I'm going to make a suggestion to any moderator who could arrange this. The letters from Microsoft are truly golden in terms of the advice offered. If there is any way to perhaps post this information in a special place where it will be more easily referenced, it might be a great help to others. I am not sure where it would be posted, but it is worthy of being kept aside in a special place.

 

[isladogs]

DONE!

The posts and attachments have been copied to the Theory and Practice section and made into a sticky post.
The two emails don't provide any new information but they are extremely thorough and clearly explained.

Mind you I believed the recent issues with a Windows update causing database corruption had since been fixed.
The first email suggests not.

 

[The_Doc_Man]

Separate comments from the "move/copy" post #15...

These databases are developed by end users (some actually by inmates) and we currently have no control over when and how they are developed.

Is this perhaps a correctional facility and are you teaching your inmates programming skills or Office skills to make them more employable on release? (Your screen name suggests a police involvement, after all.)

If so, then it is a GREAT opportunity to teach them about some optimization tricks such as the importance of splitting shared databases. As to the other problem, search this forum for the trick of launching the shared FE files by having a little .BAT file copy the shared FE to the user's local workstation.

Your issue will of course be that they develop these databases in isolation. If you emphasize that a database graduating from "in development" to "ready for sharing" requires this special step of being moved to a server for more efficient sharing, you can reinforce the lesson.

As to WHY it is necessary, you can probably search this forum for articles about shared-file locking. In essence, by isolating the FE file, you isolate the locks for everything inside the FE. Then the only shared locks are on the BE file, which reduces the amount of sharing. This might make a good teaching opportunity.

Of course, if my take on this situation is wrong, forgive me for tossing my 2 cents' worth into the wrong place.

 

[The_Doc_Man]

Thanks, Colin! Even if there is nothing inherently new, at least a lot of suggestions are all in one place.