How to use ADO in MS Access / Visual Basic

Just a quick example of data access code that uses ADO to get a recordset. Typically this type of code ports well from Access to VB and vice versa. It also nearly ports to script files, remove the variable types and you’re good to script.

Private Sub SetSomeData()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim param As ADODB.Parameter
Set param = New ADODB.Parameter
param.NAME = “theColumn_Id”
param.value = “Some Value”
param.TYPE = adVarChar
param.Size = 40
param.Direction = adParamInput

Set cn = CurrentProject.AccessConnection

Set cmd = New ADODB.Command
With cmd
Call .Parameters.Append(param)
.ActiveConnection = cn
.CommandText = “q_GetSomeData” ‘uses a MS Access query
.CommandType = adCmdTable
Set rs = .Execute()
End With

If rs.RecordCount > 0 Then
tSomeData = GetSomeDataFromTheRecordset(rs)
Else
tSomeData = “”
End If
Call rs.Close
End Sub

This is what the Access query looks like:
q_GetSomeData:
PARAMETERS theColumn_ID Text ( 255 );
SELECT Column1, Column2
FROM my_Table
WHERE Column_ID = theColumn_ID