Loading recordset at runtime

[BruceM]

I am working on loading a recordset at runtime, and modifying the recordset
as needed. The default recordset will typically return about 10 records out
of a few hundred or thousand. The default recordset consists of records in
which a FinalApproval (Number) field is Null. A modified recordset may
consist of all records, final approval completed or not, for a department
(one of the fields in the recordset) within a certain date range, to use one
example.

I can write a query to which I apply criteria as needed. For instance, in
the form's Load event:

Dim strSQL as String

strSQL = "SELECT * FROM qryMain WHERE [FinalApproval] Is Null"

Me.Recordsource = strSQL


When I want to see all records for the Accounting department:

Dim strSQL as String

strSQL = "SELECT * FROM qryMain WHERE [Dept] = 'Accounting' "

Me.Recordsource = strSQL


The other choice would be to build the recordset in VBA rather than using a
named query:

strSQL = "SELECT [Dept], [StartDate], [EndDate], [FinalApproval] FROM
tblMain "
strSQL = strSQL & "WHERE [FinalApproval] Is Null"

This is greatly simplified. The actual SQL would be in the neighborhood of
500 - 1000 characters, depending on the parameters.

I can build the SQL string in VBA. The question is whether there is are
arguments one way or the other about using a named query rather than
building the string from constants, user choices (about Department, dates,
etc.), and so forth.

 

[Albert D. Kallal]

Actually, neither approach is needed.

You have a forms "where" clause that is designed to solve your exact
problem. Simply open the form and pass the where clause.

eg:

docmd.Openform "formName",,,"FinalApproval Is Null"

and

docmd.Openform "formName",,,"Dept = 'Accounting'"

So the where clause is a nice feature that often will save having to build
the sql string in code.

The above equally applies to reports also.

 

[BruceM]

Thanks for the reply, Albert. I guess I didn't make it clear that in some
cases the recordset will be modified after the form is opened. After
opening with the default recordset it may be necessary to look for all
Accounting records from Jan. 1 to the present, then to look for all
Maintenance records for the past two years, and so forth.

I do use the Where condition for reports.

 

[Albert D. Kallal]

Thanks for the reply, Albert. I guess I didn't make it clear that in some
cases the recordset will be modified after the form is opened. After
opening with the default recordset it may be necessary to look for all
Accounting records from Jan. 1 to the present, then to look for all
Maintenance records for the past two years, and so forth.

I do use the Where condition for reports.

Ah, ok, then use the forms "filter" option. The same text/examples I gave
will work for the filter.

eg:

me.Filter = "FinalApproval Is Null"
me.FilterOn = True

In fact your question kind of nicely points out when to use "where" clause
(at form/report open time),and when to use the filter option (ie: after the
form or report that is already open). We often see questions here as when it
better to use one or the other feature. "Where" clause ONLY works on the
open form/report..but the filter option continue to functions AFTER the
form/report is opened.

However, my personal preference is to build the sql string in code and just
stuff it into the forms record source. It not clear as to what your question
is in regards to using a query vs that of the base table?

In both cases (table/query) we are taking about using code to add additional
criteria to that sql, so you might as well use the base table since I see no
advantages to using a query (the only exception here is if the "base" sql
was very complex and messy. Then I do save it as a query and in code use the
"text" property of the query object to pull out the sql and then add-on my
sql.

In fact taking sql and modifying it (adding criteria) is better from a
performance point of view. Using sql in-line means the query optimizer is
forced to re-compile that string each time. This resulting in BETTER
performance then a query with parameters. (note that in your question I
don't believe you were asking about using a query with parameters, but I am
just giving an performance tip that it better to avoid a saved query with
parameters as compared to in-line sql in vba code).

Since you don't know what parameters you going to be using ahead of time,
then a query with parameters makes no sense here (unless you want to build
2-3 different queries). So, therefore using a query in place of the base
table also yields you nothing I can think of here.

So, either use the above filter suggestion (it designed for forms ALREADY
open), or just use the base table + criteria in an sql string. There is
little if any advantage I can see to using a query here.

 

[BruceM]

Thanks! I'm trying to get away from loading the entire recordset and
filtering it. As the databases grow there is a lot of historical data that
need to be viewed only rarely (and almost never all at once), so I would
rather not load it. It's not a big deal yet, but there is a discernible
difference in loading time for the entire recordset and the default 10
records or so, and it won't get better as the number of records increases.

The main thrust of my question was to whether it is better to use a named
query or build the SQL string in VBA. The query is not especially messy, so
I will go with your suggestion about building the string in SQL when
practical. I have built filter strings and SQL strings in VBA (based on
something Allen Browne has on his web site for multi-select list boxes, as I
recall), so no problem there. Truth be told, I like building strings for
such purposes. It typically starts with a base SELECT statement, with the
customization being mostly in the WHERE statement and maybe ORDER BY.

You have provided exactly the information I was seeking, and I have come to
regard you as a very reliable source, so thanks for taking the time to look
over my question.