Take the SQL Taste Test with Scott  
                

I am not talking about SQLserver, I am talking about SQL - Structured Query Language. The back bone of any good Database developer. Microsoft has provided Access with a very thorough SQL tool set. Most of us know and use a little SQL every day. If you do not regularly use it or never have do this simple test. Take one of your Queries that you have designed or use every day, Open it up in Design mode and then view it in SQL. Now in your VBA code (OR VB code) you can use this SQL command in your forms, commands and reports in Access via the follwowing command.

docmd.runsql "SELECT * FROM tbleName"

Now you can also use a similar command using ADO. We can often tell if an application has been designed and implemented by a Database Professional or a Visual Basic Professional by the way they do things using ADO.

A common piece of code we see that can be replaced by a one liner is making an ADO record set and then moving to the begging of the record set and then setting up a loop where each record is tested for an event and if true then an action is preformed. This could be an update, inserting into a list box, deleting selected records and so on.

Instead of setting up the loop record by record (which can take some

time) the same result can be achieved using ADO and SQL in a single query.

For example the following delete code

Dim rsLGG As New ADODB.Recordset

Dim cnthisconnect As ADODB.Connection

Set cnthisconnect = CurrentProject.Connection

rsLGG.Open "tblLGG", cnthisconnect, adOpenKeyset, _ adLockOptimistic,adCmdTable 
Do Until rsLGG.EOF 
  rsLGG.Delete 
  rsLGG .MoveNext 
Loop 
rsLGG.Close 

set rsLGG = nothing 
set cnthisconnect = nothing

Could be replaced with the following SQL command;

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
  .ActiveConnection = CurrentProject.Connection
  .CommandText = "DELETE tblLGG.* FROM tblLGG "
  .CommandType = adCmdText
  .Execute
End With

Perhaps the number of lines of code are similar, but there are less objects instantiated, less overhead and it is a single operation not 'many' and the actual operation takes less time to execute.

Considering that WHERE statements can be utilised, the power of SQL in ADO is immense.

Consider the following use of a WHERE statement in opening a record set.

rsLGG.Open "SELECT * FROM tblLGG WHERE field1 = '1000' and field2 = 'Australia'", _ 
cnthisconnect, adOpenForwardOnly, adLockReadOnly, adCmdText

This allows you to open smaller record sets which are handy if you then want to run a record by record loop. Or you only want a particular occurrence.

Click on the following button to jump to the next page in the document loop.