locked
Define a file path in Excel VBA which connect to a Access .accdb database, but got error for not finding .mdb database which I never define its file path RRS feed

  • Question

  • I'm writing code in Excel VBA using ADO connection to open an Access .accdb database. I defined the file path as bellow, but when I run my code, error message tells me that "couldn't find file C:\Users\sevenice\Documents\EM Database.mdb". It's so weird that I never define that file path which in .mdb file extent.

    However, when I save accdb file as mdb file and then place the mdb file under Documents folder, the code works! But I couldn't figure it out why.

    Is there anything wrong with my codes? Or there exists some default file path in Access 2007? My code is in Excel 2007.

    Thanks, Bing

    Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset

    Dim Dbfilepath As String

    Dbfilepath = "C:\Users\sevenice\Desktop\EM Database.accdb"

    Set cnn = New ADODB.Connection

    cnn
    .Open "Provider= Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & Dbfilepath & ";" & "Persist Security Info =False;"

    Set rst = New ADODB.Recordset
    rst
    .ActiveConnection = cnn

    • Moved by Mike Feng Tuesday, October 11, 2011 2:04 PM VBA (From:Visual Basic General)
    Friday, October 7, 2011 12:36 AM

Answers

  • The filetype in Access 2007 and higher is changed into .ACCDB, Access version lower are with extension .MDB.

    Then there you also have different connectionstrings as well using different Access versions, using the JET for MDB and ACE for the ACCDB version.

     

    So if you want to use the MDB version, use the JET OLE DB connectionstring, see below thread:

    http://www.connectionstrings.com/access

     

    Hope this helps,

    Daniel


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Monday, January 9, 2012 8:45 PM
    • Marked as answer by danishani Thursday, January 12, 2012 5:53 AM
    Monday, November 28, 2011 10:55 PM

All replies

  • This forum is not for VBA, try the VBA forum instead of that for this question.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


    Success
    Cor
    Friday, October 7, 2011 8:04 AM
  • The filetype in Access 2007 and higher is changed into .ACCDB, Access version lower are with extension .MDB.

    Then there you also have different connectionstrings as well using different Access versions, using the JET for MDB and ACE for the ACCDB version.

     

    So if you want to use the MDB version, use the JET OLE DB connectionstring, see below thread:

    http://www.connectionstrings.com/access

     

    Hope this helps,

    Daniel


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Monday, January 9, 2012 8:45 PM
    • Marked as answer by danishani Thursday, January 12, 2012 5:53 AM
    Monday, November 28, 2011 10:55 PM