Msg - could not update; currently locked by another session

[Pat Hall]

I've picked up support for an Access 2002 database. Having problem with some
VBA code since I am learning VBA as I go.

When I run the code I get the message "Could not update; currently locked by
another session on this machine."
The message is happening on the .Edit statement.

There are no other sessions on this machine. The database links to Access
tables in another database.

I looked on the internet but was unable to find a suggestion that helps me.

The code is below. The part between the lines is where the problem is. The
code above and below the lines works correctly.

Private Sub Update_New_Address_Click()
On Error GoTo Err_Update_New_Address_Click

Dim db As Database
Dim sqlHouse, sqlASteps As String
Dim rsHouse, rsASteps, rsAStepsOpen As Recordset
Dim cnt As Integer


'Make sure New Address is filled in

If IsNull(CANewAddress) Then
MsgBox "You need to fill in the New Address field"
Exit Sub
End If

'Check for Duplicate address
cnt = DCount("[Address]", "tblActionSteps", "[Address]='" & CANewAddress
& "'")
If cnt > 0 Then
MsgBox " This address already exists in the database."
Exit Sub
End If



'Tested OK to here

____________________________________________________________________

Set db = CurrentDb

'Update tblHouse

Set rsHouse = db.OpenRecordset("tblHouse", dbOpenDynaset)

With rsHouse

.FindFirst "Address = '" & Me!Address & "'"

If Not .NoMatch Then 'record found

.Edit
!Address = Me!CANewAddress
.Update

End If
End With

____________________________________________________________________

'Update tblActionSteps


' Tested OK below here


Set rsAStepsOpen = db.OpenRecordset("tblActionSteps", dbOpenDynaset)

Set rsASteps = db.OpenRecordset("Select * From tblActionSteps Where
Address = '" & Me!Address & "'")


Do While Not rsASteps.EOF

rsASteps.Edit
rsASteps!Address = Me!CANewAddress
rsASteps.Update

rsASteps.MoveNext
Loop


'Cleanup
rsASteps.Close
rsAStepsOpen.Close
rsHouse.Close
Set db = Nothing

DoCmd.Close


Exit_Update_New_Address_Click:
Exit Sub

Err_Update_New_Address_Click:
MsgBox Err.Description
Resume Exit_Update_New_Address_Click

End Sub

 

[Dirk Goldgar]

I've picked up support for an Access 2002 database. Having problem with
some
VBA code since I am learning VBA as I go.

When I run the code I get the message "Could not update; currently locked
by
another session on this machine."
The message is happening on the .Edit statement.

There are no other sessions on this machine. The database links to Access
tables in another database.

I looked on the internet but was unable to find a suggestion that helps
me.

The code is below. The part between the lines is where the problem is.
The
code above and below the lines works correctly.

Private Sub Update_New_Address_Click()
On Error GoTo Err_Update_New_Address_Click

Dim db As Database
Dim sqlHouse, sqlASteps As String
Dim rsHouse, rsASteps, rsAStepsOpen As Recordset
Dim cnt As Integer


'Make sure New Address is filled in

If IsNull(CANewAddress) Then
MsgBox "You need to fill in the New Address field"
Exit Sub
End If

'Check for Duplicate address
cnt = DCount("[Address]", "tblActionSteps", "[Address]='" &
CANewAddress
& "'")
If cnt > 0 Then
MsgBox " This address already exists in the database."
Exit Sub
End If



'Tested OK to here

____________________________________________________________________

Set db = CurrentDb

'Update tblHouse

Set rsHouse = db.OpenRecordset("tblHouse", dbOpenDynaset)

With rsHouse

.FindFirst "Address = '" & Me!Address & "'"

If Not .NoMatch Then 'record found

.Edit
!Address = Me!CANewAddress
.Update

End If
End With

____________________________________________________________________

'Update tblActionSteps


' Tested OK below here


Set rsAStepsOpen = db.OpenRecordset("tblActionSteps", dbOpenDynaset)

Set rsASteps = db.OpenRecordset("Select * From tblActionSteps Where
Address = '" & Me!Address & "'")


Do While Not rsASteps.EOF

rsASteps.Edit
rsASteps!Address = Me!CANewAddress
rsASteps.Update

rsASteps.MoveNext
Loop


'Cleanup
rsASteps.Close
rsAStepsOpen.Close
rsHouse.Close
Set db = Nothing

DoCmd.Close


Exit_Update_New_Address_Click:
Exit Sub

Err_Update_New_Address_Click:
MsgBox Err.Description
Resume Exit_Update_New_Address_Click

End Sub

Is the table "tblHouse" by any chance part of the recordsource of the form?
Usually when you get that message, it's because you and Access are getting
in each other's way. If your form is currently editing (and has modified
but not saved) the record that you are trying to update via the recordset,
that would cause it.

 

[Pat Hall]

Yes. To get the key of the record to be changed using a combo box on the
form. Record Locks on the form are set to "Edited Record". The record is
not updated from the form but by the VBA code behind the "Save" button.

Do I just need to set the Record Locks to "No Locks" or will that cause
other problems.
--
Pat Hall

I've picked up support for an Access 2002 database. Having problem with
some
VBA code since I am learning VBA as I go.

When I run the code I get the message "Could not update; currently locked
by
another session on this machine."
The message is happening on the .Edit statement.

There are no other sessions on this machine. The database links to Access
tables in another database.

I looked on the internet but was unable to find a suggestion that helps
me.

The code is below. The part between the lines is where the problem is.
The
code above and below the lines works correctly.

Private Sub Update_New_Address_Click()
On Error GoTo Err_Update_New_Address_Click

Dim db As Database
Dim sqlHouse, sqlASteps As String
Dim rsHouse, rsASteps, rsAStepsOpen As Recordset
Dim cnt As Integer


'Make sure New Address is filled in

If IsNull(CANewAddress) Then
MsgBox "You need to fill in the New Address field"
Exit Sub
End If

'Check for Duplicate address
cnt = DCount("[Address]", "tblActionSteps", "[Address]='" &
CANewAddress
& "'")
If cnt > 0 Then
MsgBox " This address already exists in the database."
Exit Sub
End If



'Tested OK to here

____________________________________________________________________

Set db = CurrentDb

'Update tblHouse

Set rsHouse = db.OpenRecordset("tblHouse", dbOpenDynaset)

With rsHouse

.FindFirst "Address = '" & Me!Address & "'"

If Not .NoMatch Then 'record found

.Edit
!Address = Me!CANewAddress
.Update

End If
End With

____________________________________________________________________

'Update tblActionSteps


' Tested OK below here


Set rsAStepsOpen = db.OpenRecordset("tblActionSteps", dbOpenDynaset)

Set rsASteps = db.OpenRecordset("Select * From tblActionSteps Where
Address = '" & Me!Address & "'")


Do While Not rsASteps.EOF

rsASteps.Edit
rsASteps!Address = Me!CANewAddress
rsASteps.Update

rsASteps.MoveNext
Loop


'Cleanup
rsASteps.Close
rsAStepsOpen.Close
rsHouse.Close
Set db = Nothing

DoCmd.Close


Exit_Update_New_Address_Click:
Exit Sub

Err_Update_New_Address_Click:
MsgBox Err.Description
Resume Exit_Update_New_Address_Click

End Sub

Is the table "tblHouse" by any chance part of the recordsource of the form?
Usually when you get that message, it's because you and Access are getting
in each other's way. If your form is currently editing (and has modified
but not saved) the record that you are trying to update via the recordset,
that would cause it.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes. To get the key of the record to be changed using a combo box on the
form. Record Locks on the form are set to "Edited Record". The record
is
not updated from the form but by the VBA code behind the "Save" button.

Do I just need to set the Record Locks to "No Locks" or will that cause
other problems.

It won't work -- if two different connections are trying to change the
record, one of them is going to get rejected sooner or later.

I don't really understand exactly what you're trying to do here, and why
you're using a separate recordset to change a record that you already have
at your disposal via the form. Perhaps if you'll explain a bit, we can find
a better way to do it that won't lead to the conflict.

 

[Pat Hall]

ddress

 

[Dirk Goldgar]

ddress

I hope there's a followup to this rather laconic message. <g>

 

[Pat Hall]

Let me try again. I'm using the form to get the current address using a
combo box and tblHouse. Plus a text box which contains the new address.
The update is done by the VBA code behind the Save button on the form.

It updates the single record with the Address in tblHouse (Primary Key) and
multiple records with the same Address in tblActionSteps (part of Primary
Key).

I changed the Record Lock setting on the form to "No Locks" and tested. The
code now works.

 

[Pat Hall]

Did some more testing and discovered that the update of tblActionSteps is
working correctly. The update of tblHouse is not working quite right. It
saves the record with the new address and all the other informatiion in the
record. It also saves the original record with the original address and the
next 3 fields still populated. The remaining fields are now blank.

Not sure what is happening since the code is the same for both tables.

 

[Dirk Goldgar]

Did some more testing and discovered that the update of tblActionSteps is
working correctly. The update of tblHouse is not working quite right. It
saves the record with the new address and all the other informatiion in
the
record. It also saves the original record with the original address and
the
next 3 fields still populated. The remaining fields are now blank.

Not sure what is happening since the code is the same for both tables.

What are the tables involved (with their relevant fields and primary keys)?
What are the relationships among them?

What is the recordsource of the form? If it's a stored query, what is the
SQL of the query?

What are the controls (on the form) that are involved in this scenario, what
are their controlsources, and how are they used in this scenario?

Please forgive me for asking so many questions, but I need a good picture of
the setup before I can say what's wrong.

 

[Pat Hall]

Tables: tblHouse (Primary Key: Address) and tblActionSteps Primary Key:
Address + [Action Step]). No relationship set in Access only in code.
tblHouse has 1 entry per Address; tblActionSteps has multiple entries for
each tblHouse entry.

recordsource of form is tblHouse. One of the tables.

Controls on form are: Address (combo box from tblHouse) RowSource is query
(Select tblHouse.Address from tblHouse order by [Address]) and CANewAddress
(no control source). Address combo box is used to select the records in both
tables that need the Address field changed. CANewAddress is the new address
that is to replace the existing one.

You get to the Change Address form by clicking on a command button on the
main maintenance form for the database. You select the Address to be changed
on the Change Address form.

Hope this helps.

 

[Dirk Goldgar]

(Please note questions inline.)

Tables: tblHouse (Primary Key: Address) and tblActionSteps Primary Key:
Address + [Action Step]). No relationship set in Access only in code.
tblHouse has 1 entry per Address; tblActionSteps has multiple entries for
each tblHouse entry.

recordsource of form is tblHouse. One of the tables.

Controls on form are: Address (combo box from tblHouse) RowSource is
query
(Select tblHouse.Address from tblHouse order by [Address]) and
CANewAddress
(no control source).

Is the Address combo box also unbound?

Address combo box is used to select the records in both
tables that need the Address field changed. CANewAddress is the new
address
that is to replace the existing one.

Is CANewAddress a text box?

You get to the Change Address form by clicking on a command button on the
main maintenance form for the database. You select the Address to be
changed
on the Change Address form.

It seems to me that you may not need the Change Address form at all. If you
formally define an enforced relationship between tblHouse and tblActionSteps
(linking the Address fields in both tables), and you specify Cascade Updates
for the relationship, then if you change the Address in tblHouse on the main
maintenance form, that change will be pushed right out to tblActionSteps.

 

[Pat Hall]

Is the Address combo box also unbound? No it is not

Is CANewAddress a text box? Yes

Since the original code was written without relationships setup and I didn't
write the code, I am uncomfortable adding that at this date. I know the
original code checks for duplicates. The database has been in production
for 6+ months.

Is there some way to do this without setting up relationships?

--
Pat Hall

(Please note questions inline.)

Tables: tblHouse (Primary Key: Address) and tblActionSteps Primary Key:
Address + [Action Step]). No relationship set in Access only in code.
tblHouse has 1 entry per Address; tblActionSteps has multiple entries for
each tblHouse entry.

recordsource of form is tblHouse. One of the tables.

Controls on form are: Address (combo box from tblHouse) RowSource is
query
(Select tblHouse.Address from tblHouse order by [Address]) and
CANewAddress
(no control source).

Is the Address combo box also unbound?

Address combo box is used to select the records in both
tables that need the Address field changed. CANewAddress is the new
address
that is to replace the existing one.

Is CANewAddress a text box?

You get to the Change Address form by clicking on a command button on the
main maintenance form for the database. You select the Address to be
changed
on the Change Address form.

It seems to me that you may not need the Change Address form at all. If you
formally define an enforced relationship between tblHouse and tblActionSteps
(linking the Address fields in both tables), and you specify Cascade Updates
for the relationship, then if you change the Address in tblHouse on the main
maintenance form, that change will be pushed right out to tblActionSteps.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Is the Address combo box also unbound? No it is not

Hmm, this sounds like the root of your problem. If the Address combo is
bound to the Address field in tblHouse, then selecting an address in the
combo box will change the address of the current record. That doesn't seem
to be what you had in mind. It seems to me that, to do what you are
attempting, the Change Address form should be *unbound*, and the combo box
should also be unbound. After all, the purpose of this form isn't to edit
the tblHouse record directly, but to provide information that your code will
use to change the Address field in two tables.

Since the original code was written without relationships setup and I
didn't
write the code, I am uncomfortable adding that at this date. I know the
original code checks for duplicates. The database has been in production
for 6+ months.

Enforced relationships are how relational databases ensure data integrity.
You really need to understand them and not be afraid of them, even if you
decide you don't want to implement them in this database now.

Is there some way to do this without setting up relationships?

Yes, provided that you are willing to do all the work yourself. If I've
understood what you've been telling me, here's how I would do it with your
[Change Address] form.

1. Make sure the form is unbound -- no recordsource.

2. Therefore, all the controls on the form are unbound. The combo box
[Address] still has the same rowsource as before, allowing you to select the
address that you want to change.

3. Rewrite your code for the command button as follows:

'------ start of code ------
Private Sub Update_New_Address_Click()

On Error GoTo Err_Update_New_Address_Click

Dim strOldAddress As String
Dim strNewAddress As String
Dim strErrors As String

Const Q As String = """"
Const QQ As String = Q & Q

' Make sure the old address has been selected.
' If it has, make a properly quoted version of it for use
' in SQL statements.

If IsNull(Me!Address) Then
strErrors = strErrors & vbCr & _
"+ You need to select the Address to be changed."
Else
strOldAddress = Q & Replace(Me!Address, Q, QQ) & Q
End If

' Make sure the new address is filled in.
' If it has, make a properly quoted version of it for use
' in SQL statements, and check to see if it's a duplicate.

If IsNull(CANewAddress) Then

strErrors = strErrors & vbCr & _
"+ You need to fill in the New Address field."
Else
strNewAddress = Q & Replace(Me!CANewAddress, Q, QQ) & Q
'Check for Duplicate address
If Not IsNull(DLookup("Address", "tbnlActionSteps", _
"Address=" & strNewAddress)) _
Then

strErrors = strErrors & vbCr & _
"+ This address already exists in the database."
End If
End If

' If any errors were found, display them and exit.
If Len(strErrors) > 0 Then
MsgBox _
"Unable to change address:" & vbCr & strErrors, _
vbExclamation, _
"Errors Detected"
Exit Sub
End If

' Here, no errors were found, so make the changes.

With CurrentDb

'Update tblHouse
.Execute _
"UPDATE tblHouse SET Address = " & strNewAddress & _
" WHERE Address = " & strOldAddress, _
dbFailOnError

'Update tblActionSteps

.Execute _
"UPDATE tblActionSteps SET Address = " & strNewAddress & _
" WHERE Address = " & strOldAddress, _
dbFailOnError

End With

' Close this form.

DoCmd.Close acForm, Me.Name, acSaveNo


Exit_Update_New_Address_Click:
Exit Sub

Err_Update_New_Address_Click:
MsgBox Err.Description
Resume Exit_Update_New_Address_Click

End Sub
'------ end of code ------

I've used update queries instead of recordsets; that should be a teeny bit
more efficient for an operation like this.

I'm concerned about one thing. The check for duplicates looks in
tblActionSteps, not in tblHouse to see if the address is already in the
system. I suspect that it should be checking tblHouse, in case there's a
house with this address that has no action steps. I didn't change that in
my suggested code -- you can fix it, if you agree.

 

[Pat Hall]

Thanks Dirk. Changing the form and combo box to "Unbound" solved my problem.

I check tblActionSteps for duplicates because that was what I copied from
other code in the database. The database is setup to automatically add the
basic info in the tblsActionSteps for each Address when a new address is
added so a tblHouse with no tblActionSteps shouldn't happen

I am going to look at your suggested code changes and may put them in. I
like the Update statements rather than what I had copied. I understand them
better than VBA code.
--
Pat Hall

Is the Address combo box also unbound? No it is not

Hmm, this sounds like the root of your problem. If the Address combo is
bound to the Address field in tblHouse, then selecting an address in the
combo box will change the address of the current record. That doesn't seem
to be what you had in mind. It seems to me that, to do what you are
attempting, the Change Address form should be *unbound*, and the combo box
should also be unbound. After all, the purpose of this form isn't to edit
the tblHouse record directly, but to provide information that your code will
use to change the Address field in two tables.

Since the original code was written without relationships setup and I
didn't
write the code, I am uncomfortable adding that at this date. I know the
original code checks for duplicates. The database has been in production
for 6+ months.

Enforced relationships are how relational databases ensure data integrity.
You really need to understand them and not be afraid of them, even if you
decide you don't want to implement them in this database now.

Is there some way to do this without setting up relationships?

Yes, provided that you are willing to do all the work yourself. If I've
understood what you've been telling me, here's how I would do it with your
[Change Address] form.

1. Make sure the form is unbound -- no recordsource.

2. Therefore, all the controls on the form are unbound. The combo box
[Address] still has the same rowsource as before, allowing you to select the
address that you want to change.

3. Rewrite your code for the command button as follows:

'------ start of code ------
Private Sub Update_New_Address_Click()

On Error GoTo Err_Update_New_Address_Click

Dim strOldAddress As String
Dim strNewAddress As String
Dim strErrors As String

Const Q As String = """"
Const QQ As String = Q & Q

' Make sure the old address has been selected.
' If it has, make a properly quoted version of it for use
' in SQL statements.

If IsNull(Me!Address) Then
strErrors = strErrors & vbCr & _
"+ You need to select the Address to be changed."
Else
strOldAddress = Q & Replace(Me!Address, Q, QQ) & Q
End If

' Make sure the new address is filled in.
' If it has, make a properly quoted version of it for use
' in SQL statements, and check to see if it's a duplicate.

If IsNull(CANewAddress) Then

strErrors = strErrors & vbCr & _
"+ You need to fill in the New Address field."
Else
strNewAddress = Q & Replace(Me!CANewAddress, Q, QQ) & Q
'Check for Duplicate address
If Not IsNull(DLookup("Address", "tbnlActionSteps", _
"Address=" & strNewAddress)) _
Then

strErrors = strErrors & vbCr & _
"+ This address already exists in the database."
End If
End If

' If any errors were found, display them and exit.
If Len(strErrors) > 0 Then
MsgBox _
"Unable to change address:" & vbCr & strErrors, _
vbExclamation, _
"Errors Detected"
Exit Sub
End If

' Here, no errors were found, so make the changes.

With CurrentDb

'Update tblHouse
.Execute _
"UPDATE tblHouse SET Address = " & strNewAddress & _
" WHERE Address = " & strOldAddress, _
dbFailOnError

'Update tblActionSteps

.Execute _
"UPDATE tblActionSteps SET Address = " & strNewAddress & _
" WHERE Address = " & strOldAddress, _
dbFailOnError

End With

' Close this form.

DoCmd.Close acForm, Me.Name, acSaveNo


Exit_Update_New_Address_Click:
Exit Sub

Err_Update_New_Address_Click:
MsgBox Err.Description
Resume Exit_Update_New_Address_Click

End Sub
'------ end of code ------

I've used update queries instead of recordsets; that should be a teeny bit
more efficient for an operation like this.

I'm concerned about one thing. The check for duplicates looks in
tblActionSteps, not in tblHouse to see if the address is already in the
system. I suspect that it should be checking tblHouse, in case there's a
house with this address that has no action steps. I didn't change that in
my suggested code -- you can fix it, if you agree.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)