Recordsets tutorial (1 Viewer)

[dsigner1]

I have always just "bodged" recordsets because I have only needed the odd one or two and got by finding an example to copy. I now need to actually understand the things properly and can't find a decent in depth tutorial. With ADO and DAO, SQL clauses etc it is really a bit of a mine field. Does anyone know of a reliable source of detailed information on this area?

I imagine there must be other people with the same problem but a search of the forum did not give me anything except examples for specific problems.

 

[gemma-the-husky]

a recordset examines in memory the records avaialble in a query/table

you define the recordset
you open/close the recordset
there may be some memory leakages if these are not done

you can use code to define/locate the active record
eg find, seek methods
movefirst, movelast, movenext etc

you can examine fields in a record

either
somevar = rst!anyfield

or
somevar = rst(anyfield)

anyfield can be a variable, so if you have
name1, name2, name3 etc

you can refer to them by
somevar = rst(name & index)

you can edit a record
rst.edt - this is required first
rst!fieldname = whatever
rst.update - this saves the edit

you test begining and end of file with bof and eof

so this code iterates a record set

dim dbs as database
dim rst as recordset
set dbs=currentdb
set rst = dbs.openrecordset("somequery")

while not rst.eof 'loop until eof is true
   msgbox(rst!somefield)  'show some info about the record
   rst.movenext 'go to the next record
wend
rst.close 'tidy up
set rst=nothing
set dbs= nothing

hope this helps

 

[boblarson]

Give this a try.

 

[dsigner1]

Thanks Bob that is exactly what I had in mind.
Dave - A useful start point but I wanted to know how to deal with all the different types of recordset that are available.
The combination of the two should help any one else who gets confused by these pesky but useful objects.