0

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
Improve this question
4
  • 1
    Had you saved all code and queries before you tried this? This is the standard error generated when using ADO with unsaved structural data (forms, queries, code etc). Commented Nov 7, 2010 at 10:52
  • @Remou Yes, I have saved all the forms code etc ... I think the problem arises when a user is entering data on a form and saves the record (say using 'ctrl + S') and keeps the record open. If the user avoids doing this there doesn't seem to be a problem. Commented Nov 7, 2010 at 11:57
  • Ctrl-S does not save the record -- it saves the FORM, and that will cause exactly the problem @Remou suggested. The only exception to that would be if you've overridden the default definition of Ctrl-S with a custom action. Commented Nov 8, 2010 at 1:25
  • @David Yep that seems to be what the problem is/was. Splitting the database solves the problem Commented Nov 8, 2010 at 10:40

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I hadn't yet split the database, have just done so and the problem has gone. Nice one Remou
@glinch Just curious, why is this no longer your accepted answer? You may notice that David posted several hours after my answer.
Sorry there, mistake on my behalf, this is still my accepted answer.
0

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

Improve this answer

9 Comments

@RichO Cheers for the reply. Im looking for both to be able to read/write. There is no problem with Access doing this the problem only arises when the spreadsheet tries to write to the open record. Are you saying that it is impossible to achieve both Access and Excel writing to the same record at the same time?
I see... That's a good question; my gut tells me that you can't do this because you are relying on two separate database engines (the internal access engine, and the ADO driver) to cooperate on a record locking level. Have you considered using ole automation from access instead? At least that would keep everything running under the same database engine... And not that you want to hear this, but this sounds like a job for a database back-end such as sql server..
One last thought... Have you tried setting the ActiveConnection to nothing in addition to setting rst to nothing?
@RichO Ah right, this is sounding like it could be a lot more difficult than I envisaged. I dont quite understand your OLE automation comment. I use this when I'm writing from Access to Excel. The problem I have is communicating from Excel to Access (apologies if im getting the wrong end of the stick). With regards to the ActiveConnection 'Set conn = Nothing' sets that to nothing as far as i believe.
Er, if you're using the ADO interface, you're still using Jet/ACE to interact with the data store. It's just a different interface, but the Jet/ACE engine is doing all the work. There happen to be two instances of it working (one in Access and one in Excel), but that's just a multi-user scenario, so there is no issue there. That is, use of ADO has nothing to do with it.
|
0

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.

Improve this answer

1 Comment

I'm not so sure that this is the problem. I'm not opening the database in exclusive mode. When the database is open you can write to it through the Excel file. The problem arises when trying to write to the same record with both the database form and the excel spreadsheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.