Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
SQL query that uses parameters
24 views
Skip to first unread message
colmkav
May 22, 2012, 10:36:02 AM5/22/12
to
Hi,
I am trying to write an Access query where I choose the tablename the
results will be output to. I thought the following should work but I
end up with the results stored in the actual tablename called
"tableName"
PARAMETERS [TableName] CHAR;
SELECT [Averages - 2].* INTO [@TableName]
FROM [Averages - 2];
Can you tell me how to correct this?
I am trying to write an Access query where I choose the tablename the
results will be output to. I thought the following should work but I
end up with the results stored in the actual tablename called
"tableName"
PARAMETERS [TableName] CHAR;
SELECT [Averages - 2].* INTO [@TableName]
FROM [Averages - 2];
Can you tell me how to correct this?
Bob Barrows
May 22, 2012, 12:50:23 PM5/22/12
to
have to concatenate the value of the TableName variable into the sql string
and execute that. Dynamic SQL.
dim Tablename as string,sSQL as string
TableName = <not sure where you are getting this value>
sSQL="SELECT [Averages - 2].* INTO [" & _
TableName & "] FROM [Averages - 2]"
currentdb.execute sSQL
Are you sure you need to store the results of this query? Have you thought
about the consequences if the "chosen" table already exists? Clue, if the
table already exists, an error will be raised. Data will not be appended
into an existing table. You have to use an INSERT (append) query for that.
Tony Toews
May 23, 2012, 5:27:16 PM5/23/12
to
On Tue, 22 May 2012 07:36:02 -0700 (PDT), colmkav
<colm...@yahoo.co.uk> wrote:
>I am trying to write an Access query where I choose the tablename the
>results will be output to.
Why not create a SQL string with all the relevant portions of the
<colm...@yahoo.co.uk> wrote:
>I am trying to write an Access query where I choose the tablename the
>results will be output to.
query and execute it directly.
strSQL = "INSERT INTO <tablename> ( <fieldname>, ... )" & _
"VALUES (<value>, ...);"
currentdb.Execute strSQL, dbFailOnErrors
Much easier to work with and debug.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Gene Wirchenko
May 23, 2012, 6:05:36 PM5/23/12
to
On Wed, 23 May 2012 15:27:16 -0600, Tony Toews
<tto...@telusplanet.net> wrote:
>On Tue, 22 May 2012 07:36:02 -0700 (PDT), colmkav
><colm...@yahoo.co.uk> wrote:
>
>>I am trying to write an Access query where I choose the tablename the
>>results will be output to.
>
>Why not create a SQL string with all the relevant portions of the
>query and execute it directly.
Ah, the possibility of an SQL injection attack?
<tto...@telusplanet.net> wrote:
>On Tue, 22 May 2012 07:36:02 -0700 (PDT), colmkav
><colm...@yahoo.co.uk> wrote:
>
>>I am trying to write an Access query where I choose the tablename the
>>results will be output to.
>
>Why not create a SQL string with all the relevant portions of the
>query and execute it directly.
> strSQL = "INSERT INTO <tablename> ( <fieldname>, ... )" & _
> "VALUES (<value>, ...);"
> currentdb.Execute strSQL, dbFailOnErrors
>
>Much easier to work with and debug.
Gene Wirchenko
Tony Toews
May 24, 2012, 1:24:05 AM5/24/12
to
On Wed, 23 May 2012 15:05:36 -0700, Gene Wirchenko <ge...@ocis.net>
1) presumably this is within Access and thus semi trusted employees
are running the database although that's less of a reason
2) presumably the original poster is stuffing in the exact values of
the table name, field name and values themselves. For example they
might have one of three or fourtables . Then the code has those exact
table names included.
wrote:
>>>I am trying to write an Access query where I choose the tablename the
>>>results will be output to.
>>
>>Why not create a SQL string with all the relevant portions of the
>>query and execute it directly.
>
> Ah, the possibility of an SQL injection attack?
Because
>>>I am trying to write an Access query where I choose the tablename the
>>>results will be output to.
>>
>>Why not create a SQL string with all the relevant portions of the
>>query and execute it directly.
>
> Ah, the possibility of an SQL injection attack?
1) presumably this is within Access and thus semi trusted employees
are running the database although that's less of a reason
2) presumably the original poster is stuffing in the exact values of
the table name, field name and values themselves. For example they
might have one of three or fourtables . Then the code has those exact
table names included.
Bob Barrows
May 24, 2012, 7:08:34 AM5/24/12
to
Tony Toews wrote:
> On Tue, 22 May 2012 07:36:02 -0700 (PDT), colmkav
> <colm...@yahoo.co.uk> wrote:
>
>> I am trying to write an Access query where I choose the tablename the
>> results will be output to.
>
> Why not create a SQL string with all the relevant portions of the
> query and execute it directly.
>
> strSQL = "INSERT INTO <tablename> ( <fieldname>, ... )" & _
> "VALUES (<value>, ...);"
> currentdb.Execute strSQL, dbFailOnErrors
>
> Much easier to work with and debug.
>
Putting aside the issue that parameters cannot be used for the OP's task, I
> On Tue, 22 May 2012 07:36:02 -0700 (PDT), colmkav
> <colm...@yahoo.co.uk> wrote:
>
>> I am trying to write an Access query where I choose the tablename the
>> results will be output to.
>
> Why not create a SQL string with all the relevant portions of the
> query and execute it directly.
>
> strSQL = "INSERT INTO <tablename> ( <fieldname>, ... )" & _
> "VALUES (<value>, ...);"
> currentdb.Execute strSQL, dbFailOnErrors
>
> Much easier to work with and debug.
>
disagree that dynamic sql is easier to work with and debug than using
parameterized queries. Using parameters relieves the developer from the
odious task of making sure values are properly delimited as well as the more
odious task of escaping special characters in strings to force them to be
treated literally.
But of course, it comes down to developer style and what the developer is
used to doing ... and where he is doing it. With an external application
using ADO to access a Jet database backend, given the choice of
strSQL = "INSERT INTO <tablename> ( <fieldname>, ... )" & _
"VALUES (<value>, ...);"
vs.
cn.savedquery parm1, parm2, parm3
I will definitely choose the latter.
0 new messages