Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


MS Access 2002 conversion question

Posted on 2006-05-16
Medium Priority
Last Modified: 2008-01-09
I have an Access97 app that I converted into Access 2002 (XP) version. This is a secured app (MDW).
After the conversion all the users are getting an error that the record is locked and cannot be edited.
This happens only when they try to edit a saved record. They can add a record but are not able to edit each of them.
I have set any bit field to zero as a default in the database.
This did not help.
Please advise.
Question by:devtha
  • 10
  • 6

Author Comment

ID: 16695677
The error they get is a WRITE CONFLICT error.
LVL 39

Expert Comment

ID: 16798295
Check Tools, options, advanced, default record locking is set to edited record.

Check that the forms causing the problem is set to edited record locking (data tab.)

Other info from my "canned" reply on the subject:
One of the biggest concerns in a multiuser environment is what happens if two or more people try to change the same information at the same time.  The way that this is handled is through locking.  If you look up "lock" in Access and VBA help, you will see record locking, all record locking, page locking, optimistic locking, pessimistic locking.  99% of the time you want pessimistic record locking (editied record) or optimistic record locking (no locks).  Then the question becomes optimistic or pessimistic locking.

The optimist says "It's unlikely that two people will be in the same record at the same time."  The pessimist says "Yah, right!  And most likely, it'll be my boss that's in the same record as me!!"  I our medical office, we have over 2000 patients in the database so you might think it is pretty unlikely that two people would be changing the same patient at the same time.  Until you realize that we are only seeing ten of those 2000+ on any given day.  Until I switched to pessimistic locking for the patient records, we were getting a lot of write conflicts.  I use optimistic locking for the rest of my db.

If you use optimistic locking, you can reduce the risk of write conflicts by breaking your forms up into subforms or tabs.  Records are saved when you move to a subform or another tab.  You can also put in "Me.Dirty = False" in after update events for some or all controls to save the record.

If you don't want the second person to be able to save changes to a record when someone else has not finished saving then that is called pessimistic locking.  The code below (thanks to Jim Horn if my memory serves) will notify the second person that the record is in use and by whom.  The biggest downside of pessimistic locking is the famous employee went to lunch without saving or logging off.  The computer goes to screen saver, locks and no one but the missing employee can save the record.  This can be resolved by 1: automatic logoff (of database or windows) after timed inactivity; 2: automatic save by setting the timer interval with the form's on dirty event and "Me.Dirty = False" in the timer; and 3: a reminder to save the record.

Function IsRecordBusy( _
    rs As Recordset, _
    Optional UserName As String, _
    Optional MachineName As String, _
    Optional CreateMsg As Boolean = True) As Boolean
      ' Accepts: a recordset and two string variables
      ' Purpose: determines if the current record in the recordset is locked,
      '          and if so who has it locked.
      ' Returns: True if current record is locked (and sets UserName
      '          and MachineName to the user with the lock).  False if the
      '          record isn't locked.
      ' From: Building Applications Chapter 12
          Dim ErrorString As String
          Dim MachineNameStart As Integer
10       On Error GoTo IsRecordBusy_Error

20        IsRecordBusy = False
40        rs.Edit                     'Try to edit the current record in the recordset.
50        rs.MoveNext
60        rs.MovePrevious
70        Exit Function               'No error, so return False.

80       On Error GoTo 0
90       Exit Function

100       If Err = 3260 Or Err = 3197 Or Err = 3188 Then   'Record is locked -- parse error string.
110           If CreateMsg Then
120               ErrorString = Error$
130               UserName = Mid$(ErrorString, 45, InStr(45, ErrorString, "'") - 45)
140               If UserName = "" Then UserName = "(unknown)"
150               MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
160               MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
170               If MachineName = "" Or MachineNameStart = 0 Then MachineName = "(unknown)"
180               MsgBox "This record is being used by " & UserName & " on station " _
                      & MachineName, vbExclamation, "Record Lock Warning"
190           End If
200       Else
210           MsgBox "Error: " & Err.Number & ", " & Err.Description & ", IsRecordBusy, " & Erl
220       End If

   On Error GoTo 0
End Function

Call the function with:
    Set rstForm = Me.RecordsetClone
    strBM = Me.Bookmark
    Set rstFormClone = rstForm.Clone()
    rstFormClone.Bookmark = strBM
    If IsRecordBusy(rstFormClone) Then
          Cancel = True
          Exit Sub
    End If

Author Comment

ID: 16801095
thanks those are good points...
I have come across this before in MS Access 97.
I had solved it as well.

Now that I converted from 97 to 2002 (back end SQL for both versions)
I am getting this error.
Users are able to add a record but once saved they cannot edit it.
Is this SQL server related?
Is this related to what you are saying above?
Is this related to something else?
The record lock is set to Edited Records on the form's data tab.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 39

Expert Comment

ID: 16803605
Can you edit the record in the table?
Yes - is allow edits in the form set to yes?
No - Open Access w/o the database (and with the db closed everywhere else).  Select open, select the database (do not open it), click on the down arrow to the right of the open button, select open exclusive.

Can you edit the record now?
Yes - probably a sharing issue.  First try closing the db and deleting the [database name].ldb file (in the same folder of the db).  You may need to reboot to delete it.

No - probably corrupt db:

Steps (approximately in order - check after each step) to use to fix a corrupted Access database.


2) Compact and repair

3) Use jetcomp.exe  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956

4) decompile: in run: msaccess "dbPathName.mdb" /decompile
   Compact/repair:  in run: msaccess "dbPathName.mdb" /compact
   compile: in VB editor: debug> compile

5) Use a backup (you have a lot of those -- yes?)

6) If you can identify one or a few forms or reports causing problems, copy the problem form/report from a backup.  Or copy and paste a new copy of the form/report then copy and paste the code from the old form/report into the new form/report.  If a module, copy the code, create a new module, paste the code into the new module.

7) Create a new database, use File> Get external data> Import to get all objects of the damaged database and set up the options again.

8) Try a recovery tool:

9) Hire a company to repair the database  http://www.pksolutions.com/services.htm

Additional information:
How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases

How to Troubleshoot Corruption in a Microsoft Access Database

Corrupt Microsoft Access MDBs FAQ

Author Comment

ID: 16809496
I cannot edit records in table.
Now this is strange I can edit other records but I cannot edit certain records.
So not all records are commited successfully...
Perhaps a JET issue?
Seems like it is happeneing on one of the tables.
Now the form I am having problems with has recordsetclone used but does not get executed
until it is clicked. So as long as I am not clicking the button the Recordsetclone does not come into picture.
More in next...

LVL 39

Expert Comment

ID: 16809803
>I can edit other records but I cannot edit certain records.

There are only two reasons I can think of that would cause this behavior.  
1) When you try to change the record, do you see a stop symbol (a circle with a line through it) at the far left side of the record?  If so, then the record is locked by another process - it could be a query, an open form, an open recordset (or recordset clone).  To help you find the problem, try downloading and installing Find and Replace http://www.rickworld.com/.  Search for the name of the table and check all the references found.  Also search for the name of any query that refers to the table.

2) Database corruption.  Did you try at least 1-7 of the fix corruption list?

Author Comment

ID: 16810337
I am unable to use JETCOMP. Comes with an error "cannot use"

Author Comment

ID: 16810348
There seems to be a known issue with BIT fields in such cases.
All bit fields must be set to a default value. Let me try this. If you find something else let me know
LVL 39

Expert Comment

ID: 16810781
I missed your mention of bit fields in your question.  Is this a SQL server back end? Access does not have bit fields.

Author Comment

ID: 16810866
yes MS SQL is the back-end  -- mentioned in my yesterdays post
LVL 39

Accepted Solution

thenelson earned 400 total points
ID: 16810941
>mentioned in my yesterdays post
You sure did - one of these days I need to learn to read.

There are others stronger than I in SQL - I will post an invite for them.

Author Comment

ID: 16811110
ok np I appreciate what you have done thus far....
I am trying in our dev dB by adding 0 default to all bit fields.
I think I have a fix. I tried it once but I am starting all over by regerating the table in Dev from prod and then reproducing the prob and then fix the fields.

Author Comment

ID: 16817380
I changed all BIT fields to default = 0.
I still get the same write conflict.
There is no one in the dB except myself.
Any thoughts?
Do I need to do the same for numeric fields?
LVL 38

Expert Comment

by:Jim P.
ID: 16817534
The problem may be that VBA uses 0 and -1 for True/False (boolean) fields.  In SQL the Bit is 0 or +1.  Try change all bit fields to SmallInt. TinyInt won't take negatives either.

Author Comment

ID: 16818243
I tried all BIT fields set to 0 as a default.
Then when it did not work I changed all INT fields to default=0.
It seems to be working now.
I am putting it into test once works I will accept one of the answers.
Thanks pls stand by
LVL 39

Expert Comment

ID: 16852095
Glad to help and thank you very much for the points with "A" grade although it looks like Jim actually gave you the answer.

Happy Computing!


Author Comment

ID: 16852325
Actually I used default values for bit fields and integer fields. I did not change the data type. In fact I may have found my own answer but you had a thorough input to this question.
Thanks to Jim for contributing but was not the solution.

Featured Post

Technology Partners: 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

571 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