DoCmd.RunSQL

[Guest]

Hello all

I’ve been running into a problem when trying to modify the SQL statements contained in my query. I want to be able to change the number of records returned by my SELECT… FROM statement. To do this, I have written the following code and call it into action by way of a command button on a form

Sub CreateSQL(

DoCmd.OpenQuery “SQL String Queryâ€, acViewNormal, acEdi

Dim strSQL As Strin

strSQL = "SELECT TOP 75 tblExam.Question, tblExam.[Choice A], tblExam.[Choice B], tblExam.[Choice C], tblExam.ID, tblExam.[Choice D], tblExam.[Choice E]" &
" FROM tblExam " &
" ORDER BY Rnd([ID]);
DoCmd.RunSQL strSQ

End Su

I debug the code without any problems, but when I click the command button in the form I get the following error

Run-Time Error 2342: A RunSQL action requires an argument consisting of an SQL statement

This same statement works if I type in manually into the Query’s SQL View, so I’m not sure what I’m doing wrong. Many thanks in advance

 

[Guest]

RunSQL is only to be used for action queries (such as INSERT or UPDATE) that don't return a recordset. SELECT tells Access "give me some records, and I'm going to do something with them"

What are you intending to do with the results of your SELECT statement?

 

[Guest]

I'm using the SELECT statement to generate reports based on user specifications, so in essence I do have to find records and then "do something with them."

I have a series of forms that ask for user preferences for the design of this report, which is actually an exam to be given to students. Among these preferences, the user can set the number of records (i.e. questions) they would like displayed and then ask to randomize those records (hence the ORDER BY statement I provided). I would like to be able to handle this programmatically. Perhaps there is a way of coding the report itself to accept the specifications? I'm not sure. I'm running into a wall on this one... any advice?

 

[Guest]

Disclaimer: I've never done this, but I THINK it can work

If "SQL String Query" is the query that you base your report (i.e., the exam) on, then I think you'll want to use the QueryDefs collection to feed that SQL to the query

This is junk code - you may need to do more than this, but it should get you in the right directio

On Error Resume Nex
DoCmd.DeleteObject acQuery, "SQL String Query
DAO.CreateQueryDef "SQL String Query", strSQ
DoCmd.OpenReport "rptExam


----- Joe L wrote: ----

I'm using the SELECT statement to generate reports based on user specifications, so in essence I do have to find records and then "do something with them."

I have a series of forms that ask for user preferences for the design of this report, which is actually an exam to be given to students. Among these preferences, the user can set the number of records (i.e. questions) they would like displayed and then ask to randomize those records (hence the ORDER BY statement I provided). I would like to be able to handle this programmatically. Perhaps there is a way of coding the report itself to accept the specifications? I'm not sure. I'm running into a wall on this one... any advice?

 

[Roger Carlson]

Here's something a little more complete. It is much the same idea, though:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query
strSQL = "SELECT TOP 75 tblExam.Question, tblExam.[Choice A], " & _
"tblExam.[Choice B], tblExam.[Choice C], tblExam.ID, " & _
"tblExam.[Choice D], tblExam.[Choice E]" & _
" FROM tblExam " & _
" ORDER BY Rnd([ID]);"

'*** delete the previous query
db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

'*** open the query
DoCmd.OpenReport "rptExam"

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

On my website (see sig below) is a small sample database called
"CreateQueries2.mdb" which illustrates this and other scenerios.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Disclaimer: I've never done this, but I THINK it can work.

If "SQL String Query" is the query that you base your report (i.e., the

exam) on, then I think you'll want to use the QueryDefs collection to feed
that SQL to the query.

This is junk code - you may need to do more than this, but it should get you in the right direction

On Error Resume Next
DoCmd.DeleteObject acQuery, "SQL String Query"
DAO.CreateQueryDef "SQL String Query", strSQL
DoCmd.OpenReport "rptExam"


----- Joe L wrote: -----

I'm using the SELECT statement to generate reports based on user

specifications, so in essence I do have to find records and then "do
something with them."

I have a series of forms that ask for user preferences for the design

of this report, which is actually an exam to be given to students. Among
these preferences, the user can set the number of records (i.e. questions)
they would like displayed and then ask to randomize those records (hence the
ORDER BY statement I provided). I would like to be able to handle this
programmatically. Perhaps there is a way of coding the report itself to
accept the specifications? I'm not sure. I'm running into a wall on this
one... any advice?

 

[Guest]

Roger, your a good man. Between MDW and your website (I actually used RandomRecords2K but the code is essentially the same), I was able to find the solution to my problem. I really appreciate the help, guys

----- Roger Carlson wrote: ----

Here's something a little more complete. It is much the same idea, though

Private Sub cmdRunQuery_Click(
On Error GoTo Err_cmdRunQuery_Clic

Dim db As DAO.Databas
Dim qdf As DAO.QueryDe
Dim strSQL As Strin

Set db = CurrentD

'*** create the quer
strSQL = "SELECT TOP 75 tblExam.Question, tblExam.[Choice A], " &
"tblExam.[Choice B], tblExam.[Choice C], tblExam.ID, " &
"tblExam.[Choice D], tblExam.[Choice E]" &
" FROM tblExam " &
" ORDER BY Rnd([ID]);

'*** delete the previous quer
db.QueryDefs.Delete "SQL String Query
Set qdf = db.CreateQueryDef("SQL String Query", strSQL

'*** open the quer
DoCmd.OpenReport "rptExam

Exit_cmdRunQuery_Click
Exit Su

Err_cmdRunQuery_Click
If Err.Number = 3265 Then '*** if the error is the query is missin
Resume Next '*** then skip the delete line and resume o
the next lin
Els
MsgBox Err.Description '*** write out the error and exit th
su
Resume Exit_cmdRunQuery_Clic
End I
End Su

On my website (see sig below) is a small sample database calle
"CreateQueries2.mdb" which illustrates this and other scenerios

--
--Roger Carlso
www.rogersaccesslibrary.co
Reply to: Roger dot Carlson at Spectrum-Health dot Or

Disclaimer: I've never done this, but I THINK it can work

exam) on, then I think you'll want to use the QueryDefs collection to fee
that SQL to the query

DoCmd.DeleteObject acQuery, "SQL String Query
DAO.CreateQueryDef "SQL String Query", strSQ
DoCmd.OpenReport "rptExam

specifications, so in essence I do have to find records and then "d
something with them.of this report, which is actually an exam to be given to students. Amon
these preferences, the user can set the number of records (i.e. questions
they would like displayed and then ask to randomize those records (hence th
ORDER BY statement I provided). I would like to be able to handle thi
programmatically. Perhaps there is a way of coding the report itself t
accept the specifications? I'm not sure. I'm running into a wall on thi
one... any advice

 

[Chris Nebinger]

Roger,

Nice code. I have one question tho.

What's the difference between:

db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

And:

CurrentDB.QueryDefs("SQL StringQuery").SQL = strSQL


I've always used the second, and wasn't sure if you knew
if one was better than the other.


Chris Nebinger

-----Original Message-----
Here's something a little more complete. It is much the same idea, though:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query
strSQL = "SELECT TOP 75 tblExam.Question, tblExam.[Choice A], " & _
"tblExam.[Choice B], tblExam.[Choice C], tblExam.ID, " & _
"tblExam.[Choice D], tblExam.[Choice E]" & _
" FROM tblExam " & _
" ORDER BY Rnd([ID]);"

'*** delete the previous query
db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

'*** open the query
DoCmd.OpenReport "rptExam"

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

On my website (see sig below) is a small sample database called
"CreateQueries2.mdb" which illustrates this and other scenerios.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Disclaimer: I've never done this, but I THINK it can work.

If "SQL String Query" is the query that you base your

report (i.e., the
exam) on, then I think you'll want to use the QueryDefs collection to feed
that SQL to the query.

This is junk code - you may need to do more than this,

but it should get
you in the right direction

On Error Resume Next
DoCmd.DeleteObject acQuery, "SQL String Query"
DAO.CreateQueryDef "SQL String Query", strSQL
DoCmd.OpenReport "rptExam"


----- Joe L wrote: -----

I'm using the SELECT statement to generate reports

based on user
specifications, so in essence I do have to find records and then "do
something with them."

I have a series of forms that ask for user

preferences for the design
of this report, which is actually an exam to be given to students. Among
these preferences, the user can set the number of records (i.e. questions)
they would like displayed and then ask to randomize those records (hence the
ORDER BY statement I provided). I would like to be able to handle this
programmatically. Perhaps there is a way of coding the report itself to
accept the specifications? I'm not sure. I'm running into a wall on this
one... any advice?


.

 

[Roger Carlson]

Well, the second one assumes there is a query called "SQL StringQuery"
already in existence. That may not be the case. It will throw an error if
there is not. In that case, you would have to error trap it and use the
CreateQueryDef method.

I prefer to delete the existing query and recreate it. This also throws an
error if the query doesn't exist, but I can trap it and use the Resume Next
to bypass the delete. Another method I've used is to check for the
existence of the query and use an IF to determine whether to use the
existing query or create the query.

I also like to define a database variable (db) rather than using CurrentDB
directly. While they work identically in most cases, there are a few
instances in which CurrentDB will not work this way, while a database
variable will always work. Also the database variable can be defined once
and used again and again.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Roger,

Nice code. I have one question tho.

What's the difference between:

db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

And:

CurrentDB.QueryDefs("SQL StringQuery").SQL = strSQL


I've always used the second, and wasn't sure if you knew
if one was better than the other.


Chris Nebinger

-----Original Message-----
Here's something a little more complete. It is much the same idea, though:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query
strSQL = "SELECT TOP 75 tblExam.Question, tblExam.[Choice A], " & _
"tblExam.[Choice B], tblExam.[Choice C], tblExam.ID, " & _
"tblExam.[Choice D], tblExam.[Choice E]" & _
" FROM tblExam " & _
" ORDER BY Rnd([ID]);"

'*** delete the previous query
db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

'*** open the query
DoCmd.OpenReport "rptExam"

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

On my website (see sig below) is a small sample database called
"CreateQueries2.mdb" which illustrates this and other scenerios.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Disclaimer: I've never done this, but I THINK it can work.

If "SQL String Query" is the query that you base your

report (i.e., the
exam) on, then I think you'll want to use the QueryDefs collection to feed
that SQL to the query.

This is junk code - you may need to do more than this,

but it should get
you in the right direction

On Error Resume Next
DoCmd.DeleteObject acQuery, "SQL String Query"
DAO.CreateQueryDef "SQL String Query", strSQL
DoCmd.OpenReport "rptExam"


----- Joe L wrote: -----

I'm using the SELECT statement to generate reports

based on user
specifications, so in essence I do have to find records and then "do
something with them."

I have a series of forms that ask for user

preferences for the design
of this report, which is actually an exam to be given to students. Among
these preferences, the user can set the number of records (i.e. questions)
they would like displayed and then ask to randomize those records (hence the
ORDER BY statement I provided). I would like to be able to handle this
programmatically. Perhaps there is a way of coding the report itself to
accept the specifications? I'm not sure. I'm running into a wall on this
one... any advice?


.

 

[Chris Nebinger]

True, however either way you're going to have to trap an
error if the query exists or not. Most of the time when
I'm changing SQL statements inside queries, it's on pass
through queries that I don't want to lose the connection
property.

In 97 environment (and 2000+ w/o Compact on Close)
deleting queries and adding new ones will cause the
database to bloat until a compact is done. I've seen
several production applications that were never
compacted. The last one went from 100 meg to 17 meg.

Anyway, I wasn't sure if there was a fundamental change to
how Access preformed the operations.


Chris Nebinger

-----Original Message-----
Well, the second one assumes there is a query called "SQL StringQuery"
already in existence. That may not be the case. It will throw an error if
there is not. In that case, you would have to error trap it and use the
CreateQueryDef method.

I prefer to delete the existing query and recreate it. This also throws an
error if the query doesn't exist, but I can trap it and use the Resume Next
to bypass the delete. Another method I've used is to check for the
existence of the query and use an IF to determine whether to use the
existing query or create the query.

I also like to define a database variable (db) rather than using CurrentDB
directly. While they work identically in most cases, there are a few
instances in which CurrentDB will not work this way, while a database
variable will always work. Also the database variable can be defined once
and used again and again.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Roger,

Nice code. I have one question tho.

What's the difference between:

db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

And:

CurrentDB.QueryDefs("SQL StringQuery").SQL = strSQL


I've always used the second, and wasn't sure if you knew
if one was better than the other.


Chris Nebinger

-----Original Message-----
Here's something a little more complete. It is much

the
same idea, though:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query
strSQL = "SELECT TOP 75 tblExam.Question, tblExam.

[Choice
A], " & _

"tblExam.[Choice B], tblExam.[Choice

C],
tblExam.ID, " & _

"tblExam.[Choice D], tblExam.[Choice

E]"
& _

" FROM tblExam " & _
" ORDER BY Rnd([ID]);"

'*** delete the previous query
db.QueryDefs.Delete "SQL String Query"
Set qdf = db.CreateQueryDef("SQL String Query", strSQL)

'*** open the query
DoCmd.OpenReport "rptExam"

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is

the
query is missing

Resume Next '*** then skip the

delete
line and resume on

the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

On my website (see sig below) is a small sample

database
called

"CreateQueries2.mdb" which illustrates this and other scenerios.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

news:4A0950F4-F7A6-4C9D-A669- (e-mail address removed)...
Disclaimer: I've never done this, but I THINK it can work.

If "SQL String Query" is the query that you base your report (i.e., the
exam) on, then I think you'll want to use the QueryDefs collection to feed
that SQL to the query.

This is junk code - you may need to do more than

this,
but it should get

you in the right direction

On Error Resume Next
DoCmd.DeleteObject acQuery, "SQL String Query"
DAO.CreateQueryDef "SQL String Query", strSQL
DoCmd.OpenReport "rptExam"


----- Joe L wrote: -----

I'm using the SELECT statement to generate

reports
based on user

specifications, so in essence I do have to find records and then "do
something with them."

I have a series of forms that ask for user preferences for the design
of this report, which is actually an exam to be given

to
students. Among

these preferences, the user can set the number of

records
(i.e. questions)

they would like displayed and then ask to randomize

those
records (hence the

ORDER BY statement I provided). I would like to be

able
to handle this

programmatically. Perhaps there is a way of coding the report itself to
accept the specifications? I'm not sure. I'm running into a wall on this
one... any advice?


.

.