-
Apr 17th, 2009, 08:36 AM
#1
Thread Starter
New Member
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
-
Apr 17th, 2009, 09:39 AM
#2
Re: Connect to Access 2007 from Excel 2007 VBA
If you do Select * does it work?
-
Apr 17th, 2009, 09:47 AM
#3
Thread Starter
New Member
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.
-
Apr 17th, 2009, 10:06 AM
#4
Thread Starter
New Member
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?
-
Apr 17th, 2009, 10:16 AM
#5
Thread Starter
New Member
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
-
Dec 30th, 2009, 01:56 PM
#6
New Member
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
-
Dec 30th, 2009, 03:35 PM
#7
Thread Starter
New Member
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
-
Dec 31st, 2009, 06:00 AM
#8
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|