sql parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Query below prompts for variables, then executes properly. The variables are already defined, and I need the Query t
execute those values. When the query works properly, I'll be passing the parameters to the procedure, but that's a different issue

What am I missing

Thanks
Mik

Public Sub CStores(

Dim c, n, sq As Strin

c = "KINGS
n = "3

sq = "PARAMETERS [c] STRING, [n] STRING;
sq = sq & "INSERT INTO STUDY
sq = sq & "SELECT TOP " & n & " STORE,
sq = sq & "0 AS PANEL FROM DATA
sq = sq & "WHERE COUNTY = '" & c & "';

DoCmd.RunSQL (sq

End Su
 
Remove the line

sq = "PARAMETERS [c] STRING, [n] STRING; "

from your code for now.

In fact, if you're always going to be running your query from code, it's not
really necessary to declare them as parameters so that the query prompts:
you can always prompt for the values yourself from VBA code, using InputBox
(or something more sophisticated if you prefer).

Another comment. You might find that using the Execute method of the
Database object is preferable to using DoCmd.RunSQL for a couple of reasons.

The first is that you won't get the "Access is about to insert n rows"
alert, and the second is that you can specify an optional second parameter
that will raise an error that you can trap for those times when the query
doesn't run properly.

Try something like:

Public Sub CStores()

On Error GoTo Err_CStores

Dim c As String, n As String, sq As String

c = "KINGS"
n = "3"

sq = "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

CurrentDb.Execute sq, dbFailOnError

End_CStores:
Exit Sub

Err_CStores:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_CStores

End Sub

If you don't have a reference set to DAO, use 128 instead of dbFailOnError

By the way, note the change I made to the declaration of variables in your
code. It's necessary to declare the variable type for each variable. What
you had was actually declaring c and n to be variants.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Mike Klein said:
Query below prompts for variables, then executes properly. The variables
Click to expand...
are already defined, and I need the Query to
execute those values. When the query works properly, I'll be passing the
Click to expand...
parameters to the procedure, but that's a different issue.
What am I missing ?

Thanks,
Mike


Public Sub CStores()

Dim c, n, sq As String

c = "KINGS"
n = "3"

sq = "PARAMETERS [c] STRING, [n] STRING; "
sq = sq & "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

DoCmd.RunSQL (sq)

End Sub
Click to expand...
 
I should probably mention that you can't specify n as a parameter in SELECT
TOP " & n & " STORE anyhow, which is another reason not to bother using
parameters!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Douglas J. Steele said:
Remove the line

sq = "PARAMETERS [c] STRING, [n] STRING; "

from your code for now.

In fact, if you're always going to be running your query from code, it's not
really necessary to declare them as parameters so that the query prompts:
you can always prompt for the values yourself from VBA code, using InputBox
(or something more sophisticated if you prefer).

Another comment. You might find that using the Execute method of the
Database object is preferable to using DoCmd.RunSQL for a couple of reasons.

The first is that you won't get the "Access is about to insert n rows"
alert, and the second is that you can specify an optional second parameter
that will raise an error that you can trap for those times when the query
doesn't run properly.

Try something like:

Public Sub CStores()

On Error GoTo Err_CStores

Dim c As String, n As String, sq As String

c = "KINGS"
n = "3"

sq = "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

CurrentDb.Execute sq, dbFailOnError

End_CStores:
Exit Sub

Err_CStores:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_CStores

End Sub

If you don't have a reference set to DAO, use 128 instead of dbFailOnError

By the way, note the change I made to the declaration of variables in your
code. It's necessary to declare the variable type for each variable. What
you had was actually declaring c and n to be variants.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Mike Klein said:
Query below prompts for variables, then executes properly. The
Click to expand...
variables
are already defined, and I need the Query to
execute those values. When the query works properly, I'll be passing
Click to expand...
the
parameters to the procedure, but that's a different issue.
What am I missing ?

Thanks,
Mike


Public Sub CStores()

Dim c, n, sq As String

c = "KINGS"
n = "3"

sq = "PARAMETERS [c] STRING, [n] STRING; "
sq = sq & "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

DoCmd.RunSQL (sq)

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