locked
Excel 2010 Connecting to Access 2007 database - 2147467259 (80004050) - File path not found RRS feed

  • Question

  • Hello,

     

    I went into Excel 2010 and set up the Miscrosoft ActiveX DataObjects 2.1 Library and the ADO 3.6 Library.

    I have the following code:

     

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim cn As ADODB.Connection  'this is the connection object

    Dim rst As ADODB.Recordset   'this is the recordset object

     

    Set rst = New ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\hd.mdb"

     

    I get an error indicating that the .mdb file is not found.

     

    I tried:

    'Call MsgBox(ThisWorkbook.Path & "\hd.mdb")

    to make sure the path is working correctly. And it seems to be

     

    Then I tried:

     

    On Error GoTo automation

     

    'FocusDone:

    'Exit Function

    '

    automation:

    'If Err.Number = -2147467259 Then

    'Resume

    'End If

    What happened was that the application froze. 
    So what do I need to do? Or what am I not doing to be able to setup a connection with an Access database?

    Monday, September 12, 2011 3:19 PM

All replies

  • For 2007/2010 try changing

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\hd.mdb"
    to
    cn.ConnectionString = "Provider=Microsoft.Jet.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\hd.mdb;"

    maybe also the trailing ;

    I didn't test that so try recording a macro

    Peter Thornton

    Monday, September 12, 2011 7:16 PM