Access 2013: working query cannot be read through DAO recordset

Hi

I'm experiencing a problem that I have never seen before:
- I have an Access query on a join of 3 tables in SQL Server. When I run that query manually, it works perfectly
- I need to use that query in a VBA routine, so I set a DAO recordset using  the usual

Set rs = CurrentDb.OpenRecordset("SELECT * FROM queryName", dbOpenDynaset)

- the latter gives me either an error "Too few parameters. Expected 1.", or no error, but the recordset is set to nothing !

I mean, I have done this in 1000 other cases without the smallest trouble. What can be the reason for such an error ??

The strange thing is that, in the Access app, I need to access some tables over ADODB, so I have a reference to the ADO library. When I looked at my references, I noticed the DAO ref was missing, so I tried to add it. This, however, gives an error "Name conflicts with existing module, project, or object libray", which I also have never seen before. But when I dimension the recordset mentioned above as a DAO recordset, Access happily accepts it and compiles.

So I'm pretty lost and totally stuck with that application.

Thanks for help
Bernard
LVL 1
bthouinAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
This often means that your query references a form with [Forms]![YourFormName]![SomeFieldName]

This you will have to supply when running from code:

Set db = CurrentDb
Set qd = db.QueryDefs("queryName")
qd.Parameters(0).Value = [Forms]![YourFormName]![SomeFieldName]
Set rs = qd.OpenRecordset()

/gustav
0
 
bthouinAuthor Commented:
Oh, Gustav, you are spot on ! That is indeed the case.

You saved my day, I was getting desperate...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
When you open a query in code, resolving any references is left up to you.

If all your references are form/control references, then you can do it like this:

Dim db As Database
Dim qdef As QueryDef
Dim prm as Parameter
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdef.OpenRecordset()  


You could also set the paramertes by name:

qdef.Parameters("<name>") = Eval(qdef.Parameters(0).Name)  

and if the name of the parameter is not a reference, then you must set it directly:

qdef.Parameters(0)  =  strMyCrieria  

For example.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.