-
Aug 25th, 2020, 01:01 PM
#1
Thread Starter
New Member
Parameter Issues
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.
-
Aug 25th, 2020, 01:20 PM
#2
Re: Parameter Issues
Welcome to VBForums
The cause of the issue is actually likely to be something other than parameters.
In many cases the issue is caused by a "bad" name for a field/table/query/etc, perhaps because of a space in the name, or maybe because the name is a reserved word. If you can change the names (in the database and your code), it is likely to work correctly.
For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)
If for some reason you cannot change the name (and it sounds like you might not be able to in this case), then try putting the name inside square brackets, eg: [Select] or [Name with spaces in]
-
Aug 26th, 2020, 01:36 AM
#3
Re: Parameter Issues
What about a race-condition?
He bulk-updates/inserts in first call, in his second call he updates/inserts something else which needs references from the first call (PrimaryKey, whatever), but the backend is still processing the first call.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 18th, 2020, 11:46 AM
#4
Re: Parameter Issues
The errors are totally random.
this rules a bad name out. race condition yes possible but as long as its only him working on the access db i doubt that inserts/updates/etc run in parallel.
it would be good to know the error code. could also be a timeout. and i personaly know sharepoint from behaving like a nightmare in some areas, so i could also imagine a crumbled response from sharepoint server.
-
Sep 18th, 2020, 12:01 PM
#5
Re: Parameter Issues
Originally Posted by digitalShaman
this rules a bad name out. race condition yes possible but as long as its only him working on the access db i doubt that inserts/updates/etc run in parallel.
it would be good to know the error code. could also be a timeout. and i personaly know sharepoint from behaving like a nightmare in some areas, so i could also imagine a crumbled response from sharepoint server.
To be fair, until we know what the errors are, we can't rule anything out. We also can't rule out the nature of the queries... like you mentioned. SharePoint is horrible to work at times and only tolerable the rest of the time. Certainly not a picnic.
-tg
-
Sep 18th, 2020, 12:04 PM
#6
Re: Parameter Issues
Originally Posted by Campbell MH
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
I havn't done this for a long time, but the way you pass the parameter looks wrong
should look something like this
Code:
Dim qd As QueryDef
Set qd = db.QueryDefs("QueryName")
qd.Parameters("pID").Value = Text1.Text
'more parameters
Set rs = qd.OpenRecordset
'etc...
HTH
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Sep 18th, 2020, 01:56 PM
#7
Re: Parameter Issues
It's using a different style... it's the same as recordset!fieldname... which works well enough, but can have some limitations.
But... that does make me think of something. Are all the parameters the same type? OR are some one trype, and others another? And are the ones that fail, are they of a particular type? OR of a type DIFFERENT from the previous query?
Again, this is why the error message(s) are important.
-tg
-
Sep 19th, 2020, 12:20 AM
#8
Re: Parameter Issues
Originally Posted by techgnome
But... that does make me think of something. Are all the parameters the same type? OR are some one trype, and others another? And are the ones that fail, are they of a particular type? OR of a type DIFFERENT from the previous query?
Again, this is why the error message(s) are important.
-tg
I think that's the issue
if you create the ParmeterQuery with Code you have to pass the Type
Code:
Set qdf = dbs.CreateQueryDef("myQuery")
strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
strSQL = strSQL & "SELECT * FROM [Table1] "
strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
qdf.SQL = strSQL
this will create the Parameters for TEXT and a NUMBER
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|