Solved

Access 97 table Error problem

Posted on 2001-07-15
8
346 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 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
Back Up Your Microsoft Windows Server®

Back up 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.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 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