Search code examples
sqlvbams-accesswhile-loopvb5

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

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

Solution

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