[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 97 table Error problem

Posted on 2001-07-15
8
Medium Priority
?
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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