Connection Problem

[Bob Ewers]

Everytime I try to establish a connection to the active database, I get the following error message.

The database has been placed in a state by user that prevents it from being opened or locked.

How or why is this happening and how can I correct the error? Sample code through the first case is included below.

Private Sub UserInput_Click()
On Error GoTo Err_UserInput_Click

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database
Dim intPMvar As Integer
Dim SQLStmt As String

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

intPMvar = Me![Frame91]

Select Case intPMvar

Case 1 'Returns xxx Total

SQLStmt = "SELECT pm, sum(bps) FROM rpm_data WHERE pm = 'xxx' GROUP BY pm"
Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open (SQLStmt)
rst.Update

 

[Van T. Dinh]

Did you open the database in "Exclusive" mode?

(*Only guessing here*) but quite often, the (JET) database
being accessed through the Access GUI is considered as a
connection and "Exclusive" can block other connections,
including connections being attempted by your VBA Code.

Try opening you database in "Shared" mode.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Everytime I try to establish a connection to the active

database, I get the following error message.

The database has been placed in a state by user that

prevents it from being opened or locked.

How or why is this happening and how can I correct the

error? Sample code through the first case is included
below.

Private Sub UserInput_Click()
On Error GoTo Err_UserInput_Click

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database
Dim intPMvar As Integer
Dim SQLStmt As String

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

intPMvar = Me![Frame91]

Select Case intPMvar

Case 1 'Returns xxx Total

SQLStmt = "SELECT pm, sum(bps) FROM rpm_data WHERE pm = 'xxx' GROUP BY pm"
Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open (SQLStmt)
rst.Update
.