How can I improve my Classic VBA coding using SQL? I'm making a mess with loops

Question

Well... I am a n00b, I know that. And this is pretty straight foward.

Basically this is the code I've been writing.

rstSwift.MoveFirst
Do While Not rstSwift.EOF
    If Left(rstSwift!Date, 2) & Mid(rstSwift!Date, 4, 2) & Right(rstSwift!Date, 4) = fromdate Then
        INSERTFUNCTIONHERE
    Else
    End If
    rstSwift.MoveNext
Loop

I recently made a question about SQL and it seems that it does have a lot of solutions to my problems... you see, my current database has around +15000 entries - and everytime I need to do a query about all the entries related to a single day it takes a while - after all, I'm asking it to do a LOOP across the entire database.

Now, It has come to my attention that I might be a little dumb to be doing so - well, that was the only way I could figure out to do it - my main question is:

Can I substitute that 'do while not' for a SQL query? Like

NEWTABLEVARIABLE = SELECT * from rstSwift WHERE rstSwift.Date = '20/11/2011';
NEWTABLEVARIABLE.MoveFirst
Do While Not NEWTABLEVARIABLE.EOF
INSERTFUNCTIONHERE
NEWTABLEVARIABLE.MoveNext
Loop

And on the results of this query, run the code? Would it be faster than my current code? Even if it's not faster - is it slower? Cause it seems much easier to write that single select argument than having to deal with the While-Loop situation...

Answer 1

Yes, you can get the filtering done on the database side. You would pass the query you specified in when you open the recordset. Something like this:

set rstSwift = connMyDbConnection.Execute("SELECT * from rstSwift WHERE rstSwift.Date = #20/11/2011#")
Do While Not rstSwift.EOF
   INSERTFUNCTIONHERE
   rstSwift.MoveNext
Loop

EDIT:
When using the Database.OpenRecordset() method use this syntax instead of the above:

set rstSwift = mdb.OpenRecordset("SELECT * from rstSwift WHERE rstSwift.Date = #20/11/2011#")
Do While Not rstSwift.EOF
   INSERTFUNCTIONHERE
   rstSwift.MoveNext
Loop

It would potentially be faster than what you are currently using, depending on how many records match the filter.

A WORD OF CAUTION:
The example I provide can be prone to SQL Injection attacks if you modify the query to be created dynamically based on some kind of input. ALWAYS make sure you're cleansing your SQL before running it.

Answer 2

The aim should be to remove the looping entirely.

Say you INSERTFUNCTIONHERE increments the column data_col by 1: instead of:

rstSwift.MoveFirst
Do While Not rstSwift.EOF
    If Left(rstSwift!swift_date, 2) & _
          Mid(rstSwift!swift_date, 4, 2) & _
          Right(rstSwift!swift_date, 4) = fromdate Then
        rstSwift!data_col = rstSwift!data_col + 1
        rstSwift.Update
    End If
    rstSwift.MoveNext
Loop

Instead, re-write the entire operation in SQL e.g.

CREATE PROCEDURE IncrementSwifts
(
 :fromdate DATETIME
)
AS
UPDATE Swifts
   SET data_col = data_col + 1
 WHERE swift_date = :fromdate;

Then in VBA, use a Command object to execute the proc, using a Parameter object to pass in the fromdate value.