ADO, VBA, and ms access 2003

[jkainth]

Hey guys
I have some problem. i am using vba in excel to get some data from ms access 2003. I Have a stored query in the access database that i need to run and get the recordset using ADO.
The query that i need to execute is a select query that uses other smaller queries to generate the final dataset. I am trying to execute the query with the following code below:

Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
stDB = "C:\TTReports.mdb"
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & stDB & ";"
Set cnt = New ADODB.Connection
cnt.Open stConn
Set rst1 = cnt.Execute("UTIFrench")

the name of the query is 'UTIFrench' and like i said is a query that uses other queries(not tables) to generate the dataset. When i use the above code i get a enpty recordset i.e. EOF but it though picks up the columns (when i do msgbox rst1.feilds.count it returns 10..which is correct)
But the query when run in access gives me the correct dataset (26 rows and 10 columns)

Please help me out. I would coudl just use the open recordset but the query is soo complicated and so writing the query with subqueries would be a pain in the ass.

Thanks alot....

 

[Cynoclast]

<connection>.Execute

is only for action queries. Such as Insert, update, delete. Not select.

you need:

rst1.Open "qrySortTimes", cnt

and you may need to specify some of the additional optional arguments to the .Open method.

 

[RoyVidar]

Set rst1 = cnt.Execute("UTIFrench")

is valid way of obtaining a readonly forwardonly recordset based on select queries on a connection object, but assumes that ADO knows what "UTIFrench" is, which I don't think the connection does. It could perhaps work within Access, but from Excel, I think something like this, utilizing the command object in stead, to retrieve/resolve the query:

Dim cnt As ADODB.Connection
dim cmd as adodb.command
Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
stDB = "C:\TTReports.mdb"
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & stDB & ";"
Set cnt = New ADODB.Connection
cnt.Open stConn
set cmd=new adodb.command
with cmd
.activeconnection=cnt
.properties("Jet OLEDB:Stored Query")=true
.commandtext="UTIFrench"
Set rst1 = .Execute
end with

 

[RoyVidar]

Correction, after testing, on my setup, your code works, i e listing all resulting rows from a query. I was so sure you'd need the command object for that.

Next questions would be, are there any parameters in the underlying queries? Could it be the the subqueries are too complex for ADO? How do you determine that there are no records, are you looping them? If you're doing a .Recordcount, I think you'll get -1 anyway, but it isn't reliable.