Link to home
Start Free TrialLog in
Avatar of OAC Technology
OAC TechnologyFlag for United States of America

asked on

Microsoft Access recordset being locked.

After this code runs and adds a record to the recordset, I am unable to add any more entries into the record. I believe the record is getting locked by the database since it detected changes, but I'm not sure how to remove that through access. I have verified this code works properly besides the unintended side effect of being unable to repeatedly do it. Does anyone know how to unlock the record so everyone can access it after this runs?

    Set ClientRS = CurrentDb.OpenRecordset("Client Contacts", dbOpenDynaset, dbSeeChanges)
   
    With ClientRS
        .AddNew
        ![id] = ClientID
        ![Full Name] = FullName
        ![Phone Number] = PhoneNumber.Value
        ![Company] = "Other"
        .Update
        .Close
    End With

    Set ClientRS = Nothing
   
    DoCmd.Close acForm, "Add Phone Record", acSavePrompt
End Sub
Avatar of Big Monty
Big Monty
Flag of United States of America image

i think you want to close it after you add all of the data, so try commenting out the line:

.Close
Avatar of OAC Technology

ASKER

I've tried removing the .Close and it doesn't work. It's positioned after the data is modified anyways, isn't that where it should be?
To give another insight, running this command more than once, even with different information, yields a debug error where Access can't Update the database. (Gives error on the ".Update" step if run more than once)
What is the error?
Where is ClientID coming from?
When you reference form fields, use the Me. prefix to disambiguate the names.  The advantage is that it gives you intellisense and it saves Access some work.
Are you using an unbound form for a reason?  Access is a RAD tool and one of the best time savers is bound forms.
User generated imageCopy of the error message.

User generated imageCopy of the debug line from the error message.

This only happens after it works the first time. I believe that since it's making changes to the database, the database is locking it out and not unlocking when finished. I need to know how to do that.
OK.  That's an answer to one out of the three questions.  WHERE is ClientID coming from?  It sounds like the PK.  Are you sure it is unique?
It is not very clear what you are doing and where, but you may benefit from studying this:

Handle concurrent update conflicts in Access silently

/gustav
It's not unique, ClientID is a number to represent the company that is previously defined in the sub. I only included the section of the code that is having the issue. This code is adding a new record to a table which is saved using an SQL database. I have verified it is correctly adding the table entry, as it is visible in access even after restarting it. The issue however, is that it won't let me continuously run this script for multiple phone numbers I want to record.
This will only happen if you from two (or more) places try to update the same recordset without refreshing or at least rereading the data.
Still, what you do and how is hidden for us.

See my demo in the above link how locking will occur and how - when updating from code - it can be resolved.

/gustav
This is all that happens in the code segment. There is only one person using this at the moment, so it's not possible for two or more places to update the same recordset at the same time. It's not releasing the update after it makes it the first time, even though i tell it to .close.
You are also closing a form.

/gustav
ASKER CERTIFIED SOLUTION
Avatar of OAC Technology
OAC Technology
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial