1

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

Erik A
  • 31,639
  • 12
  • 42
  • 67
Johnny Bigoode
  • 578
  • 10
  • 31

2 Answers2

3

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • +1 Viva set based operations. This is definitely much better than looping. My Access fu is weak, didn't even know it had procs. – squillman Dec 01 '11 at 14:24
2

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.

squillman
  • 13,363
  • 3
  • 41
  • 60
  • SQL Injection? Like the ones described here : http://en.wikipedia.org/wiki/SQL_injection ? – Johnny Bigoode Nov 29 '11 at 18:52
  • #FAIL! I'm using MS Access 95 - the command connMyDbConnection did not work =( – Johnny Bigoode Nov 29 '11 at 20:04
  • Sorry, force of habit (I'm a SQL Server guy). Fixed the SELECT to use the properly formatted date filter for Access. I'm assuming that's the error. If it still doesn't work can you please post the error? – squillman Nov 29 '11 at 20:24
  • It gives me a 'non-defined variable' for the expression 'connMyDbConnection' – Johnny Bigoode Nov 29 '11 at 20:27
  • 1
    Well, you need to use the connection variable that you're already using in your code. You have to have a connection somewhere (ADODB.CONNECTION object). The one in my example is just that. It's showing you that you need to call the Execute() method on your connection object. Somewhere in your code you'll a line like `set [somevariablename] = CreateObject("ADODB.CONNECTION")`. That `[somevariablename]` is what you want to replace `connMyDbConnection` with. – squillman Nov 29 '11 at 20:31
  • This is how I'm declaring my database. I'm really sorry for sounding retarded but... I don't get where I'm supposed to add the 'connMyDbConnection' `Dim mdb As Database Dim rstSwift As Recordset Set mdb = CurrentDb Set rstSwift = mdb.OpenRecordset("Tbl_Swift")` – Johnny Bigoode Nov 29 '11 at 20:37
  • I'm still having problems... you see I live in Brazil. When I say today is 11/29/2011 I will write it down as 29/11/2011. I'm pretty sure that when I ask it to filter #05/11/2011# It's trying to find all records of May 11th and not November 5th Any suggestions? – Johnny Bigoode Nov 29 '11 at 21:43
  • Access uses the format of the locale you set through the Control Panel. Have a look at this link: http://allenbrowne.com/ser-36.html For your particular locale use `#11/05/2011#` in your query to find May 11. If you need it to be dynamic then you'll need to do some inspection of the date format. – squillman Nov 29 '11 at 21:47
  • Man, I just wanted to say thanks again. My old code was taking about 58 seconds to run tru the entire database with those old loops. Now? NOW? IT TAKES 2 - NUMBER TWO - SECONDS. TWO SECONDS. It's incredible. – Johnny Bigoode Dec 01 '11 at 14:01
  • Awesome :) Have a look at @onedaywhen's answer. It's better. Set based operations are a better way to go. – squillman Dec 01 '11 at 14:24