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
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
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.