Running a SELECT Query from VBA

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

While walking through a sequence of queries in which I am writing the SQL
via code, I have a couple of SELECT queries that need to be refreshed along
the way. I prefer to not have to reproduce the SQL in code when there are no
parameter changes based on a form.

Execute doesn't work on a SELECT query. How can I do this in DAO?
 
Dkline said:
While walking through a sequence of queries in which I am writing the
SQL via code, I have a couple of SELECT queries that need to be
refreshed along the way. I prefer to not have to reproduce the SQL in
code when there are no parameter changes based on a form.

Execute doesn't work on a SELECT query. How can I do this in DAO?
Click to expand...

I'm not sure what you mean by "refreshed along the way", but the way to
"run" a select query with DAO is to open a recordset on the query. For
example:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT MyField FROM MyTable")
' or you could open with the name of a stored query or a tale"
' Set rs = db.OpenRecordset("MyStoredQuery")
' Set rs = db.OpenRecordset("MyTable")

' ... here do something with the recordset and its fields ...

rs.Close
set rs = Nothing
set db = Nothing
 
Thanks for the reply.

The "refreshed" queries are getting input from a form which launches to
code. The form is used to provide various dates to set criteria in several
of the SELECT queries. I run these in a sequence as two of them are query of
queries.
 
Dkline said:
Thanks for the reply.

The "refreshed" queries are getting input from a form which launches
to code. The form is used to provide various dates to set criteria in
several of the SELECT queries. I run these in a sequence as two of
them are query of queries.
Click to expand...

If you have recordsets open on these queries, you can requery the
recordset objects; e.g.,

rs.Requery

Is that what you're looking for?
 
Yep - that's all I need to do on some of these. So, if I understand this
correctly, I open the query as a recordset, requery, and close it. Thus I
avoid writing the SQL each time which is unnecessary.
 
Dkline said:
Yep - that's all I need to do on some of these. So, if I understand
this correctly, I open the query as a recordset, requery, and close
it. Thus I avoid writing the SQL each time which is unnecessary.
Click to expand...

I don't know if I can confirm your conclusions, because I have only a
very hazy idea of what you're actually doing. Bear in mind that, if
you're dealing with stored queries that have been opened as datasheets
in the user interface, opening and requerying a recordset is not going
to affect those datasheets. So if *that's* what's going on, you'll have
to take a different approach. But if you are working with recordsets,
then requerying the recordset should be all you need.
 
Here is an example. Currently I am building this query:

Function BuildSQLString_CumulativePremiumSummary(ByRef strSQL As String) As
Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strGROUPBY As String

strSELECT = "SELECT [qry_cumulative premium].PolicyNumber, "
strSELECT = strSELECT & "Sum([qry_cumulative premium].TransactionAmount)
AS SumOfTransactionAmount "

strFROM = "FROM [qry_cumulative premium] "

strGROUPBY = "GROUP BY [qry_cumulative premium].PolicyNumber;"

strSQL = strSELECT & strFROM & strGROUPBY

BuildSQLString_CumulativePremiumSummary = True
End Function


The above function is being called here:

'qry_cumulative premium summary

strCurrentQuery = "qry_cumulative premium summary"
If Not BuildSQLString_CumulativePremiumSummary(strSQL) Then
MsgBox "Problem building SQL string in " & strCurrentQuery
Exit Sub
End If

'run the query
CurrentDb.QueryDefs("qry_cumulative premium summary").SQL = strSQL
SysCmd acSysCmdUpdateMeter, 5



Since there are no variables to be taken from a User Form, I'm trying to
avoid having code rewrite a query that doesn't need any changes i.e. passing
a date from the form as a parameter query.

But as this one is a query of queries, I believe I have to refresh it after
the underlying queries are run. BUT since this one is in turn called by the
next and final query, then can I assume that that query will cause this
query of queries to refresh itself?
 
Dkline said:
Here is an example. Currently I am building this query:

Function BuildSQLString_CumulativePremiumSummary(ByRef strSQL As
String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strGROUPBY As String

strSELECT = "SELECT [qry_cumulative premium].PolicyNumber, "
strSELECT = strSELECT & "Sum([qry_cumulative
premium].TransactionAmount) AS SumOfTransactionAmount "

strFROM = "FROM [qry_cumulative premium] "

strGROUPBY = "GROUP BY [qry_cumulative premium].PolicyNumber;"

strSQL = strSELECT & strFROM & strGROUPBY

BuildSQLString_CumulativePremiumSummary = True
End Function


The above function is being called here:

'qry_cumulative premium summary

strCurrentQuery = "qry_cumulative premium summary"
If Not BuildSQLString_CumulativePremiumSummary(strSQL) Then
MsgBox "Problem building SQL string in " & strCurrentQuery
Exit Sub
End If

'run the query
CurrentDb.QueryDefs("qry_cumulative premium summary").SQL = strSQL
SysCmd acSysCmdUpdateMeter, 5



Since there are no variables to be taken from a User Form, I'm trying
to avoid having code rewrite a query that doesn't need any changes
i.e. passing a date from the form as a parameter query.

But as this one is a query of queries, I believe I have to refresh it
after the underlying queries are run. BUT since this one is in turn
called by the next and final query, then can I assume that that query
will cause this query of queries to refresh itself?
Click to expand...

I have a feeling you may be laboring under a misconception, or at least
an unusual use of the term "run" with respect to a query. Please bear
with me if my feeling turns out to be wrong. I note your comment in the
following code:
'run the query
CurrentDb.QueryDefs("qry_cumulative premium summary").SQL = strSQL
Click to expand...

That statement does *not* run the query. It just modifies its SQL.
It's not at all clear to me that you need to modify its SQL at all,
since in this code, at least, the SQL string you assign to it is static.
If that's the same SQL as was already in that query ("qry_cumulative
premium summary"), then there's no reason nor need to reassign it.

You also say:
But as this one is a query of queries, I believe I have to refresh it
after the underlying queries are run.
Click to expand...

If I understand what you are saying, your belief is incorrect. As long
as [qry_cumulative premium] exists and has SQL that selects fields named
PolicyNumber and TransactionAmount, there is no need to "refresh"
[qry_cumulative premium summary].

When you talk about running the underlying queries, is that "running"
also a matter of updating their SQL, or are you opening them as
datasheets, opening recordsets on them, or executing them as action
queries? If all you did with the underlying queries was modify their
SQL, or if you did nothing with their SQL, then there can be no
refreshing to be done, because there are no query results held in
memory.

Let me give you an example. Suppose I have queries qryA, qryB, and qryC
defined in my database, as shown on the Queries tab of the database
window. Suppose that qryA takes a parameter, [Enter Policy No], used as
a criterion in a WHERE clause to select records for a particular policy
number. Now suppose that qryB extracts and maybe summarizes data from
qryA, and that qryC extracts and summarizes data from qryB. So we have
dependencies like this:

qryA
|
+-----> qryB

|
+-----> qryC

(maybe that diagram lined up for you, and maybe it didn't.)

Now, if I open a datasheet on qryC, Access will prompt me for the
parameter needed by qryA: [Enter Policy No]. That's because it needs
records from qryB, which needs records from qryA, which needs the
parameter. If I enter policy number 1234 in response to the prompt, I
will see the appropriate results for that policy number. I do *not*
have to open qryA, then open qryB, and then open qryC. The database
engine takes care of the business of figuring out which records from
what tables need to be extracted, and how to crunch them to get the
results needed by qryC.

If I close the datasheet on qryC and open it again, I'll get the
parameter prompt all over again. If I now enter policy number 5678, the
results I get from qryC will reflect the fact that qryA was filtered by
that policy number. I didn't have to refresh anything, just run qryC
again.

Or suppose I open a qryA as a datasheet and specify policy number 1234
at the parameter prompt. If I now open qryC as a datasheet, it will
*still* prompt me for a policy number, and I can enter 5678 and get the
results from that policy number. It doesn't matter that I already had
qryA open with different results, because qryC is still going to call
qryB, and qryB is still going to call qryA *all over again*.

That's why I say that I don't think you need to refresh anything, or
rebuild the SQL of [qry_cumulative premium summary]. I could be wrong
about what you're trying to do, though; does my explanation tell you
anything you didn't already know?
 
Your explanation was thorough and much appreciated. I conclude that the only
queries I need to "run" from the VBA code are those that take data from the
form. Three dates are entered on that form to determine final balance date,
and a start and an end date. So only in those do I rewrite the SQL to
include the appropriate form entry.

Example:
strWHERE = "WHERE [tbl_premium ledger].TransactionDate <=#" & datEndDate
& "# "

All the rest will take care of themselves by virtue of the db software - in
this case Access.

Thank you for your insight.


Dirk Goldgar said:
Dkline said:
Here is an example. Currently I am building this query:

Function BuildSQLString_CumulativePremiumSummary(ByRef strSQL As
String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strGROUPBY As String

strSELECT = "SELECT [qry_cumulative premium].PolicyNumber, "
strSELECT = strSELECT & "Sum([qry_cumulative
premium].TransactionAmount) AS SumOfTransactionAmount "

strFROM = "FROM [qry_cumulative premium] "

strGROUPBY = "GROUP BY [qry_cumulative premium].PolicyNumber;"

strSQL = strSELECT & strFROM & strGROUPBY

BuildSQLString_CumulativePremiumSummary = True
End Function


The above function is being called here:

'qry_cumulative premium summary

strCurrentQuery = "qry_cumulative premium summary"
If Not BuildSQLString_CumulativePremiumSummary(strSQL) Then
MsgBox "Problem building SQL string in " & strCurrentQuery
Exit Sub
End If

'run the query
CurrentDb.QueryDefs("qry_cumulative premium summary").SQL = strSQL
SysCmd acSysCmdUpdateMeter, 5



Since there are no variables to be taken from a User Form, I'm trying
to avoid having code rewrite a query that doesn't need any changes
i.e. passing a date from the form as a parameter query.

But as this one is a query of queries, I believe I have to refresh it
after the underlying queries are run. BUT since this one is in turn
called by the next and final query, then can I assume that that query
will cause this query of queries to refresh itself?
Click to expand...

I have a feeling you may be laboring under a misconception, or at least
an unusual use of the term "run" with respect to a query. Please bear
with me if my feeling turns out to be wrong. I note your comment in the
following code:
'run the query
CurrentDb.QueryDefs("qry_cumulative premium summary").SQL = strSQL
Click to expand...

That statement does *not* run the query. It just modifies its SQL.
It's not at all clear to me that you need to modify its SQL at all,
since in this code, at least, the SQL string you assign to it is static.
If that's the same SQL as was already in that query ("qry_cumulative
premium summary"), then there's no reason nor need to reassign it.

You also say:
But as this one is a query of queries, I believe I have to refresh it
after the underlying queries are run.
Click to expand...

If I understand what you are saying, your belief is incorrect. As long
as [qry_cumulative premium] exists and has SQL that selects fields named
PolicyNumber and TransactionAmount, there is no need to "refresh"
[qry_cumulative premium summary].

When you talk about running the underlying queries, is that "running"
also a matter of updating their SQL, or are you opening them as
datasheets, opening recordsets on them, or executing them as action
queries? If all you did with the underlying queries was modify their
SQL, or if you did nothing with their SQL, then there can be no
refreshing to be done, because there are no query results held in
memory.

Let me give you an example. Suppose I have queries qryA, qryB, and qryC
defined in my database, as shown on the Queries tab of the database
window. Suppose that qryA takes a parameter, [Enter Policy No], used as
a criterion in a WHERE clause to select records for a particular policy
number. Now suppose that qryB extracts and maybe summarizes data from
qryA, and that qryC extracts and summarizes data from qryB. So we have
dependencies like this:

qryA
|
+-----> qryB

|
+-----> qryC

(maybe that diagram lined up for you, and maybe it didn't.)

Now, if I open a datasheet on qryC, Access will prompt me for the
parameter needed by qryA: [Enter Policy No]. That's because it needs
records from qryB, which needs records from qryA, which needs the
parameter. If I enter policy number 1234 in response to the prompt, I
will see the appropriate results for that policy number. I do *not*
have to open qryA, then open qryB, and then open qryC. The database
engine takes care of the business of figuring out which records from
what tables need to be extracted, and how to crunch them to get the
results needed by qryC.

If I close the datasheet on qryC and open it again, I'll get the
parameter prompt all over again. If I now enter policy number 5678, the
results I get from qryC will reflect the fact that qryA was filtered by
that policy number. I didn't have to refresh anything, just run qryC
again.

Or suppose I open a qryA as a datasheet and specify policy number 1234
at the parameter prompt. If I now open qryC as a datasheet, it will
*still* prompt me for a policy number, and I can enter 5678 and get the
results from that policy number. It doesn't matter that I already had
qryA open with different results, because qryC is still going to call
qryB, and qryB is still going to call qryA *all over again*.

That's why I say that I don't think you need to refresh anything, or
rebuild the SQL of [qry_cumulative premium summary]. I could be wrong
about what you're trying to do, though; does my explanation tell you
anything you didn't already know?

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

(please reply to the newsgroup)
Click to expand...
 
Dkline said:
Your explanation was thorough and much appreciated. I conclude that
the only queries I need to "run" from the VBA code are those that
take data from the form. Three dates are entered on that form to
determine final balance date, and a start and an end date. So only in
those do I rewrite the SQL to include the appropriate form entry.

Example:
strWHERE = "WHERE [tbl_premium ledger].TransactionDate <=#" &
datEndDate & "# "

All the rest will take care of themselves by virtue of the db
software - in this case Access.
Click to expand...

That sounds right. Although ... are you aware that you can use explicit
references to controls on forms, in your queries? So long as the form
is open when the query is actually executed, the references, which are
seen as parameters, can be evaluated at run time, sparing you the need
to rewrite the query's SQL on the fly. For example, your WHERE clause
as posted above could conceivably be framed like this:

WHERE [tbl_premium ledger].TransactionDate <=
[Forms]![YourFormName]![datEndDate]

(where "YourFormName" is the name of the form and "datEndDate" is the
name of a control on that form).

When you open a form, report, or datasheet on a stored query with such a
reference, Access automatically resolves the form reference, provided
that the form in question is open.

If you use DAO to open a recordset on that query, on the other hand, you
have to resolve such parameters yourself, by way of the Parameters
collection of the QueryDef object; for example:

'----- start of example code -----
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qd = db.QueryDefs("YourQueryName")

For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

' ... do stuff with rs ...

rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
'----- end of example code -----

I don't know if this helps you, but it sounds as though you may be
making changes to the SQL of stored queries when you don't have to. Of
course, changing the SQL on the fly is always an option.
 
Back
Top