Problem updating record in Access 2007 database from Excel Worksheet

I have a macro in my excel workbook that updates a specific record in the access database related to the spreadsheet.

All works fine if the access database is closed. Problems arise if the database is open and a user is editing the specific record that the excel spreadsheet relates to.` I get the following Error Message:

Error Number 2147467259: 
The database has been paced in a state by user 'ADMIN' on
'LAPTOP' that prevents it from being opened or locked.

I have set the database form's Record Locks to 'No Record Locks' but this hasn't helped.

Any advice or help is greatly appreciated.

Cheers Noel

Public Sub updateAccessRecord()
On Error GoTo ProcError
Dim subFuncName As String
subFuncName = "updateAccessRecord"

Dim conn As 开发者_开发技巧ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Dim dbName As String
Dim dbPath As String
Dim strCon As String
Dim recID As Long
Dim fieldVal As Double
Dim strSQL As String

fieldVal = Worksheets("House Claim").Cells(593, 10).Value
dbName = "claim-db.mdb"
dbPath = ThisWorkbook.Path & "\..\..\..\..\"
dbPath = dbPath & "\" & dbName

strSQL = "UPDATE tblInsClaimDet SET propSet=" & fieldVal & " WHERE ID=" & recID & ""

   Set conn = New ADODB.Connection
   With conn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & dbPath
       .Open
   End With
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandText = strSQL
   End With
   Set rst = cmd.Execute
   Set rst = Nothing
   conn.Close
   Set conn = Nothing

ExitProc:

   Exit Sub

ProcError:

   MsgBox "Error " & Err.Number & ": " & Err.Description, _
         vbCritical, "Error in procedure in " & subFuncName
   Resume ExitProc
End Sub 

---

It seems that you have not split the database into a front-end and a back-end. The problem goes away if you do. Ctrl+S is not for saving a record, that is Shift+Enter, it is for saving a database object, and so it seems it has the effect of throwing the database into design or development state.

---

On the macro side; have you tried opening your connection as read-only? Even though your Access user is not locking the record, he has a "read lock" on the record, thereby preventing an exclusive lock by excel. I'm thinking that if both users are only attempting only read access, you should be Ok; but if either one is doing read/write, then it will fail.

On your access form, you should also have:

Me.AllowAdditions = True

Me.AllowDeletions = False

Me.AllowEdits = False

---

The problem is probably not in your code. The error:

The database has been paced in a state by

Indicates that the database has been opened in an exclusive mode. You should check how you are opening the database.