Patrick,

The following does not address you specific situation but it does give some ADODB examples using VBA.

The following text and sample code illustrates how to:

   -> open a database connection
   -> use a stored procedure to perform a query
   -> open a recordset using a custom query
   -> open a recordset using a table name
   -> check for an empty recordset
   -> read all records in a recordset
   -> add a record to a recordset
   -> delete a record from a recordset
   -> copy a recordset with headers to a worksheet
   -> close a recordset and database

This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as follows:

   Value = MyRecordset!Field1
   MyRecordset!Field2 = Value + 1

When the current record is changed use the Update Recordset method to apply the changes to the database. Use the Add method to add a new record and teh Delete method to delete the current record.

Before writing any ADODB code the following references need to be referenced in the VBA project (Tools->References):

   Microsoft ActiveX Data Objects x.x Library
   Microsoft ActiveX Data Objects Recordset x.x Library

For additional information on the ADODB interface see the MSDN pages at:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

Sample code:

   Dim MyDatabase As ADODB.Connection
   Dim MyCommand As New ADODB.Command
   Dim MyRecordset As New ADODB.RecordSet
   Dim Column As Long

   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   and navigate to Open Method (ADO Connection)
   '   Additional help constucting connection strings can be found at http://www.connectionstrings.com/

   ' Query database using stored procedure (requires command object)
   Set MyCommand.ActiveConnection = MyDatabase
   MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
   MyCommand.CommandType = adCmdStoredProc
   With MyCommand
      .Parameters.Refresh
      .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, "Value")
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
      ' For more information about CreateParameter syntax:
      '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
      '   and navigate to CreateParameter Method
   End With
   
   ' Open recordset using command onject
   MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
   ' For more information about Open method syntax:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   and navigate to Open Method (ADO Recordset)
   
   ' Build custom query using command object
   With MyCommand
      Set .ActiveConnection = Database
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

   ' Open recordset by specifying specific table (no query)
   MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
   
   ' Open recordset by specifying query without using command object
   MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

   ' Test for no records
   If MyRecordset.BOF And MyRecordset.EOF Then
      MsgBox "No records in table"
   End If

   ' Determine total records
   MsgBox "Total records: " & MyRecordset.RecordCount
   
   ' Look at all records in record set
   While Not MyRecordset.EOF
      MsgBox "Record number: " & MyRecordset.AbsolutePosition
      MyRecordset.MoveNext
   Wend
   
   ' Update current record
   MyRecordset!Field1 = "Some data"
   MyRecordset!Field2 = "Some more data"
   MyRecordset.Update

   ' Move specific fields from current record to worksheet
   With Sheets("Sheet1")
      Cells(Row, "A") = MyRecordset!Field1
      Cells(Row, "B") = MyRecordset!Field2
   End With
   
   ' Add new record and set field values
   MyRecordset.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Create headers and move data
   With Sheets("Sheet1")
      For Column = 0 To MyRecordset.Fields.Count - 1
         .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
      Next
      .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
     .Cells(2, 1).CopyFromRecordset MyRecordset
   End With

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin