Get results of MS Access Select Query in ADO recordset

[wpilgri]

I have a query setup in Access that selects certain records. I need to run the query and get the records it returns in an ADO recordset so I can do further processing in code with the results of the query. I have tried:

rst.Open "qryQueryNameInAccess", CurrentProject.Connection, adOpenStatic, , adCmdStoredProc

and variations of this but I always get an error. I can open tables or pass SQL strings to the .open command successfully so I don't know why I cannot run a SELECT query successfully. Any ideas?

Thank You

 

[RoyVidar]

Neither do we, unless you could provide some more info, for instance:
* errormessage
* the query

If the query is parameterized, you'll need to resolve those, if it is complex, ADO might not be able to open a recordset based on it. For parameterized queries, you might have a look here thread709-1019057, or for a2k, should it be parameters from forms thread709-819033.

Roy-Vidar

 

[wpilgri]

This is not a parameter query...just a simple select query. I've tried several variations of:

rst.Open "qryRelatedPartNumbersForCurrentMainDesign",
CurrentProject.Connection, adOpenStatic, adLockOptimistic, adcmdTable

I've varied adcmd*, adlock*, adopen* and I always get an Error -2147217900: Invalid SQL statement; expected 'DELETE', 'INSERT'....etc

When I run the query named qryRelatedPartNumbersForCurrentMainDesign by itself it works fine. qryRelatedPartNumbersForCurrentMainDesign is the top level query but it refers to qryWindchillDataForCurrentMainRecord for its criteria

qryWindchillDataForCurrentMainRecord SQL is:

SELECT tblMainWindchillData_Fixed.number AS Expr1, tblMainWindchillData_Fixed.name AS Expr2, tblMainWindchillData_Fixed.NOUN AS Expr3, tblMainWindchillData_Fixed.[TUBING SIZE NOMINAL] AS Expr4, tblMainWindchillData_Fixed.[LOBE CONFIGURATION] AS Expr5, tblMainWindchillData_Fixed.STAGES AS Expr6, tblMainWindchillData_Fixed.[MODEL SIZE] AS Expr7, tblMainWindchillData_Fixed.TYPE1 AS Expr8, tblMainWindchillData_Fixed.[EXTERNAL COATING] AS Expr9
FROM tblMainWindchillData_Fixed
WHERE ((([tblMainWindchillData_Fixed].[number]) Like "*" & [forms]![frmmaindesign]![partnum]));



qryRelatedPartNumbersForCurrentMainDesign SQL is:

SELECT tblMainWindchillData_Fixed.number AS Expr1, tblMainWindchillData_Fixed.NOUN AS Expr2, tblMainWindchillData_Fixed.[TUBING SIZE NOMINAL] AS Expr3, tblMainWindchillData_Fixed.[LOBE CONFIGURATION] AS Expr4, tblMainWindchillData_Fixed.STAGES AS Expr5, tblMainWindchillData_Fixed.[MODEL SIZE] AS Expr6, tblMainWindchillData_Fixed.TYPE1 AS Expr7, tblMainWindchillData_Fixed.[EXTERNAL COATING] AS Expr8
FROM tblMainWindchillData_Fixed, qryWindchillDataForCurrentMainRecord
WHERE ((([tblMainWindchillData_Fixed].[NOUN])=[qryWindchillDataForCurrentMainRecord].[noun]) AND (([tblMainWindchillData_Fixed].[TUBING SIZE NOMINAL])=[qryWindchillDataForCurrentMainRecord].[tubing size nominal]) AND (([tblMainWindchillData_Fixed].[LOBE CONFIGURATION])=[qryWindchillDataForCurrentMainRecord].[lobe configuration]) AND (([tblMainWindchillData_Fixed].[STAGES])=[qryWindchillDataForCurrentMainRecord].[stages]) AND (([tblMainWindchillData_Fixed].[MODEL SIZE])=[qryWindchillDataForCurrentMainRecord].[model size]) AND (([tblMainWindchillData_Fixed].[TYPE1])=[qryWindchillDataForCurrentMainRecord].[type1]) AND (([tblMainWindchillData_Fixed].[EXTERNAL COATING])=[qryWindchillDataForCurrentMainRecord].[external coating]));

 

[wpilgri]

Now I have tried the following code, taken from "Access 2000 Developer's Handbook":

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockReadOnly
strQryName= "qryRelatedPartNumbersForCurrentMainDesign"
rs.Open strQryName, Options:=adCmdTableDirect

The error I receive now is:
Error -2147217904: Too Few Parameters. Expected 1.

This is taken right out of the Developer's Handbook so I'm confused why it won't work. I have tried changing the reference from ADO 2.8 to 2.5, 2.1 and none of them work. The database also has a reference to DAO, but since I create the recordset using adodb.recordset there should be no ambiguity. I've tried on both Access 2000 and Access 2003.

Any ideas ???

 

[cmmrfrds]

strQryName= "qryRelatedPartNumbersForCurrentMainDesign"
rs.Open strQryName, Options:=adCmdTableDirect

An Access query is not one of the options. In the option your are using above it is expecting a table name. You could pull the SQL from the query and run the SQL. One way is with the DAO QueryDef object. Another is using the ADOX Catalog. Example below.

''- Microsoft ADO Ext. 2.1 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
'iterate view collection (these are queries) if you want.
For Each v In cg.Views
Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next
'- set to specific view name
Set vn = cg.Views("query1")

rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

'Referencing vn.Name returns the SQL of the query.

 

[RoyVidar]

The expected action query error is probably because of the option, adcmdstoredproc is the option to use when opening stored queries.

The next errormessage, expected parameter - very often it is caused by a typo in the field names in the where clause, but here I think it relates to the fact that the subquery is parameterized - fetching a parameter from a form, which is not resolved through just opening a recordset based on the query. You will need to resolve the parameter, I think using the command object would be needed.

The second link I provided, gives some methods of resolving such parameters dynamicly. The first suggestion within the thread works on all versions (2000 - 2003) and is picked from the same reference (page 276 in my edition "Create a Recordset Based on a Command object"), the later suggestion in that thread (near the bottom) needs 2002+ versions.

Also I've found ADO a bit reluctant on complex queries, when stored Access queries contains stored Access sub queries (but it might just be my competence, making me having problems getting them to run;-)). I'm not sure, but I think I'd try moving the parameter to the main query, if possible, and see if that works. Else, I'd see if it was possible to to rewrite the query to not use a stored subquery, and see if that works.

Curiously enough, the adcmdtabledirect option works on a lot of things that I've not found documentation on, and often provides faster access than the "correct" option, but I think, to open stored queries as recordset, you'll need the adcmdstoredproc option.

Roy-Vidar

 

[wpilgri]

Thank you for all your help. A lot of good options I am going to try. I guess my Acess 2000 Developer's Handbook is incorrect. It clearly listed that adcmdTable or adcmdTableDirect are both acceptable for queries as well as tables. It doesn't seem to work, but that's what it says.

Thanks Again for all the help. I will report back.