Record is deleted. (Error 3167) & Compact & Repair error

[Scoob55k]

Hello,

I'm hoping someone can give me some insight here. This database began as a combination of 3 into 1. It has grown over the last year and its use and amount of users grows daily. Not sure what the limit is if there is any on the amount of users an Access DB can have.

Anyway, we recently tried to use the "Compact on Close" option in the database as it became corrupted at some point and we thought this might fix it. I turned that off as it seemed users were "End Tasking" the DB because of the extended time it took for the last user to close the DB during the Compacting process. This would corrupt the DB and a Compact and Repair would have to be done AFTER we got everyone out across our network (State).

Where we are now is the "Record is Deleted" error message. It pops up occasionally when working in the database and after clicking on a form command button. It doesn't seem to do anything negative as upon continuing, the DB works fine. The problem lies when I try to compact and repair the DB to get rid of this error. They DB errors out during the compact and repair process then states the DB in an unrecognized format and will not open. This is all on a copy I make with plans to copy over the "live" version during non business hours. Below is the entire verbiage on the error message. The two issues may not be related, just coincidental. Thanks!

Record is deleted. (Error 3167)
You referred to a record that you deleted or that another user in a multi-user environment deleted. Move to another record, and then try the operation again.

 

[PaulBricker]

You might get something out of this article that is helpful.

http://support.microsoft.com/kb/208799

Also, you can always try importing all the database objects into a new database and see if that helps.

MS says that Access will support 255 concurrent users (Access 2003) although I think that is a stretch.

Paul

 

[Scoob55k]

Thanks for the info Paul. I'll post if I figure it out or what works. 255 huh? That may be an issue.

Thanks!

 

[belovedcej]

The compact and repair can be an issue with multiple users. You really don't want to compact the database unless you have it open exclusively. What would be better is to look into a way to compact it once a week during off-hours. With only four user, a compact on close can cause corruption and multiple copies of a database mysteriously appearing. This occurs if someone has the database open when another person closes it.

Also, is your database split into a separate front and back end? You will have a safer environment, allowing for more users, if you do that. But be careful - the further nested into your file system the front and back end are, the slower the performance will be. It's better to have something split where the back-end is in the "top" of the file system, and the front end is installed locally on each person's computer.

I personally have never attempted to use an Access back-end for more than 5 users. More than that, and I use mySQL or SQLServer for the back-end and just use Access for my forms and reports. And I split the database for more than two users.

I highly recommend you look into this website:

http://allenbrowne.com/tips.html

He has much you may find valuable - particularly regarding split databases and database corruption. When you start getting the message that Access is in an unknown format, you database has some corrupted areas. A corrupt database can function seemingly fine for quite a while, but that's not a road you want to head down!

 

[lespaul]

But be careful - the further nested into your file system the front and back end are, the slower the performance will be. It's better to have something split where the back-end is in the "top" of the file system

Do you have a source that backs up this assertion?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[belovedcej]

The strongest source is my own experience with extremely slow response time over a network with deeply nested folders. (we're talking path names of around 200 characters). The higher I put the back end, the faster the performance.

Also, Microsoft mentions it. Here's the quote from their website:

http://support.microsoft.com/kb/891176

SYMPTOMS
After you upgrade your computer from Microsoft Windows NT 4.0 to Microsoft Windows 2000 or to Microsoft Windows XP Professional, you may experience slower performance in Microsoft Access-based or Jet database-based programs.


CAUSE
This issue may occur if one or more of the following conditions are true:

• You are working with a split database, and the backend database file resides in a folder that has a name that is longer than 8 characters. For example, the backend database file resides in a folder that has a name that is similar to the following:
BigFolderName

• You are working with a split database, and the name of the backend database file is longer than 8 characters excluding the three-character .mdb file name extension. For example, the backend database file has a name that is similar to the following:
BigDatabaseFileName.mdb

I know there are more references, because I found it several places when I was trouble-shooting my application, but I don't happen to remember where they are. Sorry I can't give you more sources at the moment - time is getting away from me.

 

[belovedcej]

This is a comment on my own post. I had said there is a problem with compact and repair in a multi-user environment. I'm thinking that may be a superstition on my part. I have seen what appears to be proof of that, but perhaps I have something else wrong with the database in question, because according to Microsoft, it only attempts to compact on close when the last user closes it, so no problem should occur.

So I add this just to say - take my thought there with a grain of salt - I may be wrong.

 

[Scoob55k]

Well, I kind of agree with your superstition. We added that (actually a partner did) so the DB would compact on close as we had a corruption issue then. The issues just escalated after that I thought. Could have been the beginning of this issue, but I turned it off as it was about every other day it seemed the DB was not allowing users in. My thought is users were End Tasking the DB as it took much longer to close. Even for a user who was not the last user to close it.

I really appreciate all the insight and advice here. Many of you are way more advanced than I with Access, however I've learned much and will increase my knowledge as well. I never really discovered the root cause of this crash, but upon the reminder to "rebuild" the DB by starting with a fresh blank DB and importing all items, we seem to be back up and going. Thanks again!