Binding Data to Controls with MS Access and ADO - Code to retrieve the recordset
(Page 3 of 6 )
The following code is placed in a module that is added to the application. The AdoRst() procedure will provide a publicly visible recordset, rst. Since the CursorType is adOpenKeySet, the recordset may be moved up and down its range. The sample database is from Access 2003.
Option Compare Database
Public strsql As String
Public cnn As New ADODB.Connection
Public rst As New ADODB.Recordset
Public Sub AdoRst()
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select * from Shippers"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
End Sub
In an earlier tutorial it was mentioned that the Access form has a new property called the Recordset property. This is not a design time property, and therefore you do not see it in the picture shown above. You may, however, look for it in the object browser, where you will find it. Now all that is needed is to assign the form's control source property to the rst recordset. Then for each of the text boxes, the control source is the name of the Recordset's field items (0 based). The commented out message box would return true, since the recordset can be moved both ways.
Private Sub Form_Load()
Call AdoRst
Set Me.Recordset = rst
'MsgBox ("Can you move to a previous record: " & rst.Supports
(adMovePrevious))
Me.Text0.ControlSource = rst.Fields.Item(0).Name
Me.Text2.ControlSource = rst.Fields.Item(1).Name
Me.Text4.ControlSource = rst.Fields.Item(2).Name
Set rst = Nothing
Set cnn = Nothing
End Sub
A header is added to the form and appropriate labels are inserted for the text boxes, as shown in the displayed form in the next picture.
Next: Populating a combo box >>
More ASP.NET Articles
More By Jayaram Krishnaswamy
/ 11 
