[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 97 table Error problem

Posted on 2001-07-15
8
Medium Priority
?
354 Views
Last Modified: 2011-04-14
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?
0
Comment
Question by:Albie
8 Comments
 

Expert Comment

by:cjvandyk
ID: 6284318
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
 
LVL 6

Accepted Solution

by:
Mach1pro earned 150 total points
ID: 6284467
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
 
LVL 1

Expert Comment

by:dcordner
ID: 6284561
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Expert Comment

by:FlSteve
ID: 6286272
Albie,

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

FlSteve
0
 
LVL 2

Expert Comment

by:Gudare
ID: 6286943
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
 
LVL 2

Expert Comment

by:Gudare
ID: 6286947
ACK, forgot to mention, when importing it back, use a *different* name, or you'll overwrite all your existing data. :)
0
 

Author Comment

by:Albie
ID: 6288283
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
 

Author Comment

by:Albie
ID: 6288288
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question