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

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

Sarah wrote:
> 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
> 

On Mon, 25 Jan 2010 17:20:04 -0800, Sarah
<Sarah@discussions.microsoft.com> wrote:

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


>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

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%20by%20Library

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sarah" <Sarah@discussions.microsoft.com> wrote in message 
news:BB6ED13D-C3D9-4BE6-996F-EC6D12CBDDB6@microsoft.com...
> 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
> 

> 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. 

=?Utf-8?B?U2FyYWg=?= <Sarah@discussions.microsoft.com> wrote in
news:BB6ED13D-C3D9-4BE6-996F-EC6D12CBDDB6@microsoft.com: 

> 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. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/