performance: query vs. class module

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

Guest

Hello,

short question:
Are queries or class modules drastically diferent when it comes to the
performance/speed of an access application?


background:
I'm reading Access 2002 desktop developers handbook. I'm looking at class
modules and like them. I can also get my class module to do exactly what I
want where trying to use queries is causing me more problems.

I ask because i'm trying to use a comboBox to change other fields on a form,
including a subform (i'm using the northwinds sample database because the
orders form is very similar to an application i'm trying to create).

I have some experience with C++ and Java, so i understand programming code
more then I do queries, but i would like to create my access application to
be the most effiecent for performance and speed as it can be. I would also
like Access to do as much of the hard "work" as I can make it do.

Thank you,

phill
 
In general, any time you can use a query, it'll be considerably more
efficient than using VBA code to do the same thing.
 
The answer is not really. In other words, using a class object, or using
some sql will make no difference.

And, in fact, your code inside the class object that works with data will
simply use some sql anyway...right? So, where is the advantage going to be
here?

You can write regular code, or use a class object...they both execute at the
same rate.

The above code may, or may not se some sql quires...but that is really a
separate issue. I mean, if your VBA code modifies records, then if you can
use sql in that code you should (and, it is no relevant that the code is in
a class module or not).
I'm reading Access 2002 desktop developers handbook. I'm looking at class
modules and like them. I can also get my class module to do exactly what
I
want where trying to use queries is causing me more problems.
Click to expand...

Hum, the above is not quite clear, since to modify or work with data, you
likely will be using some sql. For example some code to change all
occurrences of the City = "N. Y." to New York would be:

dim strSql as string
dim rstRecs as dao.recordset

strSql = "select * from tblCustomer"
set rstRecs = currentdb.OpenRecordSet(strSql)

do while rstRecs.EOF = false
if rstRecs!City = "N. Y" then
rstRecs.Edit
rstRecs!City = "New York"
rstRecs.Update
end if
rstRecs.MoveNext
loop
rstRecs.Close
set rstRecs = nothing

Of couse, using more sql, and making a query can make this loop more simple.
So, even better then the above, would be:

dim strSql as string
dim rstRecs as dao.recordset

strSql = "select * from tblCustomer where City = 'N. Y.' "

set rstRecs = currentdb.OpenRecordSet(strSql)
do while rstRecs.EOF = false
rstRecs.Edit
rstRecs!City = "New York"
rstRecs.Update
rstrecs.MoveNext
loop
rstRecs.Close
set rstRecs = nothing

And, of couse, using even more sql, we can make a query to do the whole
thing for us:

currentdb.Execute "update tblCustomer set City = 'New York' where City = 'N.
Y.'"

So, now we are all down to one line of code. However, having this code in a
standard code module, or a class object will not effect its performance
here.

So, sure, often your code is going to be a mix of sql and reocrdsets. If you
can use sql to do the update, then it is generally faster. The other
advantage of using sql is that if you change the back end data to sql server
(and keep the front end as ms-access), then defiantly get more performance
gains, or you "can" get more performance gains by making sql do as much work
as possible. So, for looping code vs a sql update is actually quite close in
terms of performance, but if you ever plan to move the back end data to sql
server..then having given sql the performance in your coding habits..the
results will be better when using ms-access with sql server.
..
 
Back
Top