Using Variables in SQL SELECT....INTO

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access 97

I am creating a table in a temp database and linking it to the current db.
This is working well longhand as far as the query is concerned.
But I would like to use variables representing the query in the statement.

The code example is
dbs.Execute "SELECT qryAllToday.* INTO " & TempTable & " IN '" &
ReportsTempDBase & "' FROM qryAllInToday;"

Where
qryAllToday is a select query in the current mdb
TempTable is already a variable representing the desired name in the temp
mdb.
ReportsTempDBase is already a variable representing the path and name of the
temp DB.
This works ok.

But I am struggling to get it to work by substituting the query
[qryAllToday] here as a variable (DIM'd as a String).
I am getting various errors depending on the variation I make in attempting
to get it to work.
I do not want to include the .* representing all records from the query as
part of the queryname variable, but have it in the statement separately, so
I can recycle the code. Any help gratefully appreciated.

Regards,
WSF
 
CORRECTION
Sorry, but the expression SELECT qryAllToday.* should be qryAllInToday.* ,
same as FROM.
Typo on my part in post only.

WSF
 
Try:

****Untested****
Dim strSQL As String

strSQL = "SELECT [" & strQueryName & "].* INTO [" & _
TempTable & "] IN '" & ReportsTempDBase & _
"' FROM [" & strQueryName & "];"

Debug.Print strSQL

dbs.Execute strSQL
********

Look in the Debug window to see what is actually passed to
JET Database Engine and modify the SQL construction if
required.

HTH
Van T. Dinh
MVP (Access)
 
Thanks Van,
The advice you give works to a point. It recognizes the query.
But it produces the following error.
"3061 - Too few parameters. expected 2."

My query is a select query created in design view and saved, with a
criterion therein of:

Between [forms]![frmMain]![txtStartDate] And
[forms]![frmMain]![txtEndDate]+1

I have checked and the query runs okay manually.
But trying to execute it via VBA causes the above error.

Any idea what could be causing this?
When I change the above statement

Should this criterion be included in the code, rather than the query design
itself?

Thanks again,
WSF


Van T. Dinh said:
Try:

****Untested****
Dim strSQL As String

strSQL = "SELECT [" & strQueryName & "].* INTO [" & _
TempTable & "] IN '" & ReportsTempDBase & _
"' FROM [" & strQueryName & "];"

Debug.Print strSQL

dbs.Execute strSQL
********

Look in the Debug window to see what is actually passed to
JET Database Engine and modify the SQL construction if
required.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Access 97

I am creating a table in a temp database and linking it to the current db.
This is working well longhand as far as the query is concerned.
But I would like to use variables representing the query in the statement.

The code example is
dbs.Execute "SELECT qryAllToday.* INTO " & TempTable & " IN '" &
ReportsTempDBase & "' FROM qryAllInToday;"

Where
qryAllToday is a select query in the current mdb
TempTable is already a variable representing the desired name in the temp
mdb.
ReportsTempDBase is already a variable representing the path and name of the
temp DB.
This works ok.

But I am struggling to get it to work by substituting the query
[qryAllToday] here as a variable (DIM'd as a String).
I am getting various errors depending on the variation I make in attempting
to get it to work.
I do not want to include the .* representing all records from the query as
part of the queryname variable, but have it in the statement separately, so
I can recycle the code. Any help gratefully appreciated.

Regards,
WSF


.
Click to expand...
Click to expand...
 
Check the String in the Debug window and make sure it is correct as I
mentioned in my previous reply.

--
HTH
Van T. Dinh
MVP (Access)



WSF said:
Thanks Van,
The advice you give works to a point. It recognizes the query.
But it produces the following error.
"3061 - Too few parameters. expected 2."

My query is a select query created in design view and saved, with a
criterion therein of:

Between [forms]![frmMain]![txtStartDate] And
[forms]![frmMain]![txtEndDate]+1

I have checked and the query runs okay manually.
But trying to execute it via VBA causes the above error.

Any idea what could be causing this?
When I change the above statement

Should this criterion be included in the code, rather than the query design
itself?

Thanks again,
WSF


Van T. Dinh said:
Try:

****Untested****
Dim strSQL As String

strSQL = "SELECT [" & strQueryName & "].* INTO [" & _
TempTable & "] IN '" & ReportsTempDBase & _
"' FROM [" & strQueryName & "];"

Debug.Print strSQL

dbs.Execute strSQL
********

Look in the Debug window to see what is actually passed to
JET Database Engine and modify the SQL construction if
required.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Access 97

I am creating a table in a temp database and linking it to the current db.
This is working well longhand as far as the query is concerned.
But I would like to use variables representing the query in the statement.

The code example is
dbs.Execute "SELECT qryAllToday.* INTO " & TempTable & " IN '" &
ReportsTempDBase & "' FROM qryAllInToday;"

Where
qryAllToday is a select query in the current mdb
TempTable is already a variable representing the desired name in the temp
mdb.
ReportsTempDBase is already a variable representing the path and name of the
temp DB.
This works ok.

But I am struggling to get it to work by substituting the query
[qryAllToday] here as a variable (DIM'd as a String).
I am getting various errors depending on the variation I make in attempting
to get it to work.
I do not want to include the .* representing all records from the query as
part of the queryname variable, but have it in the statement separately, so
I can recycle the code. Any help gratefully appreciated.

Regards,
WSF


.
Click to expand...
Click to expand...
Click to expand...
 
WSF said:
Thanks Van,
The advice you give works to a point. It recognizes the query.
But it produces the following error.
"3061 - Too few parameters. expected 2."

My query is a select query created in design view and saved, with a
criterion therein of:

Between [forms]![frmMain]![txtStartDate] And
[forms]![frmMain]![txtEndDate]+1

I have checked and the query runs okay manually.
But trying to execute it via VBA causes the above error.

Any idea what could be causing this?
When I change the above statement

Should this criterion be included in the code, rather than the query
design itself?
Click to expand...

PMFJI, but if you're using the DAO execute method to run the SQL
statement, the form-control references will be seen as parameters and
won't be resolved automatically, the way they are when Access itself
runs the query. You could either use

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

or else create a QueryDef object, set its SQL property to strSQL, and
then resolve its parameters. That would look something like this (air
code):

' ...
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

' ...
Set qdf = dbs.CreateQueryDef("", strSQL)

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

Set qdf = Nothing
 
Thanks you very much Dirk.
Your first suggestion does the trick.
I'm doing some data digging and reports and some is a little more complex
that I am used to.
Regards,
Bill Fraser


Dirk Goldgar said:
WSF said:
Thanks Van,
The advice you give works to a point. It recognizes the query.
But it produces the following error.
"3061 - Too few parameters. expected 2."

My query is a select query created in design view and saved, with a
criterion therein of:

Between [forms]![frmMain]![txtStartDate] And
[forms]![frmMain]![txtEndDate]+1

I have checked and the query runs okay manually.
But trying to execute it via VBA causes the above error.

Any idea what could be causing this?
When I change the above statement

Should this criterion be included in the code, rather than the query
design itself?
Click to expand...

PMFJI, but if you're using the DAO execute method to run the SQL
statement, the form-control references will be seen as parameters and
won't be resolved automatically, the way they are when Access itself
runs the query. You could either use

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

or else create a QueryDef object, set its SQL property to strSQL, and
then resolve its parameters. That would look something like this (air
code):

' ...
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

' ...
Set qdf = dbs.CreateQueryDef("", strSQL)

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

Set qdf = Nothing

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top