VBA - ADODB.Connection - Using parameters and retrieving records affected count

Question

Using MS Access to complete this project.

I am attempting to simplify my ADODB code by removing the need for the ADODB.Command object. There are two requirements, the need to use parameters and the need to retrieve records affected (for verification that the SQL executed properly).

The syntax I am attempting to use was mentioned in an article documented in the code block.

{connection object}.[{name of query}] {parameter 1, ..., parameter n [, record set object]}

cn.[TEST_ADODB_Connection] 204, Date & " " & Time(), rs

Sub TEST_ADODB_Connection()

'https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx
'Using ADODB without the use of .Command

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim lngRecordsAffected As Long

Set cn = CurrentProject.Connection

'TEST_ADODB_Connection Query
'INSERT INTO tbl_Log ( LogID_Orig, LogMessage )
'SELECT [NewLogID] AS _LogID, [NewLogMessage] AS _LogMessage;

Set rs = New ADODB.Recordset
cn.[TEST_ADODB_Connection] 204, Date & " " & Time(), rs
lngRecordsAffected = rs.RecordCount 'Error 3704 - no records returned
                                    'so this is expected, but how do we 
                                    'get records affected by the update query?

Debug.Print lngRecordsAffected 

End Sub

UPDATE

Including the original code attempting to be simplified.

The .Command object does provide the functionality I desire, but I am looking for an alternative method if it is feasible.

The article (https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx) provides an example where the .Connection object could be executed using parameters. I am trying to extend that example and obtain records affected.

Sub TEST_ADODB_Command()

Dim cm As ADODB.Command
Dim rs As ADODB.Recordset

Dim iLogID_Auto As Integer
Dim strLogMessage As String

Dim lngRecordsAffected As Long

Set cm = New ADODB.Command

iLogID_Auto = 204
strLogMessage = Date & " " & Time

With cm
    Set .ActiveConnection = CurrentProject.Connection
    .CommandText = "TEST_ADODB_Connection"
    .CommandType = adCmdStoredProc
    .NamedParameters = True ' does not work in access

    .Parameters.Append .CreateParameter("[NewLogID]", adInteger, adParamInput, , iLogID_Auto)
    .Parameters.Append .CreateParameter("[NewLogMessage]", adVarChar, adParamInput, 2147483647, strLogMessage)

    Set rs = .Execute(lngRecordsAffected)

    Debug.Print lngRecordsAffected
End With

Set rs = Nothing
Set cm = Nothing

End Sub

Answer 1

Thank you for the comments. I believe I have devised what I was searching for.

Two points

• ADODB.Command is needed if you want to insert/update and retrieve a record count using parameters using a single .Execute. Examples of this can be found all over the internet including my original post under the update section.

• ADODB.Command is NOT needed if you have an insert/update query and a select query. I could not find examples of this method. Below is an example I have come up with.

High level overview of what is going on

• Execute the insert/update query. Inserts/Updates will not return a recordSet using the one line method.
• Execute a select query. This will return a recordSet, however, I couldn't get the .Count method to work as I would think it should.

• tlemaster's suggested link provided a work around in the answer section. The work around is to revise the select query to group the results and use the COUNT(*) to return the count. The returning value is then utilized instead of the .Count method.

  Sub TEST_ADODB_Connection()
  'https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx
  'Using ADODB without the use of .Command and .Parameters
  
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim lngRecordsAffected As Long
  
  Dim strDateTime As String
  Dim lngID As Long
  
  Set cn = CurrentProject.Connection
  strDateTime = Date & " " & Time()
  lngID = 204 'random number for example purpose
  
  'TEST_ADODB_Connection INSERT Query
  'INSERT INTO tbl_Log ( LogID_Orig, LogMessage )
  'SELECT [NewLogID] AS _NewLogID, [NewLogMessage] AS _LogMessage;
  
  'This line will execute the query with the given parameters
  'NOTE: Be sure to have the parameters in the correct order
  cn.[TEST_ADODB_Connection] lngID, strDateTime
  
  'TEST_ADODB_Select
  'SELECT Count(tbl_Log.LogID_Orig) AS recordCount
  'FROM tbl_Log
  'WHERE tbl_Log.LogID_Orig=[_LogID] AND tbl_Log.LogMessage=[_LogMessage];
  
  'Must initilize recordset object
  Set rs = New ADODB.Recordset
  
  'This line will execute the query with given parameters and store
  'the returning records into the recordset object (rs)
  'NOTE: Again, be sure the parameters are in the correct order
  'NOTE: the recordset object is always the last argument
  cn.[TEST_ADODB_Select] lngID, strDateTime, rs
  
  'unable to directly utilize the .Count method of recordset
  'workaround and more optimal solution is to write the SQL
  'to return a count using grouping and Count(*) - see SQL above
  lngRecordsAffected = rs("recordCount").Value
  
  'Close recordset object
  rs.Close
  
  Debug.Print lngRecordsAffected 
  End Sub