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…
Advertisement
Answer
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.