Results 1 to 8 of 8

Thread: Connect to Access 2007 from Excel 2007 VBA

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    6

    Connect to Access 2007 from Excel 2007 VBA

    Hello
    I am trying to set up VBA code in excel to query an access database and return the results in a recordset. I have the following code but it is giving me and error on the rs.Open line.

    Code:
    Dim sSQL As String
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\MyFolder\myDB.accdb;Persist Security Info=False"
    
    sSQL = "Select top 2 from Table1"
    
    Set rs = New ADODB.Recordset
    rs.Open sSQL, cn
    
    ActiveWorkbook.Sheets("Control").Cells(6, 1).CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    I get the following error on the rs.Open line:
    Code:
    Run-time error '-2147217900 (80040e14)':  Automation error

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Connect to Access 2007 from Excel 2007 VBA

    If you do Select * does it work?

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    6

    Re: Connect to Access 2007 from Excel 2007 VBA

    Yes it does work if I change it to "Select *". However, I am actually trying to do a much more complex query...I just put something simple in the example.
    If I do "SELECT * from Positions" it works but if I try to only select certain fields or if I add a WHERE clause to the query, I get the automation error again.

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    6

    Re: Connect to Access 2007 from Excel 2007 VBA

    Sorry...It is working when I just add specific columns in the select and when I add a simple Where clause. The line in my where clause that is causing the problem is the following:
    Code:
    AND Nz(num1,0) < 30
    This works in access when I run the query manually, but there must be some problem when I run through the ADODB connection from excel. Is it because the Nz function is not recognized in ADO?

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    6

    Re: Connect to Access 2007 from Excel 2007 VBA

    Thanks for your help. I've got it working now. I replaced the problem line with "IIf(IsNull(num1),0,num1)" and now it works.

    Not sure why the Nz function works in regular access query, and doesn't work when running the query from ADODB connection, but this is fine.

    Thanks

  6. #6
    New Member
    Join Date
    Dec 2009
    Posts
    2

    Re: Connect to Access 2007 from Excel 2007 VBA

    Hi,

    Please let me know how to connect the password protected MS Access 2007 file.
    the above coding works fine for the unprotected Access file.
    we need to enter password some where in the connection.

    Please help me out asap. Its urgent

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    6

    Re: Connect to Access 2007 from Excel 2007 VBA

    Sorry, not sure how to work with db that is password protected. All of mine are open.

    But this is how I usually build connection strings if not sure of the format:
    Create a new txt file
    Then change the file extenstion to ".udl"
    Then double click to open the udl file
    This should open a wizard where you can build a connection and test it
    Once the test is successful just hit OK to close the file
    Then go back and open it with notepad and the connection string will be in the notepad file.

    Not a direct solution, but hope it helps

  8. #8
    New Member
    Join Date
    Dec 2009
    Posts
    2

    Re: Connect to Access 2007 from Excel 2007 VBA

    Thanks for the reply friend.
    have got the answer to connect protected Access 2007 file.
    PFB for your ref.

    HTML Code:
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\MyFolder\myDB.accdb;Persist Security Info=False;Jet OLEDB:Database Password=123"
    Last edited by Sals; Dec 31st, 2009 at 06:10 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width