Recurrent error when compacting mdb

[Arnaud Lesauvage]

Hi group !

I have to big backend mdb files that I compact on a daily basis from the task scheduler.

This has worked smoothly for months. Now (fro maybe 2 months), the process very often crashes with a "Could not find field 'Description'" error !

I know that this is some kind of database corruption, but every time I hit this error I reimport all the tables in a brand new database, and the error still raises one or two days later !

What might be causing this ?

Please also note that I "suspect" that this arises only on the computer running the compact script. It is very hard for me to confirm that, since those are production databases, and I cannot work on them or take them offline during office-hours.

Any advice on this would be greatly appreciated !

Best regards

 

[Arnaud Lesauvage]

Arnaud Lesauvage a écrit :

I know that this is some kind of database corruption, but every time I hit this error I reimport all the tables in a brand new database, and the error still raises one or two days later !

Just a quick comment on this : I've read Microsoft's KB article number 296389 about this : "ACC2000: Table Corruption After Referential Integrity Checks", but this does not apply here, since the mdb that crashes most often does not have any memo field, nore any referential integrity check !

Regards

 

[Tom Wickerath]

Hi Arnaud,

When you import all tables into a new DB container, are you selecting the
option to import relationships? This is just a guess on my part, but you
might try importing the tables only, and then re-establishing the
relationships in the new database (instead of importing a potentially corrupt
relationship).

If that does not work, then my next attempt at fixing this would involve
deleting all relationships from a copy of your database, and then deleting
all indexes from every table. Yes, I know this may be painful. Compact the
database and close it. Then create a new DB container, disable Name
Autocorrect first, and then import your tables. Finally, add all of your
indexes back to each table, and re-establish the relationships between the
tables. This way, if you have a corrupt index somewhere, you will avoid
importing it into your new database.

Also, do not use the Compact on Close option.

Good Luck,


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

['69 Camaro]

Hi, Arnaud.

This has worked smoothly for months. Now (fro maybe 2 months), the process
very often crashes with a "Could not find field 'Description'" error !

Whenever this happens, check the MSysCompactErrors system table. It will
list whatever was deleted in order to safely repair the rest of the
database. This may include objects and even records in tables. Whatever
objects are listed in that table, you should import them from a known good
backup into your new database or else rebuild them from scratch.

What might be causing this ?

Most likely a corrupted index or memo field in a table that you keep
importing into the new databases. However, the root causes of corruption
are most commonly from sharing a multiuser database (i.e., an unsplit
database or users sharing the front end of a split database on the network),
faulty network equipment, users turning the power off on their workstations
when the Access database is still open and running, and intermittent power
outages.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Arnaud Lesauvage]

'69 Camaro a écrit :

Whenever this happens, check the MSysCompactErrors system table. It will
list whatever was deleted in order to safely repair the rest of the
database.

Hi ! Thanks a lot for your answer.
I have no such table in my mdbs (I have all system and hidden objects visible, so I can see all the MSys... tables, but not this one) !

I think that the error arises while access is copying the mdb to bd1.mdb, but when I aknowledge the error message, the bd1.mdb is immediately deleted, and my original mdb is left unchanged.

Most likely a corrupted index or memo field in a table that you keep
importing into the new databases. However, the root causes of corruption
are most commonly from sharing a multiuser database (i.e., an unsplit
database or users sharing the front end of a split database on the network),
faulty network equipment, users turning the power off on their workstations
when the Access database is still open and running, and intermittent power
outages.

I do have a split database (backend/frontend), and my users all have their own copy of the frontend.
The problem might indeed arise when a frontend crashes (yes, this does happens with MSAccess !!!), or when some network packet is lost.
I don't understand why this would be so bad now though, because the load on this DB is not a lot higher than before !

Regards

 

['69 Camaro]

Hi, Arnaud.

I think that the error arises while access is copying the mdb to bd1.mdb,
but when I aknowledge the error message, the bd1.mdb is immediately
deleted, and my original mdb is left unchanged.

Correct. The MSysCompactErrors system table will be written to the new
database file (not the original one), which is first named db1.mdb. If this
new database file is deleted, then you won't be able to read what's in that
table.

I do have a split database (backend/frontend), and my users all have their
own copy of the frontend.
Excellent!

I don't understand why this would be so bad now though, because the load
on this DB is not a lot higher than before !

Do you have any wireless devices connected to the same network? It doesn't
even have to be one of your Access users. Any wireless device with an
intermittent connection to the network where an Access database is open can
cause havoc on that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Arnaud Lesauvage]

'69 Camaro a écrit :

Correct. The MSysCompactErrors system table will be written to the new
database file (not the original one), which is first named db1.mdb. If this
new database file is deleted, then you won't be able to read what's in that
table.

The next time I have this error, I'll just copy the bd1.mdb before acknowledging the error. I hope that I will get some hints from this MSysCompactError table then !

Do you have any wireless devices connected to the same network? It doesn't
even have to be one of your Access users. Any wireless device with an
intermittent connection to the network where an Access database is open can
cause havoc on that database.

Really ! Well, this is really scarry ! I knew from experience that mdbs where very unstable, but that is another bad surprise !

Actually we have no wireless device here, but we have a quite heavily loaded network (a lot of DB work, plus a lot of big file moving on the network, plus DFS replication between two windows servers...).

I'll wait till I get some hints from the error table before I bother you again !
Thanks a lot anyway, you've been very helpful !

Regards

 

['69 Camaro]

Hi, Arnaud.

I knew from experience that mdbs where very unstable, but that is another
bad surprise !

Access (Jet) databases are sensitive to unstable networks, much like the
proverbial canary in a coal mine. However, a well-designed Access database
application on a workstation with a reliable operating system and network
can run daily for years without a glitch.

I'll wait till I get some hints from the error table before I bother you
again !
Thanks a lot anyway, you've been very helpful !

You're welcome and good luck!

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

Hi Arnaud,

Actually we have no wireless device here, but we have a quite heavily loaded
network (a lot of DB work, plus a lot of big file moving on the network, plus DFS
replication between two windows servers...).

In your initial post, you wrote: "I have to big backend mdb files that I
compact on a daily basis from the task scheduler." Is your network so busy
at the time that you have scheduled in task scheduler? I would think that you
would have picked an time off normal operating hours, when most people are
not at work.

Do you have any functionality in the FE application that closes the
application automatically for inactive users? I once helped troubleshoot a
corruption problem, reported in this newsgroup, that turn out (after much
discussions back-and-forth) to be caused because one user at the company was
leaving work without closing the application. The company in question had set
up their system to automatically turn off all PCs in the office at 10:00 PM.
This was causing Access to crash hard, and the backend file was slightly
corrupted each morning (could be fixed with a compact and repair, but
nevertheless, quite disturbing to the DBA involved).

You might also examine these two documents for any clues:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arnaud Lesauvage]

Hi Tom !

Tom Wickerath a écrit :

Hi Arnaud,


In your initial post, you wrote: "I have to big backend mdb files that I
compact on a daily basis from the task scheduler." Is your network so busy
at the time that you have scheduled in task scheduler? I would think that you
would have picked an time off normal operating hours, when most people are
not at work.

Indeed, this script runs at night.
The network might not be idle at that time though, because a lot of other process run off operating hours. That is sill a time when the load is a lot lower thant during the day.

Do you have any functionality in the FE application that closes the
application automatically for inactive users? I once helped troubleshoot a
corruption problem, reported in this newsgroup, that turn out (after much
discussions back-and-forth) to be caused because one user at the company was
leaving work without closing the application. The company in question had set
up their system to automatically turn off all PCs in the office at 10:00 PM.
This was causing Access to crash hard, and the backend file was slightly
corrupted each morning (could be fixed with a compact and repair, but
nevertheless, quite disturbing to the DBA involved).

No, I have no such functionnality.
My script checks for an ldb file match the name of the backend before trying to compact, so that it does not try if anybody has an active connection to the backend.
Computers do not turn off automatically, and I my users very rarely forget to turn off their computer (it might happen once in a while, but not often enough for me to really care about this).

You might also examine these two documents for any clues:

Preventing Corruption (Allen Browne)
http://allenbrowne.com/ser-25.html

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm

Thanks a lot for the links, they are very useful !
In fact, there are two points that I am going to watch :
"5. Do not edit the text of a module while it is running (i.e. in break mode). If a project is compiled, and you edit the text while it is running, the Compile item on the Debug menu may remain greyed out. This may indicate that Access has become confused between the different copies it keeps of the code: the compiled code, the text-version of the code, the text-version of the temporary copy that it creates when you edit the form's module, and the version that is actually running. Refusing to edit code in break mode significantly reduces corruption in my experience."

I do this from time to time. Since that is not really necessary, I won't do this anymore !

"13. Unless you really need it, turn off Record-Level Locking (as opposed to Page-Level Locking.) Page-Level locking is more efficient to execute, and works more reliably in some circumstances. In Access 2000 - 2003, uncheck the box under Tools | Options | Advanced | Open Databases Using Record-Level Locking. In Access 2007, go to Office Button | Access Options | Advanced."

Well, I did turn on Record-Level Locking, but I fear I can't work without it.
Some users open recordsets in tables where other users do large inserts. We already have some problems with locks with record-level locking activated, I think it would get worse if we reverted back to page level locking.


Thanks a lot for the hints !

Regards

 

[Tom Wickerath]

Hi Arnaud,

No, I have no such functionality.

You might want to consider adding it, while still maintaining your existing
code that checks for a matching .ldb file. This way, a user is not allowed to
keep your application open all day, unless they are actually using it. I
include this type of functionality in my multi-user applications, with a
cut-off time of 15 or 20 minutes of inactivity. I discuss this technique in
my Multiuser Applications paper (see "Close inactive sessions"), available
here:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

In fact, there are two points that I am going to watch :
"5. Do not edit the text of a module while it is running (i.e. in break mode).

I do this all the time, myself. If anything, it would lead to possible
corruption in the VBA project. However, you are dealing with a split
application, and your corruption issue involves the BE file, which typically
does not have any VBA code. In any case, even if you were editing code live,
you could not be doing so in the production BE application unless no one else
needed to use the database at the same time. The one thing I do recommend is
that you remove the Compile on Demand option that is set by default, in the
VBE (Visual Basic Editor). I don't have a direct link handy, but go to this
link, then scroll down a bit on the page to the topic "Avoid Compile On
Demand":

http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Well, I did turn on Record-Level Locking, but I fear I can't work without it.

I use it as well. In fact, I even run startup code, based on this KB
article, to help ensure that I get record level locking, since this option
turns out to be a request, not a demand:

"PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60"
http://support.microsoft.com/?id=306435


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

In fact, there are two points that I am going to watch :
"5. Do not edit the text of a module while it is running (i.e. in break mode). If a project is compiled, and you edit the text while it is running, the Compile item on the Debug menu may remain greyed out. This may indicate that Access has become confused between the different copies it keeps of the code: the compiled code, the text-version of the code, the text-version of the temporary copy that it creates when you edit the form's module, and the version that is actually running. Refusing to edit code in break mode significantly reduces corruption in my experience."

I do this from time to time. Since that is not really necessary, I won't do this anymore !

And I do that all the time and have not had any problems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arnaud Lesauvage]

Tom Wickerath a écrit :

You might want to consider adding it, while still maintaining your existing
code that checks for a matching .ldb file. This way, a user is not allowed to
keep your application open all day, unless they are actually using it. I
include this type of functionality in my multi-user applications, with a
cut-off time of 15 or 20 minutes of inactivity. I discuss this technique in
my Multiuser Applications paper (see "Close inactive sessions"), available
here:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

OK, I'll think about it then. Thanks for the link !
I won't worry about the two other points (live code editing and record level locking).

Thanks again for your hints !

Regards

 

[Arnaud Lesauvage]

'69 Camaro a écrit :

Correct. The MSysCompactErrors system table will be written to the new
database file (not the original one), which is first named db1.mdb. If this
new database file is deleted, then you won't be able to read what's in that
table.

OK, the error arose again last night.
The problem is that there is not even a bd1.mdb file created, so I have absolutely no place to check for errors !

I ran the script again with the backend copied to another location, and it worked !
The problem must be with trying to compact over the network then. I do understand that this might cause some problems, but :
- I did run this script without a glitch for a long time over the network (why errors now ?)
- It will be quite hard for me to implement a "copy to local drive / compact / copy back to network drive". This is the kind of script that will fail from time to time, living a BE in an unusable state until I manually repair it. This is a quite bad option for me, since the point is to have as much automatism as possible, and at least to have things still working if a problem arises (that's the case now : the mdb is not compacted, but its still there, usable and not locked).

I thought there was an option on MSAccess.exe command line to specify a destination mdb when compacting ? I can't find it though...


Regards

 

[Tom Wickerath]

Hi Arnaud,

I thought there was an option on MSAccess.exe command line to
specify a destination mdb when compacting ? I can't find it though...

I think you will find it in this article:

http://support.microsoft.com/?id=209207

Perhaps you should consider investing in a tool from FMS to do this for you:

Total Visual Agent
http://www.fmsinc.com/products/Agent/index.html

The 2003 version can run as an NT service, which means that you can close a
security hole that you might have open right now, by leaving the machine
logged in.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________