Can't use "Compact & Repair Database"

[Alex]

When I try to use the "Compact & Repair Database"
utility, I get the following error message:

"The Microsoft Jet database engine stopped the process
because you and another user are attempting to change the
same data at the same time. (Error 3197)"

I am using the file in as a stand-alone file, with no one
else accessing it. The error message even comes up when I
try to run a report.

I've looked through the Web and Microsoft's knowledge
base for this error without luck. Any help will be
appreciated.

 

[GVaught]

This may be caused because you don't have MaxLocksperFile set high enough in
the Registry.
http://support.microsoft.com/default.aspx?scid=kb;en-us;209940&Product=acc2000

 

[MAC]

I have had this happen to me before. Somehow,
Access still thinks you are still in the program, even
when you have exited the program. If your problem is the
same as mine was then...

Look in the directory where your Access Database is (*.mdb)
you will find that when you open your database a new file
is created with an extension of 'ldb' (*.ldb). The Type
Description states that it contains Record-Locking
Information (The Icon will have a picture of a lock on
it). When things are working normally and you exit your
access database this file (*.ldb) should disappear from
the Directory.

In the event that this file (*.ldb) does not disappear
when you exit and you are CERTAIN that there are no other
users logged in; this file can be deleted. HOWEVER, for
safty sake, I would recommend that you create a temporary
directory and copy and paste both the database and the
file (*.ldb)into it. Then delete the file (*.ldb) and run
your compact and repair. If your database looks OK, then
you can delete the bad Database and file.

Hope this works for you.

MAC

 

[Guest]

Thanks. Tried both possible solutions offered (by MAC and
GVaught) but, unfortunately, no luck.

 

[Tony Toews]

In the event that this file (*.ldb) does not disappear
when you exit and you are CERTAIN that there are no other
users logged in; this file can be deleted. HOWEVER, for
safty sake, I would recommend that you create a temporary
directory and copy and paste both the database and the
file (*.ldb)into it. Then delete the file (*.ldb) and run
your compact and repair. If your database looks OK, then
you can delete the bad Database and file.

I completely agree with making a backup of the MDB but the LDB isn't
the slightest bit useful.

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 Toews]

Thanks. Tried both possible solutions offered (by MAC and
GVaught) but, unfortunately, no luck.

For more information on corruption including possible causes,
determining the offending PC, retrieving your data, links, official MS
KB articles and a list of vendors who state they can fix corruption
see the Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm

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

 

[Guest]

Wow, I am glad I am not the only one who has that problem. Try all the suggestions that the other folks have given first. if none of that help, look for a #ERROR value in your fields that are of type Memo. It's a known problem with Memo fields. They sometimes get corrupted when two or more users are trying to make changes to the database. What I have done is actually found the record that has the #ERROR in its memo type fields, and physically remove that record, provided that you have taken down all the info you need for the record to be re-inputted, and delete the record. The tough part is trying to find the record, because you can't query into the corrupted record. I personally had to do a "binary search", physically splitting the data in half to find out with half has the error, until I find the error. But once the corrupted record is gone. You are good to go

for more help, go to the knowledge database of support.microsoft.com and type in corrupted memo field. What I am suggesting is the last ditch effort AFTER every other repair way is exhausted. Took me 3 days to repair our database.

 

[Tony Toews]

Wow, I am glad I am not the only one who has that problem.

Try all the suggestions that the other folks have given first. if none of that help, look for a #ERROR value in your fields that are of type Memo. It's a known problem with Memo fields. They sometimes get corrupted when two or more users are trying to make changes to the database. What I have done is actually found the record that has the #ERROR in its memo type fields, and physically remove that record, provided that you have taken down all the info you need for the record to be re-inputted, and delete the record. The tough part is trying to find the record, because you can't query into the corrupted record. I personally had to do a "binary search", physically splitting the data in half to find out with half has the error, until I find the error. But once the corrupted record is gone. You are good to go.

FWIW if you visit
http://www.granite.ab.ca/access/corruption/corruptrecords.htm you will
find sample code to programmatically locate corrupt memo fields.

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