OAC Technology
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("C lient 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
Set ClientRS = CurrentDb.OpenRecordset("C
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
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?
ASKER
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.
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.
ASKER
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
Handle concurrent update conflicts in Access silently
/gustav
ASKER
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
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
ASKER
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
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
.Close