SQL vs. VBA

[Sarah]

I am learning (slowly) how and where to use VBA and SQL. I've noticed that
some jobs can be done using either. For example, I can have a button with
'ON CLICK' code including the SQL fragment:

UPDATE tblData SET Description = "Not Available" ,

but I can do the same thing with a Sub containing a code fragment like:

With rstData
Do While Not .EOF
!Description = "Not Available"
.Update
.MoveNext
Loop
End With

It seems like there are probably many jobs that can be done with VBA alone
or by involving SQL. Are there guidelines out there as to when it might be
best to adopt one approach over the other?

thanks in advance
Sarah

 

[John Spencer]

The personal guideline is use SQL when you are doing multiple records. It is
more efficient and tends to keep the database from bloating.

Use VBA when I cannot do it with SQL or when I am working with one or very few
records and it is difficult to do it with an SQL statement.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom van Stiphout]

On Mon, 25 Jan 2010 17:20:04 -0800, Sarah

I agree with John.
Also consider that in your example the UPDATE statement is a very
efficient statement which database engines can execute very quickly,
even with many records. Compare that with your VBA code which runs
linearly slower with more records.

-Tom.
Microsoft Access MVP

 

[Allen Browne]

Sarah, that's a good question, so I'll chip in too as another voice
supporting John and Tom.

In general, anything you can do straightforwardly with a DML query/SQL
statement will be more efficient than looping records in VBA. Often the VBA
event procedure will just execute the SQL string. Personally I find it
better to put the SQL string in the VBA rather than use a saved query.
Consequently I find myself using this little utility quite often to bring a
SQL statement into VBA code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

The SQL standard also includes DDL (Data Definition Language), for
manipulating the data schema (creating/modifying/deleting
tables/fields/indexes/constraints.) This is too restricted in Access (JET)
to be much use, e.g. you can't set some important field properties this way.
Consequently, you'll find it more useful to use DAO to manipulate or
enumerate TableDefs, Fields, Indexes, Relations, and their properties. (In
practice DML is 99% of the SQL needed in a normalized database at runtime
anyway.)

If you are interested in manipulating the schema (not merely Select or
Action queries), this link leads to examples of DAO, ADO, ADOX, and DDL SQL:
http://allenbrowne.com/tips.html#Examples by Library

 

[Jellifish]

Consequently I find myself using this little utility quite often to bring

a SQL statement into VBA code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

I use that quite a lot too, thanks Allen.

 

[David W. Fenton]

Are there guidelines out there as to when it might be
best to adopt one approach over the other?

The simplest guideline is this:

If you're making exactly the same change to all the records, a SQL
UPDATE will definitely be faster.

If you're making a change based on logic that is specific to each
row but that draws all of its criteria from the row that's being
updated, a SQL UPDATE will almost always be faster.

If you're doing your update based on information drawn from another
table or from other records in the same table, it depends. I'd
always try SQL first before attempting to code it sequentially.