Access 2003: QueryDef.OpenRecordset method with parameters

[Guest]

I'm currently working at migrating a large Access 97 application to Access
2003. One of the differences between the two versions that I've come across
is the use of the QueryDef.OpenRecordset method when the QueryDef has
parameters.

Here is some sample code that works fine in Access 97:
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("qxtbTest")

qd.Parameters(0) = 1
qd.Parameters(1) = 5

Set rs = qd.OpenRecordset()


However, this code does not work in Access 2003. The last line results in
an "Invalid argument" error message. I've tried explicitly specifying the
type and options arguments in the OpenRecordset method call with the same
results.

I have not found any entries in the knowledge base and am wondering if
anyone else can confirm this change in behaviour? I'm not sure what the
behaviour is in Access 2000 or 2002 as I do not have access to those versions.

Finally, it looks like I'll need to find another way to write this code in
Access 2003 and am just wondering if anyone has suggestions for me. The
point of the code is that I'm using a crosstab query as the RecordSource of a
report. Since a crosstab can return a varying number of columns, I open a
recordset in the report's Open event with the crosstab query so I can
determine how many columns there are and what the caption should be of each
of the variable columns.

One option would be to avoid using parameters in the query by modifying the
query's SQL each time just before I open the report to use the new values in
the WHERE clause of the query. I'd prefer to find another approach, since
I've found that this technique is difficult to document and causes problems
later in code maintenance. Any better ideas? Would this code work if
rewritten in ADO?

Thanks,
David Kroeker
Programmer/Analyst
B.Sc. Computer Engineering

 

[Allen Browne]

Hi David

Just tried your code in Access 2003, and it works fine here.

A couple of suggestions:

1. Try disambiguating, i.e.:
Dim rs As DAO.Recordset
The ADO library also has a Recordset object.

2. Have you explicitly declared the parameters?
This is always important, but crucial with crosstabs.

If #1 solves the problem, either remove the reference to the ADO library, or
disambiguate all references (or both). More info on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

 

[david epsom dot com dot au]

Access 2000 is even more picky about query parameters than
97 was. Check that the parameters are DECLARED in qxtbTest,
and that the declared type is correct, and that the actual
parameter values are the correct type.

 

[Guest]

1. Try disambiguating, i.e.:
Dim rs As DAO.Recordset
The ADO library also has a Recordset object.

Our application actually uses DAO exclusively. We have not added any ADO
code or a reference to the ADO library to avoid having to worry about
modifying all of our ambiguous declarations.

2. Have you explicitly declared the parameters?
This is always important, but crucial with crosstabs.

Yes, the parameters are declared the query and correctly typed. The
parameter names refer to a form control:
[Forms!fmnuIntReportSwitchBrd!txtP1Effective]
I'm going to test some more and I'll post my findings here.

Allen, since the code seems to work for you, I'd be really interested to
figure out what the difference is.

David Kroeker
Programmer/Analyst
B.Sc. Computer Engineering

 

[Guest]

After some more testing, it looks like the problem depends on the complexity
of the crosstab query itself. I took the original crosstab query that was
causing the errors and started deleting tables and removing portions of the
WHERE clause from the SQL. Then, I'd retest after removing a few items.
Once I simplified the query enough, it ran just fine. Of course, the query
no longer returns the information I want so I can't actually use it, but it
helps to explain why the problem doesn't happen all the time.

The workaround that I've settled on is to delete the parameters in my
crosstab query and use a function call in the SQL instead. This function
returns the value of the form control I want, so I don't need to use any
parameters.

I'll try testing this with ADO to confirm if the same limitation exists there.

Thanks,
David Kroeker
Programmer/Analyst
B.Sc. Computer Engineering

 

[Guest]

I've rewritten the code using ADO, and it works just fine. So, this is only
a limitation of DAO.

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qxtbTest"
cmd.CommandType = adCmdTable
cmd.Parameters(0) = 1
cmd.Parameters(1) = 5

Set rs = cmd.Execute

Thanks to all who responded for their ideas,
David Kroeker
Programmer/Analyst
B.Sc. Computer Engineering