Randomly Passed Parameters (1 Viewer)

Campbell MH

New member
Local time
Today, 09:21
Joined
Aug 19, 2020
Messages
2
I have an Access Database that is reading data from a SharePoint list,
then outputting the data into an excel spreadsheet There are many queries involved, about 40 total.

Here is the format of each query:

Set qdf20 = db.QueryDefs("<Query Name Here>")
qdf20!Param1 = Param
Set rst20 = qdf20.OpenRecordset()
qdf20.Close
rst20.Close
Set qdf20 = Nothing
Set rst20 = Nothing

This is in a 'For' loop, and the 'Param' changes with each iteration of the loop.
The problem: Some queries work, and some do not. The only thing that is changed
is the query name, the record set name and the query def name. The errors are
totally random. The parameters are in an array, and the for the queries that work,
all the parameters are successfully transferred.

Solutions I have tried are using the Parameters Collection Object, and using the SQL commands directly in VBA, but that didn't help.
The error I receive is: "Too few parameters, expected 1". I have read the other posts concerning this same error, but they do not solve the problem.

I'm at my wits-end trying to find a solution. Can anyone offer any insight?
It would be very helpful and much appreciated.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:21
Joined
May 21, 2018
Messages
3,631
Are the parameters in the array variants? If not a string passed to a numeric would give that error. Can you debug by printing out the value of the parameter when it fails and the query def sql.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:21
Joined
Oct 29, 2018
Messages
13,202
Hi. Just a wild guess, but could it possibly be a timing issue?
 

onur_can

Member
Local time
Today, 06:21
Joined
Oct 4, 2015
Messages
116
You should be careful when using query parameters in VBA. Because the syntax is written differently for each variable type you specify in the parameter. It shows this in the error you received. I think there is an error in field names, variable names and table names. Or the domain name you used in the code may not be included in the query. Pay attention to using single quotes or double quotes when using variables!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:21
Joined
May 21, 2018
Messages
3,631
You should be careful when using query parameters in VBA. Because the syntax is written differently for each variable type you specify in the parameter.
I am not sure I understand that, I would think it is the opposite.
The beauty of parameters you do not have to format the values unlike an insert query.
p1 = txtBxDate
p2 = txtBxSomeString
P3 = txtBxSomeNumber
P4 = txtBxWithNull
p5 = txtbxSomeTime
p6 = chkYesNoField
 

arnelgp

error reading drive A:
Local time
Today, 22:21
Joined
May 7, 2009
Messages
10,858
do all the queries have same Parameter Name, param1?
do all quries have only 1 parameter?

can you use something like:

Set qdf20 = db.QueryDefs("<Query Name Here>")
qdf20.parameters(0) = Param
Set rst20 = qdf20.OpenRecordset
qdf20.Close
rst20.Close
Set qdf20 = Nothing
Set rst20 = Nothing
 

Users who are viewing this thread

Top Bottom