Solved

Access 97 table Error problem

Posted on 2001-07-15
8
340 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 50 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now