Connection Problem

B

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
 
V

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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top