MS Access 2002 conversion question

Posted on 2006-05-16
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
    LVL 6

    Author Comment

    The error they get is a WRITE CONFLICT error.
    LVL 39

    Expert Comment

    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
    LVL 6

    Author Comment

    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.
    LVL 39

    Expert Comment

    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;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

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

    How to Troubleshoot Corruption in a Microsoft Access Database;en-us;306204

    Corrupt Microsoft Access MDBs FAQ
    LVL 6

    Author Comment

    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

    >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  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?
    LVL 6

    Author Comment

    I am unable to use JETCOMP. Comes with an error "cannot use"
    LVL 6

    Author Comment

    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

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

    Author Comment

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

    Accepted Solution

    >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.
    LVL 6

    Author Comment

    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.
    LVL 6

    Author Comment

    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.
    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.
    LVL 6

    Author Comment

    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

    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!

    LVL 6

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now