ADO Recordsets


ADO recordsets have multiple uses, all the ones I've used them for are listed below, with demo code where applicable:


Hold data from excel

Dim rstTemp as New ADODB.Recordset, r as Integer
r = 1 
rstTemp.Fields.Append "FldName", DataType, [Len]
rstTemp.Fields.Append "FldName2", DataType, [Len]
rstTemp.LockType = adLockOptimistic
Do While Len(Sheet.Range("A" & r).Formula) > 0
With rstTemp
    .Fields("FldName") = Sheet.Range("A" & r).Value
    .Fields("FldName2") = Sheet.Range("B" & r).Value
End With
r = r + 1

The LockType does not really matter, since in this use, the recordset is disconnected from any real data source, however it needs to be set if this recordset is to be used as a source for a control such as a listbox.

Load data from a table

Dim rstTemp as New ADODB.Recordset
rstTemp.Open "TblName", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Use as Recordset for Listbox

Assumes that recordset has been created and filled (remember to set LockType for disconnected recordsets)

Set ListObj.Recordset = rstTemp
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License