Runtime error: The database has been placed in a state by user ...

[firefly2k8]

.. that prevents it from being opened or locked.

There seem to be more comprehensive posts on this error, but they focus on multiple users accessing the database. I am developing a new database. I have the code below, which works once. But run it a second time and I get this error and have to re-open access to clear it. Obviously this is a real pain when trying to develop something. Am i doing something wrong, how can i run this procedure again and again without closing access?

Sub test3()
  Dim strSQL As String
  Dim rst1 As ADODB.Recordset
  Dim fld1 As Field
  Dim int1 As Integer
   
  strSQL = "SELECT DISTINCT [String1] & [Date1] AS Expr1 " & _
            "FROM [Project Table 1] " & _
            "WHERE [String1] is not null;"
 
  Set rst1 = New ADODB.Recordset
  rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FILEPATH;"
  rst1.Open strSQL
  
  With rst1
  Do While Not .EOF
     Debug.Print .Fields(0)
     .MoveNext
  Loop
 End With
  rst1.Close
  Set rst1 = Nothing
  
End Sub

 

[Father Branigan]

Did you ever find a solution for this? I am experiencing the same thing.

 

[boblarson]

If you use something like this for the connection:

rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FILEPATH;"


and you are really just wanting to use it on objects within the same database file that your code is in, you must use

CurrentProject.Connection

as the connection instead of a connection string. If you use the connection string method then Access assumes that is coming from outside of the database and hence gives you that error.

 

[Father Branigan]

Thanks a lot. That worked for me.

 

[boblarson]

If you use the connection string method then Access assumes that is coming from outside of the database and hence gives you that error.

Well, technically that isn't correct - what is happening is that it opens a connection which is not part of the internal connection and therefore its state gets set appropriately which causes the error.

 

[boblarson]

Did you ever find a solution for this? I am experiencing the same thing.

Glad that it helped.

 

[firefly2k8]

Hi. I think I abondoned this method altogether and went with a dao connection. Sounds like u got your answer though. Martin