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

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: