Re: Too few parameters, expected n when executing SQL from VBA

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>



<pietlinden@xxxxxxxxxxx> wrote in message
news:1194911270.831346.281200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Importing text, etc. on remote .mdb file
    ... You can then use VBA string functions to get from there to the ... Is there an easy way to schedule the imports of the text files so I ... 'Modify strSQL and filenames as required ... Dim oJet 'DAO.DBEngine ...
    (microsoft.public.access.externaldata)
  • Re: Delete button and refresh
    ... which is great except that it filters the form to only include the records ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)