Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Unanswered: Database has been placed in a state...

    I've seen several posts on this, but none seem to be much help on this situation.

    I use a JET DB back end. My front end works fine most of the time however I often access the backend through SQL statements using ADODB connections and recordsets.

    When I'm developing I will often get the "Database has been placed in astate..." message. This is not a multi-user issue (when I'm developing I am the only user). It is not an LDB error. Usually the error will occur when I am trying to open a connection to the database. It goes away when I close and reopen the front end.

    Is this because I'm using ADODB? Should I be using DAO and the currentdb object instead? Or is there another reason completely?

    It's getting to the point where development is a real struggle because I'm closing the database after every change. Any help would be appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's probably because while developing, we are often running code that is not quite right (pre-debugging) and you may have code that, for example, opens a file and fails to close it,... often errors come out of code while developing and needing to shut it down and re-open it is a BLESSING!! Why? Because it is a very good time to take another backup and compact and repair.

    How often does it happen?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    ..... it is a BLESSING!! Why? Because it is a very good time to take another backup and compact and repair.
    and get a brew on, or go spend a bit of time away from staring at the something screen....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2008
    Posts
    6

    The answer is...

    Using ADODB and creating and opening a connection every time I need to access the database is definitely causing the problem. By reverting all adodb references to DAO and using currentDB I was able to eliminate the error.

    thanks.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    That doesn't sound right. Simply switching from ADO to DAO should not be the solution. I think there's something about the way you wrote the code.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2008
    Posts
    6

    Thtat's what I thought...

    But I don't know any other way to code it than how i did. Basically the steps were:

    1. Define the adodb connection and recordset
    2. open the connection
    3. fill the recordset
    4. manipulate the data
    5. set the recordset to nothing
    6. close the connection
    7. set the connection to nothing


    The error always came when executing the query. When I deleted all lines referring to the connection and defined the recordset as DAO and used currentdb, the problem completely disappeared. And it wasn't just happening every once in a while. every 20 minutes or so I'd have to close the database and reopen it. So I don't know what else it could be. But it's not a problem any more.

    thanks

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's some typical ADO code I use to open a recordset. You shouldn't need to dim a connection unless you're using a DSN-Less connection. I usually use SQL Server linked tables as I've never really had any issues using linked tables. Later, I may come back and redo it to a stored procedure and "then" design a connection to use the SQL Server driver.

    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from dbo_tblSysUser where UserID = '" & LID & "'"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

    If rs.EOF And rs.BOF Then
    msgbox "Your login name was not found in the admin table. You don't have admin rights."
    isAdmin = False
    Else
    If rs!Admin = True Then
    isAdmin = True
    Else
    isAdmin = False
    End If
    End If

    rs.Close
    Set rs = Nothing

    Again, I'm not sure what the difference would be by using DAO versus ADO and why this would/would not cause locking of any records. I've never had any issues like you've described just using ADO. The only thing that would make sense is that if somehow your closing of the connection wasn't firing.
    Last edited by pkstormy; 12-15-08 at 17:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2008
    Posts
    6

    That makes sense

    Basically my code looks just lke yours, except I dim the connection at the beginning and close it at the end. I suspect that's what the problem was. I didn't realize there was a currentproject.connection and that probably would have solved it as well.

    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •