Creating ADO Objects

In order to use ActiveX Data Objects to manipulate data, you need to reference the ActiveX Data Objects Library in your picture's project. Select the References command from the VBE Tools menu and then select the ADO type library. The object variables are declared at the Module level, which means that they are available in all of the other routines in this example. If you don't need to manipulate the records after reading them, then they can be declared at the Procedure level.

Example: Creating an ADO Record set

'General Declarations

Dim conODBC As ADODB.Connection

'This stores the link to the database.

Dim adoRS As ADODB.Recordset

'This stores the results of the query.

 

Private Sub InitADO()

     Dim strQuery As String

     On Error GoTo ErrorHandler

 

     'SQL query that ADO will run.

     strQuery = "SELECT Recipe_ID, Recipe_Name, Batch_Size, " _

     & "Milk_Quan , Choc_Quan, Mix_Time, Milk_Type _

     FROM ACCEPT_TEST & "ORDER _BY _Recipe_Name ASC"

 

     Set conODBC = New ADODB.Connection

     'Create ADO Connection Object.

 

     'Connect to the database. Connect string can be DSN-less.

     'This varies by database type.

     conODBC.Open "driver= _

     & " SQL server};server=thunder;uid=sa;pwd=;database=master"

     Set adoRS = New ADODB.Recordset

     'Create ADO Recordset Object.

 

     'Run the query, and set options to allow read/write access.

     adoRS.Open strQuery, conODBC, adOpenDynamic, _

     adLockPessimistic, adCmdText

     Exit Sub

     ErrorHandler:

          HandleError

End Sub