ADODB Connection Object Error?

[VBUser77]

I am using ADODB Connection Object in my code. But after successfully running my code shown below I get this runtime error message:

"The database has been placed in a state by user 'Admin' on machine 'JH' that prevent it from being opened or locked.

When I close the database and open it again ,,, the code runs fine for few times and then after that it starts giving me the same error message. This thing is driving me crazy. Any ideas/thoughts?

Thanks in advance.

jay

Dim cnConnection As ADODB.Connection
Set cnConnection = New ADODB.Connection

Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\AutoUpdate.mdb;"

cnConnection.Open strConnection

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
rst.LockType = adLockBatchOptimistic
rst.Open "SELECT * FROM TestTable", cnConnection

Set cnConnection = Nothing


rst.MoveFirst

With rst

Do While Not rst.EOF
.Fields(1) = 100
.Update
.MoveNext
Loop
End With


Set cnConnection = New ADODB.Connection
cnConnection.Open strConnection

rst.UpdateBatch

End Function

 

[lotharious]

Well, I dont ever see you closing the connection.. However Im not sure how VBA deals with that. But as a habit and good programming you should do a cnConnection.Close when you are done with that connection instead of setting it to nothing, then reseting it and reopinging it.

 

[VBslammer]

My first example didn't close the connection, as Loth pointed out, here's a more proper way to do it:

Sub RstDisconnected()
  Dim cnn As New ADODB.Connection
  Dim rst As New ADODB.Recordset
  
  [green]'create a connection[/green]
  cnn.ConnectionString = CurrentProject.BaseConnectionString
  cnn.Open
  
  [green]'open a client-side recordset using our connection[/green]
  rst.CursorLocation = adUseClient
  rst.LockType = adLockBatchOptimistic
  rst.Open "SELECT * FROM Customers", cnn
  
  [green]'kill the recordset's connection[/green]
  Set rst.ActiveConnection = Nothing
  
  [green]'close the connection![/green]
  cnn.Close
  
  [green]'muck with the records[/green]
  With rst
    While Not .EOF
      .Fields(1) = .Fields(1) & "#"
      .Update
      .MoveNext
    Wend
  End With

  [green]'show the local changes (uncommitted)[/green]
  With rst
    .MoveFirst
    While Not .EOF
      Debug.Print .Fields(1)
      .MoveNext
    Wend
  End With

  [green]'re-open and re-establish the connection[/green]
  cnn.Open
  rst.ActiveConnection = cnn
  
  [green]'perform the update[/green]
  rst.UpdateBatch
  
  [green]'close and destroy[/green]
  rst.Close
  cnn.Close
  
  Set rst = Nothing
  Set cnn = Nothing
  
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBUser77]

Thanks a lot Folks!