SQL Execution errer

  • Thread starter Thread starter Jim S
  • Start date Start date
J

Jim S

When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData.[Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
 
Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."

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

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

Jim S said:
When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData.[Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
Click to expand...
 
Allan,

The code below creates a working query. As I mentioned
below, I can run the query that is created by this code.
I get the error when I try to ececute the query using
code.

This has always worked in the past:

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

Jim
-----Original Message-----
Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."

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

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

When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData. [Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
Click to expand...


.
Click to expand...
 
That is correct.

In the context of the query itself, the Expression Service is available to
resolve the reference to the form. In the context of the DAO code, the
Expression Service is not available. The reference to the form does not
resolve, and so it is treated as a parameter.

Solve the problem by writing the SQL statement as a string in your code,
instead of relying on the saved query.

An alternative solution is to supply each parameter in your code, e.g.:
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Parameters("Forms!MyForm!MyControl") = Forms!MyForm!MyControl
qdf.Parameters("Forms!MyForm!Text1") = Forms!MyForm!Text1
'etc.
qdf.Execute

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

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

Jim S. said:
Allan,

The code below creates a working query. As I mentioned
below, I can run the query that is created by this code.
I get the error when I try to ececute the query using
code.

This has always worked in the past:

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

Jim
-----Original Message-----
Concatenate the value form the control into the string, e.g.:
"WHERE [MyField] = " & Forms!MyForm!MyControl & " AND ..."

Don't forget the delimiters: # for dates, and " for text. So if MyField is a
Text type field, you need:
"WHERE [MyField] = """ & Forms!MyForm!MyControl & """ AND ..."


When I run the following code, I get "Run - Time Error
3061" "Too Few parameters" The code creates the make -
table query, but gives the error when the code tries to
execute the query and create the table. (Running the
query manually creates the table)

I have created many tables with code in then past with
few problems.

Any idea how get this query to execute by using code?

Jim


Sub CreateTb1()

'Create a SQL Make Table Query
'Create Table1 - Table of locations in the selected
records

Dim db As Database
Dim qdf As QueryDef
Dim A As String, B As String, C As String
Dim D As String, E As String, F As String
Dim G As String, H As String, I As String
Dim J As String, K As String, L As String
Dim M As String, N As String, O As String
Dim P As String
Dim QrySQL As String

'Pieces of SQL Statment
A = "SELECT qry_RawData.Location INTO Temp1"
B = "FROM qry_RawData"
C = "GROUP BY qry_RawData.Pedigree, qry_RawData. [Nesting
Group], qry_RawData.[EU Entry Number], qry_RawData.Rep,
qry_RawData.[EU Data Quality], qry_RawData.[EU SPPLOT
(list)], qry_RawData.[Expt Name],"
D = "qry_RawData.Location, qry_RawData.[Location Site
Code], qry_RawData.[EU Inventory ID], qry_RawData.[EU
Source Name], qry_RawData.[Site Short Name]"
E = "HAVING (((qry_RawData.Pedigree) Like "
F = " & [Forms]![frm_Main]![Combo6] & "
G = ") AND ((qry_RawData.[Nesting Group]) Like "
H = " & [Forms]![frm_Main]![Combo1] & "
I = ") AND ((qry_RawData.[EU Entry "
J = "Number])=[Forms]![frm_Main]![Combo16]) AND
((qry_RawData.Rep) Like "
K = " & [Forms]![frm_Main]![Combo18]) AND ((qry_RawData.
[EU Data Quality]) Like "
L = " & [Forms]![frm_Main]![Combo20]) AND ((qry_RawData.
[EU SPPLOT (list)])Like "
M = " & [Forms]![frm_Main]![Combo22]))"
N = "ORDER BY qry_RawData.[Nesting Group], qry_RawData.
[EU Entry Number], qry_RawData.Rep, qry_RawData.Location;"

O = A & Chr(10) & B & Chr(10) & C & D & Chr(10) & E & Chr
(34) & "*" & Chr(34) & F & Chr(34) & "*" & Chr(34) & G &
Chr(34) & "*" & Chr(34) & H & Chr(34) & "*" & Chr(34) & I
P = J & Chr(34) & "*" & Chr(34) & K & Chr(34) & "*" & Chr
(34) & L & Chr(34) & "*" & Chr(34) & M & Chr(10) & N

'Create SQL
QrySQL = O & P

'Create query
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qry_Temp1", QrySQL)
qdf.Execute
qdf.Close
db.Close
Set qdf = Nothing

'Delete Query "qry_Temp1"
'DoCmd.DeleteObject acQuery, "qry_Temp1"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top