SQL vs. VBA
in [Queries]
|
Prev: greatest value
Next: Query?? or something else?
From: Sarah on 25 Jan 2010 20:20 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
From: John Spencer on 25 Jan 2010 21:13 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 >
From: Tom van Stiphout on 25 Jan 2010 22:57 On Mon, 25 Jan 2010 17:20:04 -0800, Sarah <Sarah(a)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
From: Allen Browne on 25 Jan 2010 23:43 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(a)discussions.microsoft.com> wrote in message news:BB6ED13D-C3D9-4BE6-996F-EC6D12CBDDB6(a)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 >
From: Jellifish on 26 Jan 2010 13:34
> 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. |