Updates not working

[Guest]

We have an Access database that works with a SQL Server database. Since
installing Access 2003, the users can add new records, but not update records.

The following code is triggered by a button on a form, but generates a
message saying "ODBC -- Cannot lock all records". When I click OK it
displays the message "You can't save this record at this time."

It works OK in Access 97, but I want to make it work in Access 2003.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

'DoCmd.RunCommand acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunCommand acCmdSaveRecord
AddOfficeRecords
cmdEdit.Enabled = False
FName.SetFocus
'cmdCancel.Enabled = False
'cmdSave.Enabled = False
'Me.AllowEdits = False

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

These are the subroutines
Private Sub AddOfficeRecords()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qDelOfficeMarket_Contact"
Set rsO_M = CurrentDb.OpenRecordset("Office_Market", dbOpenDynaset,
dbOptimistic)

'This would have been a nice place for an array.. Oh well
If CheckPet Or Check131 Then AddOfficeRecord 1, ContactID
If CheckWA Or Check131 Then AddOfficeRecord 2, ContactID
If CheckLA Or Check131 Then AddOfficeRecord 3, ContactID
If CheckSac Or Check131 Then AddOfficeRecord 4, ContactID
If CheckEB Or Check131 Then AddOfficeRecord 5, ContactID
If CheckAz Or Check131 Then AddOfficeRecord 6, ContactID
If CheckCorp Or Check131 Then AddOfficeRecord 7, ContactID
If CheckV Or Check131 Then AddOfficeRecord 8, ContactID

rsO_M.Close
DoCmd.SetWarnings True

End Sub
Private Sub AddOfficeRecord(OfficeVal As Integer, MarketVal As Integer)
rsO_M.AddNew
rsO_M.Fields("marketid") = MarketVal
rsO_M.Fields("officeid") = OfficeVal
rsO_M.Update

End Sub

 

[Guest]

Are you use SQL Server as database backend, Access as frontend userinterface?
if yes:
don't use CurrentDb ---> this is DAO
in Access's ADP, use ADO

"Mikel" 來函：