Rename Database and Corruption Error Goes Away? (1 Viewer)

swat

Registered User.
Local time
Today, 12:51
Joined
Jun 20, 2013
Messages
17
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
 

Attachments

  • Access Database Corruption - Known Issue Renaming DB.docx
    13 KB · Views: 104
  • Access Database Corruption Suggestions from Microsoft.docx
    22.3 KB · Views: 90
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 10:51
Joined
Oct 29, 2018
Messages
13,251
Hi Neil. Thanks for this info. I read both of your attachments, but neither of them discussed about renaming the database. Where did your topic title come from?
 

swat

Registered User.
Local time
Today, 12:51
Joined
Jun 20, 2013
Messages
17
Hi theDBguy,
The original issue was as follows:

1. User opens Access database and receives a corruption error.
2. I make a copy of the database and run a Compact and Repair and error goes away.
3. I ask several users to test the copy to make sure it's working. All works fine.
4. I delete the original DB and rename the test copy back to the original name and, immediately, the corruption error returns.
5. If I just rename the database to something other than the original name, it works without any further maintenance.

I know we should create front/back-end applications and, unless everyone copies their own front-end, the issue could still persist. Many of these databases are created by the users and they don't really understand Access or good database design. And we have several users that can access the database so telling them to recreate their shortcuts to a newly named database is a hassle.

The main question was "Why does the database work find if I only rename it?"

Microsoft never provided me with a final answer and the issue just eventually went away so I have not pursued it anymore.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:51
Joined
Oct 29, 2018
Messages
13,251
Hi. Thanks for the clarification.
 

HiTechCoach

Well-known member
Local time
Today, 12:51
Joined
Mar 6, 2006
Messages
4,345
I have experienced renaming a database helping for many years, starting with Access 2000. It has something to do with the VBA code source and the VBA that is compiled on demand getting out of sync. Leading to an inconsistent state. Or strange things start happening.

Do your database with the issue have any VBA code?
 

Micron

AWF VIP
Local time
Today, 13:51
Joined
Oct 20, 2018
Messages
3,471
It has something to do with the VBA code source and the VBA that is compiled on demand getting out of sync.
I learned that the decompile switch is supposed to take care of this situation; i.e. it completely rebuilds the compiled code based on what you see as the written code, which as you say, can get out of sync.


Recently, and fairly certain it was this forum, that a discussion revolved around destroying/closing objects - as in Set rs = Nothing. Some basically said 'why bother'. I read the docs (links above) and found this to be of interest:

Here are several precautions that we recommended to reduce the risk of corruption:
(several bulleted points, then)

[FONT=&quot]When programming, close all DAO objects and ADO objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.[/FONT]
I'm glad my practice has long been to 'bother' doing that.


The docs are a keeper, for sure. Thanks for posting them, swat.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
18,378
Thank you, Neil, for posting this information. Micron, I'm glad you posted your comments about closing things because many folks have told me that I am somewhat off base with my viewpoints. I have often advocated as a matter of good practice that one should follow the advice given in the book "Everything I Needed to Know I Learned in Kindergarten." This includes closing what you open and putting away what you took out.

I am pleased to see in the 2nd link of post #1 of this thread that a member of the Access team advocates closing recordsets and other structures before exiting. While it might indeed not ALWAYS be needed, I have always felt that leaving things open when you are finished with them is a sloppy form of programming. Having made that comment, let the chips fall where they may.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 13:51
Joined
Apr 27, 2015
Messages
3,083
Here are several precautions that we recommended to reduce the risk of corruption:
(several bulleted points, then)

When programming, close all DAO objects and ADO objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.

This topic has been debated, in some instances in a hostile manner, in this forum many times. There is one particular thread that I believe ChrisO was the prolific contributor that spoke to this practice but I cannot find it for the life of me. A pity too because it would be great if we could cross reference any debate on the topic to this post.

As a side note, I read many posts from ChrisO that makes me glad I was never in his crosshairs: The man could be BRUTAL! Brilliant to be sure, but brutal...
 

deletedT

Guest
Local time
Today, 18:51
Joined
Feb 2, 2019
Messages
1,220
I've done this renaming to get ride of my Front ends corruption for years. I can't remember why and how I learned it, but for me it's been a common sense.

You may not believe, but for the past two years, instead of renaming, I copy the corrupted Front End to another folder, shift double click and compile it. And copy it back to its original folder. The problem is gone.

If I shift double click it in its original folder and before moving it to another folder, the FE opens with a error message : File can not be found and closes.
 

HiTechCoach

Well-known member
Local time
Today, 12:51
Joined
Mar 6, 2006
Messages
4,345
Unfortunately for swat (the original poster), the databases are user-generated and not split. They are also on a network share.
The best practices for preventing corruption are available for swat.

User-generated, single user (not split) databases stored on a network share is a huge challenge to support.

Daily backups and using Windows’ shadow copies become critical to prevent any data being lost.

Renaming by moving folders

As Tera points out you can move the database to another folder. Why I think this also works is Access looks at the full path, not just the database file name. Moving folders changes the full path name like renaming the database file also changes the full path name.
 

Micron

AWF VIP
Local time
Today, 13:51
Joined
Oct 20, 2018
Messages
3,471
This topic has been debated, in some instances in a hostile manner, in this forum many times. There is one particular thread that I believe ChrisO was the prolific contributor that spoke to this practice but I cannot find it for the life of me.
Are you referring to this?
https://www.access-programmers.co.uk/forums/showthread.php?t=234743

Per that thread - the notion of invoking an application Quit as an example of why to not close a recordset is of little value AFAIC. Rather it is an example of a common code problem - not understanding the interaction and order of events. Maybe it's also an example of poor design? Who would invoke Application.Quit from a form with a record source anyway?
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
18,378
Having participated in that thread, I have only this regret: That ChrisO is no longer with us so that we could have one more argument about this topic in light of the letters from Microsoft embedded with post #1. We all miss you, ChrisO.
 

Micron

AWF VIP
Local time
Today, 13:51
Joined
Oct 20, 2018
Messages
3,471
...we could have one more argument about this topic in light of the letters from Microsoft...
Perhaps unfair to say based on one thread but I suspect he/she would say the letter offers no proof and the writer is guilty of simply doing what the rest of us are doing.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 13:51
Joined
Apr 27, 2015
Messages
3,083
Perhaps unfair to say based on one thread but I suspect he/she would say the letter offers no proof and the writer is guilty of simply doing what the rest of us are doing.

I think that statement is actually spot on...
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
18,378
Micron - you would be right except that the letters give an explicit mechanism related to the advent of SMBv2 and v3, and in one of the letters we learned that SMBv2 is now the default for Win7 and Win10.

You can have ordinary locking, but the advanced SMB versions allow for "Opportunistic" or "Leased" file locking. The purpose of the new locking options is to reduce network traffic by taking over the buffer for a longer time and not forcing a writeback every time your actions would update the contents of the buffer. (SMBv1 by contrast WOULD have to write back the buffer every time you update its contents.)

The new methods force an exclusive lock even though Access is opened for sharing because that is the only way to safely NOT write back the buffer with every update. You can only defer the write back if you know nothing else can happen to the buffer.

According to the letters, what happens is that when there is a lock "collision" with one of the new lock types, you have to "break" the lock (which was exclusive, remember). And apparently there is an issue with breaking exclusive locks that could lead to buffer invalidation. This next statement is conjectural on my part based on my understanding of operating systems: It sounds like there might be a race between the former lock owner and lock breaker and if the breaker wins, the buffer is invalidated and that could lead to destructive interference - i.e. database in an inconsistent state.

At LEAST until Microsoft can figure out what is eating their locks on this one, it would be wise to close anything that no longer needs to be open, because as long as it is open, the buffer is still locked since the optimization is to defer write back operations as long as possible. The way to prevent the problem is to always close anything that potentially involves any disk buffers. Recordsets involve disk buffers so definitely would run afoul of the problem. The letter also mentioned xxxdef structures that could be opened and that should be closed sooner rather than later.
 

Micron

AWF VIP
Local time
Today, 13:51
Joined
Oct 20, 2018
Messages
3,471
I don't get what your "lock" comments have to do with what I wrote about, which was destroying (or not) objects that one creates and whether or not failing to do so does or doesn't induce corruption.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 12:51
Joined
Feb 28, 2001
Messages
18,378
Ah, simple enough. Let's use a recordset for the discussion.

The letter clearly suggests that ANY object on a disk will be managed through Windows file locks and implies that the locks are per buffer as opposed to per file. A recordset object refers to the contents of a table directly or through a query. That set of records resides on a disk so will be the point of corruption if this error occurs.

For Access, the mechanism of corruption is almost always the same though often from various different first steps. You corrupt a database when you start making changes to that database but don't finish the process. This leaves the database in an inconsistent, half-updated state. The "inconsistent state" error message we have been discussing is quite literal here.

Based on what I read in the two letters, this inconsistent state occurs when Access opens a file for sharing BUT Windows in its infinite (?) wisdom takes out a non-shared lock on a buffer AND sets up one of the new buffering optimization schemes.

In older versions of Windows, the SMB protocol would write back buffer contents every time you updated the buffer, even if your next operation was in the same buffer. In SMBv2 and v3, though, they have an option to NOT write that back immediately. They do that to reduce network traffic.

When you have a recordset open and there is a currently selected record, the active copy of that record resides in a memory buffer corresponding to the place on the disk where that record resided BEFORE you opened the recordset. So you have this recordset and do some sort of .Edit/.Update sequence and the active buffer gets updated in memory. The new protocols, however, allow that buffer to NOT get updated to disk right away.

The optimizer cannot tell why you pause after your most recent update to the recordset. So it does nothing. That new optimization doesn't know that you are done with the recordset object until you CLOSE the recordset and start to destroy it. At that point, the lock and buffer optimization must flush the modified buffers back to the disk. This is where your "destroying (or not)" issue kicks in.

IF before this point of closure, another thread or a different part of your own process thread try to touch that buffer again, you have to unlock it. According to what the letter and linked articles describe, the buffer can be invalidated before it gets written back. If you were doing a sequence of updates and the last buffer doesn't get written back correctly, you now have a corrupt database.

The way to avoid that is to CLOSE whatever object you opened and set it to Nothing. That action would flush the disk buffer and destroy the object. That will prevent the corruption.

Did that help you see the mechanism any better?
 

swat

Registered User.
Local time
Today, 12:51
Joined
Jun 20, 2013
Messages
17
I learned that the decompile switch is supposed to take care of this situation; i.e. it completely rebuilds the compiled code based on what you see as the written code, which as you say, can get out of sync.
Thanks Micron! I'm pretty sure I tried the decompile (I was trying everything in the book) but I for sure will try that again the next time it happens.
 

Users who are viewing this thread

Top Bottom