ADO Recordsets

Overview

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

Uses

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
rstTemp.Open
Do While Len(Sheet.Range("A" & r).Formula) > 0
With rstTemp
    .AddNew
    .Fields("FldName") = Sheet.Range("A" & r).Value
    .Fields("FldName2") = Sheet.Range("B" & r).Value
    .Update
End With
r = r + 1
Loop

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