Recordset Question

[h3lm3t]

Hi there, thanx for looking, (my first post)

I'm a bit new to using VBA inside of access, i did the proper thing and bought a book, and thats got me through BUT
some things are missing:

Why should you use a recordset ? - i mean if you can get data out of the tables by referencing them through queries reports etc.

I cant see any reason for making up a set of records that already exist.

I probably have the wrong end of the stick, but please explain of you can.

Thanx in advance, kind regards

h3lm3t

 

[mpastore]

A recordset is an object used within VBA code as a representation of a set of data based either on a single table or a group of related tables joined in a SQL query. You are not really "making" another set of data when using recordsets, you are just viewing the data in an alternate way.

You may want to use a recordset to scan a table in code one row at a time. This is often useful in situations where you cannot perform the same operation *easily* in SQL. For instance, consider a scenario where you want to scan a set of data but may need to move the record pointer back and forth through the file.

Consider a table called "Orders". For simplicity sake, say we enter the type of fruits ordered chronologically by date:

fruit_name order_date
apples 1/1/03
bananas 1/1/03
peaches 1/2/03
bananas 1/3/03
mellons 1/3/03
apples 1/3/03
apricots 1/3/03

Suppose we want to know instances where bananas were purchased right after an apple purchase (don't ask, maybe it's some kind of marketing test).

*************************************
* NOTE, this is pseudo code, I have not tested it
*************************************

sub apples_before_bananas

dim rsOrders as recordset
set rsOrders = currentdb.openrecordset("select * from orders order by order_date&quot

do while not rsOrders.eof
if rsOrders("fruit_name&quot="bananas" and not rsOrders.bof then
rs.moveprevious
if rsOrders("fruit_name&quot="apples" then
debug.print "An apple was purchased before the banana"
rs.movenext ' restore the record pointer
endif
endif
rs.movenext
loop

end sub

Now this could also be done in SQL, but the code would be a little complicated. The use of a recordset allows a more straight-forward approach to solve the problem.

 

[Ed2020]

That's not a particularly easy question to answer. Uses of recordsets are so wide-ranging it would not be possible to give a proper answer within this forum.

Browse this site and others and look at examples of what people have done using recordsets. This will be the best way of answering your question.

You are however correct in saying that most of what can be achieved in a recordset can also be achieved in SQL.

Bear in mind that a recordset is not a copy of a set of records - it is a pointer the the set of records.

Ed Metcalfe.

Please do not feed the trolls.....

 

[h3lm3t]

Thanx very much for the help, i see what you mean now
I also mucked around with some recordsets, and seen their value when i looked in the local and immediate windows.

Thanx again

h3lm3t