Access 97 table Error problem

I have a database that is shared by 5 people.

In one of the main tables that includes autonumbers, I have a record with #Error in one of the fields. Whenever I try to alter the field, I get a message saying "Record is opened by another user".

This always happens, even when I am the only one accessing the database.

I cannot import or export this table as I always get the above arror.

I have run repair, which completed successfully without fixing the problem and I have attempted to compact, which stalled, with a "no read permission" error.

Does anyone know how to fix it?
AlbieAsked:
Who is Participating?
 
Mach1proConnect With a Mentor Commented:
I have run into a similar problem before. Basically you have corrupted data  The only way to fix the problem is to  delete the record with the #Error.  You should be able to copy the data from all of the other fields in the record before deleting.
0
 
cjvandykCommented:
A couple of questions:
1. Have you tried opening the database in "Exlusive" mode?
2. Are you using the "Owner" ID to sign in?

I would suggest you use the owner or creator id for the table and do the following:
1. Start Access.
2. Click "Cancel" on the standard open dialog you get.
3. Now click "File" and then "Open Database"
4. Now locate your database file and select it by single clicking it.
5. On the right hand side of the dialog window there are 3 buttons, "Open", "Cancel" and "Advanced" under each other.  Under the Advanced button is a "Exclusive" check box.  Check the check box.
6. Click Open button.
7. Now go about your repair actions.

This is the best way to lock the db and eliminate the kind of "other user" error messages you are getting.
Let me know if it helps...
0
 
dcordnerCommented:
Hi,

When you exit the database, and no one else is using it at all, do you still have an .LDB file in the same folder as the database?  Does the database reside on a server, or is it in a shared folder on a workstation?  If the answer to the first question is "Yes", power down all workstations and delete the .LDB file from the machine hosting the database.  It's a simple solution, but it's worth a try, since it seems that Access believes the record in question is locked by another user.  This can occur when a workstaion that is accessing the database crashes, or is shutdown in some way where it will not report a record lock release for the current record.

Also, what version of service pack for Access are you using?  You can get this info through the  Help Menu, by selecting the "About Microsoft Access" menu option.

--

Don
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
FlSteveCommented:
Albie,

Good to see you here.  Hope your problem gets resolved.  Will be watching to see.  Isn't this a great website?

FlSteve
0
 
GudareCommented:
Mach1Pro has it correct, the bookmark index on the table went corrupt and destroyed that record. Clear it out if you can, the problem is that because it's being edited, you typically *can't*. It's a shame.

What you do is import the table to a new DB, structure only, then import it back (this clears the index). Add a field called OldIndex.

Now, run an append query against the table in the DB. This typically works, else, copy and paste_append the records over. Use the old index field you added for the new one. You'll see why shortly. One problem. Your indexing is about to get turned to useless data.

To deal with that issue, what you end up doing is having a new autonum field and an 'old index field'. Then you connect the existing tables that used the old index to the old index field in a series of update queries, and change the *other table's* indexes to the new value the records have in an autonum field.

Simple, easy? No. Certainly, see if you can't find a way to outright delete the file so you don't have to go through that. Else...

Good Luck.

-Craig
0
 
GudareCommented:
ACK, forgot to mention, when importing it back, use a *different* name, or you'll overwrite all your existing data. :)
0
 
AlbieAuthor Commented:
Thanks all for these suggestions, I have ended up deleting the 2 #error entries in the table. I may attempt to recreate the table to put them back in correctly, but in a slightly different way than suggested. The table records are delivery docket information and the autonumber field is the delivey docket number, so I can't upset the sequence.  I'm looking at creating a table with the autonumbering already in and using an update query to import the rest of the record data from the old table. This will allow for gaps in the autonumbering due to operator errors. I will then list the unduplicated records in the new table and then delete them. Well that's the idea, wish me luck
0
 
AlbieAuthor Commented:
This has given me a good idea of how to go about relving the lack of data error due to deleting the 2 corrupt files
0
All Courses

From novice to tech pro — start learning today.