Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: Database prevented from being opened or locked!!!

    Hi folks,

    The following simple ADO code does not work in Access 2000 VBA.

    Please help!!

    Code:
    Public Sub test2(strDBpath As String)
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    With cn
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        cn.Open strDBpath
    
    End With
    
    cn.Close
    Set cn = Nothing
    
    End Sub
    On giving test2 "./FP12.mdb" in Immediate window, I get the error:

    The database has been placed in a state by user 'Admin' on machine 'xxx' that prevents it from being opened or locked

    Would appreciate a solution....

    Rgds

  2. #2
    Join Date
    Mar 2004
    Posts
    118
    i may be totaly off, but maybe is you declare strDBpath seperatly?

    or if your using that string to store the path declare it as a global in a new module?

  3. #3
    Join Date
    Feb 2004
    Posts
    47

    Unhappy ADO works in Excel VBA, but not in Access VBA.

    I tried hardcoding the path and the connectionstring to a simple db1.mdb as follows:
    Code:
    Public Function ADOTest2()
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    With cn
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        cn.Open ".\db1.mdb"
    
    rs.Open "table1", cn
    rs.Find "field1='c'", adSearchForward
    
    'rs!field2 = 10
    
    Debug.Print ADOTest2
    
    End With
    cn.Close
    Set cn = Nothing
    
    End Function
    This code works fine in Excel VBA, even with Access mdb open.

    However in Access VBA it gives the error!!!:
    The database has been placed in a state by user 'Admin' on machine 'xxx' that prevents it from being opened or locked

    I am going crazy... can someone help!!!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's some code I use when I've a specific DB to open (using ADO) ...:

    Code:
    Function EstablishSrcDBConnection() As Integer
        On Error GoTo Err_ESDBC
        
        Set SrcConnect = New ADODB.Connection
        Set SrcRecSet = New ADODB.Recordset
        
    '"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
        
        SrcConnect.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & ImportFromTxt & ";Uid=Admin;Pwd=;"
        
        EstablishSrcDBConnection = 1
    Exit_ESDBC:
        Exit Function
        
    Err_ESDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishSrcDBConnection = 0
        Resume Exit_ESDBC
    End Function
    ... And ImportTxt is a textbox containing a fully qualified path and database name ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Posts
    47
    Tried it...

    I get the following error...

    -2147467259:[Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked

    Any suggestions???

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Kashizzz
    Tried it...

    I get the following error...

    -2147467259:[Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked

    Any suggestions???
    What DB is this? The one you're trying to run in? Another? Is it on a network?

    I've had this happen to me occasionally (but only when I'm developing) and what I've had to is just close out of Access completely and then go back in ... Is this DB multi-user?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Posts
    47
    The db is an access mdb file. Standalone (no on a network). Single user. The code is in the same mdb file.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Kashizzz
    The db is an access mdb file. Standalone (no on a network). Single user. The code is in the same mdb file.
    Are you trying to run this in debug mode? Also is the DB setup for shared access? Did you open it exclusively?
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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