title: VBA - ADODB.Connection - Using parameters and retrieving records affected count
body: <p>Using MS Access to complete this project.</p>
  
  <p>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).</p>
  
  <p>The syntax I am attempting to use was mentioned in an article documented in the code block.</p>
  
  <p>{connection object}.[{name of query}] {parameter 1, ..., parameter  n [, record set object]}</p>
  
  <p>cn.[TEST_ADODB_Connection] 204, Date &amp; " " &amp; Time(), rs</p>
  
  <pre><code>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 &amp; " " &amp; 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
  </code></pre>
  
  <p>UPDATE</p>
  
  <p>Including the original code attempting to be simplified.  </p>
  
  <p>The .Command object does provide the functionality I desire, but I am looking for an alternative method if it is feasible.  </p>
  
  <p>The article (<a href="https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx" rel="nofollow noreferrer">https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx</a>) provides an example where the .Connection object could be executed using parameters.  I am trying to extend that example and obtain records affected.</p>
  
  <pre><code>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 &amp; " " &amp; 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
  </code></pre>