#Error appearing in some fields of an acess table

[noor]

Hi All,

I am new member of this forum. I am getting some error in my acess 2000 database. I have an application in VB with acess 2000 database at backend. I have used DAO in vb to open database. I am confused when user says that some error occors in the program. and When I open the database I get #Error written on all fields of certain records in a table. I can't delete these records because it gives error when I click on them. All I have to do is to repair the database by using tools menu. And it simply deletes all these records. And user has to re-enter all these records. I don't understand why this happen?
If someone help on this issue. I will be obliged.

Thanks

 

[The_Doc_Man]

If this is a shared database, then it might be that you are not using Access Workgroup security to your advantage. This COULD be a data locking problem. (Not going to swear to it, though.)

The thing that makes me think that it is sharing-related is that you said the "Error" records were deleted after a repair. This vaguely sounds like record corruption caused by sharing with no locks enabled, such that you get the phenomenon of "destructive data interference."

Now, if this is actually a single-user database, then I'll admit I've not seen this before.

 

[andrewf10]

Folks,

I've seen all sorts of examples of this, including users opening the database twice on their own machine and locking themselves out! And blaming me for it...

The fix I used was:

Tools...Options...Advanced...Default Record Locking should be set to Edited Record.

HTH
Andrew

 

[noor]

Thanks
Can you explain me what is the purpose of using this Record Locking option?

 

[andrewf10]

Noor,

Microsoft explain record locking on Access Help as follows:



"When you edit a record, Microsoft Access can automatically prevent other users from changing the record before you have finished editing it.

Giving one user exclusive access to a record is called locking. There are three locking strategies to choose from:

No Locks — Microsoft Access does not lock the record you're editing. When you try to save changes to a record that another person has also changed, Microsoft Access displays a message giving you the options of overwriting the other user's changes to the record, copying your version of the record to the Clipboard, or discarding your changes. This strategy ensures that records can always be edited, but it can create editing conflicts between users.

Edited Records — Microsoft Access locks the record you're editing, so no other user can change it. It might also lock other records that are stored nearby on your disk. If another user tries to edit a record that you've locked, Microsoft Access displays the locked record indicator in the other user's datasheet. This strategy ensures that you can always finish making changes that you start. It is a good choice if you don't have editing conflicts often.

All Records — Microsoft Access locks all records in the form or datasheet (and underlying tables) you're editing for the entire time you have it open, so no one else can edit or lock the records. This strategy is very restrictive, so choose it only when you know you're the only person who needs to edit records at any one time."


Hope this answers your question
Andrew