QueryDefs - Execute - Won't Run

[Guest]

Hi Thanks in Advance (Using 2000)

Trying to run the following append query, but I get an error "Too Few
Parameters Expected 6". What am I doing wrong?


Dim db As DAO.Database
Dim lngCount As Long

Set db = CurrentDb

db.QueryDefs("Q_App_01_Unity").Execute
lngCount = db.QueryDefs("Q_App_01_Unity").RecordsAffected

Debug.Print lngCount

Set db = Nothing


Thanks Trev

 

[Duane Hookom]

You might want to share the SQL view of your query.

 

[Allen Browne]

Open Q_App_01_Unity in SQL View (View menu, from query design.
What is in the SQL statement?

When you run the query from the query window, or as the source for a form or
report, it calls the Expression Service (ES) to evaluate any reference such
as:
[Forms].[Form1].[Text0]
but the ES is not available for Execute.

One alternative is to tell VBA how to evalute the parameters before
executing, e.g.:
Dim qdf As AueryDef
Set qdf = db..QueryDefs("Q_App_01_Unity")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
'etc.
qdf.Execute

Most times, I find it easier and more flexible to dispense with the saved
query, and build the SQL string at runtime, e.g.:
Dim strSql As String
strSql = "INSERT INTO ... WHERE City = """ & Forms.Form1.Text0 & """;"
db.Execute strSql, dbFailOnError

 

[Guest]

Thanks Allen Great.

Another Quick qustion how do I pass multi Parameters Works with 1 can't do
more.

qdf.Parameters("[Forms]![Form Import]![Text10]") = [Forms]![Form
Import]![Text10] & _
("[Forms]![Form Import]![Text12]") = [Forms]![Form
Import]![Text12]

Trev

Open Q_App_01_Unity in SQL View (View menu, from query design.
What is in the SQL statement?

When you run the query from the query window, or as the source for a form or
report, it calls the Expression Service (ES) to evaluate any reference such
as:
[Forms].[Form1].[Text0]
but the ES is not available for Execute.

One alternative is to tell VBA how to evalute the parameters before
executing, e.g.:
Dim qdf As AueryDef
Set qdf = db..QueryDefs("Q_App_01_Unity")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
'etc.
qdf.Execute

Most times, I find it easier and more flexible to dispense with the saved
query, and build the SQL string at runtime, e.g.:
Dim strSql As String
strSql = "INSERT INTO ... WHERE City = """ & Forms.Form1.Text0 & """;"
db.Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Thanks in Advance (Using 2000)

Trying to run the following append query, but I get an error "Too Few
Parameters Expected 6". What am I doing wrong?


Dim db As DAO.Database
Dim lngCount As Long

Set db = CurrentDb

db.QueryDefs("Q_App_01_Unity").Execute
lngCount = db.QueryDefs("Q_App_01_Unity").RecordsAffected

Debug.Print lngCount

Set db = Nothing


Thanks Trev

 

[Allen Browne]

One line of code for each parameter:

qdf.Parameters("[Forms]![Form Import]![Text10]") = _
[Forms]![Form Import]![Text10]
qdf.Parameters("[Forms]![Form Import]![Text12]") = _
[Forms]![Form Import]![Text12]

Since I prefer to build the SQL statement dynamically, I haven't tested
this, but it might be possible to loop through the Parameters collection (by
number), and use Eval to get the value:

For i = 0 to qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen Great.

Another Quick qustion how do I pass multi Parameters Works with 1 can't do
more.

qdf.Parameters("[Forms]![Form Import]![Text10]") = [Forms]![Form
Import]![Text10] & _
("[Forms]![Form Import]![Text12]") = [Forms]![Form
Import]![Text12]

Trev

Open Q_App_01_Unity in SQL View (View menu, from query design.
What is in the SQL statement?

When you run the query from the query window, or as the source for a form
or
report, it calls the Expression Service (ES) to evaluate any reference
such
as:
[Forms].[Form1].[Text0]
but the ES is not available for Execute.

One alternative is to tell VBA how to evalute the parameters before
executing, e.g.:
Dim qdf As AueryDef
Set qdf = db..QueryDefs("Q_App_01_Unity")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
'etc.
qdf.Execute

Most times, I find it easier and more flexible to dispense with the saved
query, and build the SQL string at runtime, e.g.:
Dim strSql As String
strSql = "INSERT INTO ... WHERE City = """ & Forms.Form1.Text0 &
""";"
db.Execute strSql, dbFailOnError

Hi Thanks in Advance (Using 2000)

Trying to run the following append query, but I get an error "Too Few
Parameters Expected 6". What am I doing wrong?


Dim db As DAO.Database
Dim lngCount As Long

Set db = CurrentDb

db.QueryDefs("Q_App_01_Unity").Execute
lngCount = db.QueryDefs("Q_App_01_Unity").RecordsAffected

Debug.Print lngCount

Set db = Nothing