[AccessD] Stored Procedure return value

Hi John,

If I remember how this discussion started, you just wanted to run some SQL
Server code from Access. After you were able to do so you wanted to know how
many rows were affected.

If this is right, I don't see why you need stored procedures; why not
execute the code directly? Just construct the SQL statement in code (no need
for parameters this way) and then execute it.

I wrote an Access subroutine that does this in my local copy of SQL Server
2008. The code is below. It inserts the records from one table into another
and then deletes the new records (because truncate, while faster, doesn't
return the number of records).

Hope this helps,
Fred

Public Sub RunCommand()
  Dim strCNN As String
  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim intRecords As Integer
  
  ' Setup connection (using 2008, you'll need to change the provider too)
  strCNN = "Provider=SQLNCLI10" & _
          ";Server=FRED-6998B25045" & _
          ";Database=TrapoLocal" & _
          ";Uid=Trapo" & _
          ";Pwd=Trapo"
  Set cnn = New ADODB.Connection
  With cnn
    .ConnectionString = strCNN
    .ConnectionTimeout = 10
    .CursorLocation = adUseClient
    .Open
  End With
  
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cnn
    .CommandText = "insert into test select * from dbo.TempVsPSIG"
    .Execute intRecords
    .CommandText = "delete from test"
    .Execute intRecords
  End With
  
  Set cmd = Nothing
  cnn.Close
  Set cnn = Nothing
End Sub