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

---

If you do Select * does it work?

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.

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?

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

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

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

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"

---