Too few parameters, expected n when executing SQL from VBA

[pietlinden]

I read this article... apparently I didn't understand it as well as I
thought I did...
http://www.mvps.org/access/queries/qry0013.htm

What I'm doing... (in case it helps).

I have a seriously denormalized table, that looks like this:

CREATE TABLE Induction(
RecordID Autonumber,
ANEMIA Number,
NEUTROPENIA Number,
.....
)

All the symptoms are in all caps (in case it matters). So I can
identify all those with a function.

because I have to normalize this mess, I'm inserting records into a
normalized table.

(PatientID, Symptom, Grade, CycleNumber, Phase)

so I created a single append query and then used Replace to modify the
SQL.

Here's the routine to do the replace...
Public Sub TestQuery(ByVal strFieldName As String)
Dim strSQL As String
Dim strNewSQL As String
Dim qdf As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim prm As DAO.Parameter

Set qdf = DBEngine(0)(0).QueryDefs("qapp Anemia")
strSQL = qdf.SQL

strNewSQL = Replace(qdf.SQL, "Anemia", strFieldName)
Debug.Print "Old SQL:"
Debug.Print qdf.SQL
Debug.Print
Debug.Print "New SQL"
Debug.Print strNewSQL
'CurrentDb.Execute strNewSQL, dbFailOnError
Set qdfNew = New QueryDef
qdfNew.SQL = strNewSQL

For Each prm In qdfNew.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute
Debug.Print qdf.RecordsAffected
End Sub


here's a sample output of the "TestQuery" function:
testquery "Allopecia"
Old SQL:
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Anemia" AS Symptom, [Toxicity Induction].ANEMIA
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].ANEMIA) Is Not Null));

'---routine returns this....
New SQL
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Allopecia" AS Symptom, [Toxicity Induction].Allopecia
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].Allopecia) Is Not Null));

when I try to run the query, I get "Too few parameters, expected
n" (see the article).

Even with the parameters supposedly getting evaluated, it still
returns the same error. What am I doing wrong?

It probably sounds like a lot of work to fix a few queries, but I have
lots of databases that are not normalized, so I'm trying to create a
routine that will automate most of the dirty work for me. so that's
why I'm doing all this in code...

Any ideas what I'm doing wrong? (Maybe I just need to take a walk or
something... )

Thanks for the help,

Pieter

 

[Ken Snell \(MVP\)]

Yoiu're on the right track, but your code approach has some coding errors...
additionally, using the "evaluate parameters" approach assumes that you're
going to open a recordset based on that query after you resolve the
parameters, which you're not doing. I don't see any parameters in your
desired SQL statement, either, so I suggest a completely different approach
where you just build the SQL statement and then execute it:


'Start of code
Public Sub TestQuery(ByVal strFieldName As String)
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Set dbs = CurrentDb

' Create the desired SQL statement, starting from
' the stored query's SQL statement
strSQL = dbs.QueryDefs("qapp Anemia").SQL
strSQL = Replace(strSQL, "Anemia", strFieldName)
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing
Exit Sub
End Sub
'End of code

--

Ken Snell
<MS ACCESS MVP>

I read this article... apparently I didn't understand it as well as I
thought I did...
http://www.mvps.org/access/queries/qry0013.htm

What I'm doing... (in case it helps).

I have a seriously denormalized table, that looks like this:

CREATE TABLE Induction(
RecordID Autonumber,
ANEMIA Number,
NEUTROPENIA Number,
....
)

All the symptoms are in all caps (in case it matters). So I can
identify all those with a function.

because I have to normalize this mess, I'm inserting records into a
normalized table.

(PatientID, Symptom, Grade, CycleNumber, Phase)

so I created a single append query and then used Replace to modify the
SQL.

Here's the routine to do the replace...
Public Sub TestQuery(ByVal strFieldName As String)
Dim strSQL As String
Dim strNewSQL As String
Dim qdf As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim prm As DAO.Parameter

Set qdf = DBEngine(0)(0).QueryDefs("qapp Anemia")
strSQL = qdf.SQL

strNewSQL = Replace(qdf.SQL, "Anemia", strFieldName)
Debug.Print "Old SQL:"
Debug.Print qdf.SQL
Debug.Print
Debug.Print "New SQL"
Debug.Print strNewSQL
'CurrentDb.Execute strNewSQL, dbFailOnError
Set qdfNew = New QueryDef
qdfNew.SQL = strNewSQL

For Each prm In qdfNew.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute
Debug.Print qdf.RecordsAffected
End Sub


here's a sample output of the "TestQuery" function:
testquery "Allopecia"
Old SQL:
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Anemia" AS Symptom, [Toxicity Induction].ANEMIA
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].ANEMIA) Is Not Null));

'---routine returns this....
New SQL
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Allopecia" AS Symptom, [Toxicity Induction].Allopecia
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].Allopecia) Is Not Null));

when I try to run the query, I get "Too few parameters, expected
n" (see the article).

Even with the parameters supposedly getting evaluated, it still
returns the same error. What am I doing wrong?

It probably sounds like a lot of work to fix a few queries, but I have
lots of databases that are not normalized, so I'm trying to create a
routine that will automate most of the dirty work for me. so that's
why I'm doing all this in code...

Any ideas what I'm doing wrong? (Maybe I just need to take a walk or
something... )

Thanks for the help,

Pieter

 

[pietlinden]

Yoiu're on the right track, but your code approach has some coding errors...
additionally, using the "evaluate parameters" approach assumes that you're
going to open a recordset based on that query after you resolve the
parameters, which you're not doing. I don't see any parameters in your
desired SQL statement, either, so I suggest a completely different approach
where you just build the SQL statement and then execute it:

'Start of code
Public Sub TestQuery(ByVal strFieldName As String)
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Set dbs = CurrentDb

' Create the desired SQL statement, starting from
' the stored query's SQL statement
strSQL = dbs.QueryDefs("qapp Anemia").SQL
strSQL = Replace(strSQL, "Anemia", strFieldName)
dbs.Execute strSQL, dbFailOnError
dbs.Close
Set dbs = Nothing
Exit Sub
End Sub
'End of code

--

Ken Snell
<MS ACCESS MVP>

I read this article... apparently I didn't understand it as well as I
thought I did...
http://www.mvps.org/access/queries/qry0013.htm

What I'm doing... (in case it helps).

I have a seriously denormalized table, that looks like this:

CREATE TABLE Induction(
RecordID Autonumber,
ANEMIA Number,
NEUTROPENIA Number,
....
)

All the symptoms are in all caps (in case it matters). So I can
identify all those with a function.

because I have to normalize this mess, I'm inserting records into a
normalized table.

(PatientID, Symptom, Grade, CycleNumber, Phase)

so I created a single append query and then used Replace to modify the
SQL.

Here's the routine to do the replace...
Public Sub TestQuery(ByVal strFieldName As String)
Dim strSQL As String
Dim strNewSQL As String
Dim qdf As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim prm As DAO.Parameter

Set qdf = DBEngine(0)(0).QueryDefs("qapp Anemia")
strSQL = qdf.SQL

strNewSQL = Replace(qdf.SQL, "Anemia", strFieldName)
Debug.Print "Old SQL:"
Debug.Print qdf.SQL
Debug.Print
Debug.Print "New SQL"
Debug.Print strNewSQL
'CurrentDb.Execute strNewSQL, dbFailOnError
Set qdfNew = New QueryDef
qdfNew.SQL = strNewSQL

For Each prm In qdfNew.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute
Debug.Print qdf.RecordsAffected
End Sub

here's a sample output of the "TestQuery" function:
testquery "Allopecia"
Old SQL:
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Anemia" AS Symptom, [Toxicity Induction].ANEMIA
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].ANEMIA) Is Not Null));

'---routine returns this....
New SQL
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Allopecia" AS Symptom, [Toxicity Induction].Allopecia
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].Allopecia) Is Not Null));

when I try to run the query, I get "Too few parameters, expected
n" (see the article).

Even with the parameters supposedly getting evaluated, it still
returns the same error. What am I doing wrong?

It probably sounds like a lot of work to fix a few queries, but I have
lots of databases that are not normalized, so I'm trying to create a
routine that will automate most of the dirty work for me. so that's
why I'm doing all this in code...

Any ideas what I'm doing wrong? (Maybe I just need to take a walk or
something... )

Thanks for the help,

Pieter- Hide quoted text -

- Show quoted text -

OIC!!! Eye gnu about that... but I was such a dumb beast that I
didn't see it...