Coding Syntax and Methods?

  • Thread starter Thread starter Darby
  • Start date Start date
D

Darby

I am having a HECK uva time trying to do something as simple as setting an
Sql String as a recordset in VBA so that I can move thru it one record at a
time. Can someone please help me...
Here is my sql String
"SELECT COLL.fstname, COLL.surname FROM COLL;"
How do I set this to become a recordset in my code?
Thanks
 
Darby said:
I am having a HECK uva time trying to do something as simple as setting an
Sql String as a recordset in VBA so that I can move thru it one record at a
time. Can someone please help me...
Here is my sql String
"SELECT COLL.fstname, COLL.surname FROM COLL;"
How do I set this to become a recordset in my code?
Click to expand...


Assuming you have a reference set to the DAO (for Access 2003 or earlier) or
ADE (for Access 2007 or later) object library, you would do it like this:

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT COLL.fstname, COLL.surname FROM COLL;"

' Open the recordset.
Set rs = CurrentDb.OpenRecordset(strSQL)

' Do things with the recordset.
With rs

' Loop through all the records.
Do Until .EOF

' ... Here's where you do things with the current record.

' Move to the next record.
.MoveNext

Loop

' Close the recordset.
.Close

End With

' Done with the recordset object.
Set rs = Nothing
 
Just a side thought ...

If you are looking to iterate through your recordset one record at a time,
there's a chance you can use a query to do what you're doing (you didn't
describe that) against the entire recordset at once... depends on your
situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks guys,

Although I am looking to iterate thru a complete recordset, I was looking
for and having trouble with the proper syntax for setting the Recordset in
VBA from a string that makes up a query. Thanks Drik for that code!
Jeff; are you suggesting that I actully create the query and use that to
iterate thru? Is this a "Better" practice? I am going to iterate thru each
record and evaluate fields and an update based on that evaluation. Thanks
for any advice.
 
Darby

Close, but no cigar...

If you 'iterate through', you have to have your code work with each record.
?Big recordset? !Slow progress!

On the other hand, a query performs "set operations", and handles them all
at once, rather than 'iterating through'.

Take a look at Access HELP re: "update query".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Darby,

Depending on what you are doing to "evaluate fields and update based on that
evaluation", you might, as Jeff points out be able to do this as a single
update query. A very simplistic example might be something like:

Upate yourTable
SET [SomeField] = IIF([field1] = "A" and [Field2] = "B", 1, 2)

If the "evaluate" portion is complicated, you might want to create a
function, and pass it the values of the fields you want to evaluate, and have
it return the value that you want to update. Something like:

Update yourTable
SET [SomeField] = fnEvalAndUpdate([Field1], [Field2], [Field3])

This may or may not be quicker than the method provided by Dirk. If you go
this route, the code inside the function would be VERY similar to the code
That you would be putting inside the Do Loop in Dirks code.

Another way to speed this operation up would be to use a WHERE clause in
your SELECT statement, to filter out those records that you don't want to
update.
 
Back
Top