Passing a recordset to SQL

[madmax262]

I have the following VB script which enters the 1st record from aa Access record set into a SQL table via a stored procedure.

The script works a treat but I want it to enter all the records from the record set. To do this I added in the 3 commands which are prefixed with ***, however when I run the code it errors stating that the stored procedure has too many arguments?

Can anyone assist me with this?

Dim MyDb As DAO.Database
Dim cmd As New ADODB.Command
Dim tmpItem_No As Long

Set MyDb = CurrentDb
Set rsFrom = MyDb.OpenRecordset( _
"SELECT * FROM T_Sub_Sales_Transactions WHERE [Local_Tran_No] = " & Forms![F_Sales_Transactions_UP]![Local_Tran_No] & "", dbOpenSnapshot)

Set cmd = New ADODB.Command

With cmd

    .ActiveConnection = "DRIVER={SQL Server};" & "Server=123.456.789.012;DATABASE=My_DB;UID=ABCD;PWD=1234;"

    .CommandType = adCmdStoredProc
    .CommandText = "dbo.INS_Sub_Sales_Transaction"
    
    ***Do Until rsFrom.EOF
    
        .Parameters.Append cmd.CreateParameter("@Tran_No", adInteger, adParamInput, 0, Me.Tran_No)
        .Parameters.Append cmd.CreateParameter("@Item_No", adInteger, adParamInput, 0, rsFrom!Item_No)
        .Parameters.Append cmd.CreateParameter("@Tran_Type_No", adInteger, adParamInput, 0, rsFrom!Tran_Type_No)
        .Parameters.Append cmd.CreateParameter("@Entered_IP_Address", adVarChar, adParamInput, 50, rsFrom!Entered_IP_Address)
    
        ***rsFrom.MoveNext
        
        .Execute
        
    ***Loop
 
End With

rsFrom.Close

Set rsFrom = Nothing
Set cmd = Nothing

[TheSmileyCoder]

The second time the loop executes, the parameters are added again, so the second time the execution has 8 parameters instead of 4.

[madmax262]

Thanks for this, but I can't see what I need to change in the script to make it work?

[TheSmileyCoder]

I have not worked enough with adodb to give you the answer, but I presume either modify the existing parameteres, or delete and re-append

[jforbes]

I have a feeling that you need to put the ADO loop outside the With cmd. This is a complete guess:

Dim MyDb As DAO.Database
 Dim cmd As New ADODB.Command
 Dim tmpItem_No As Long
 
 Set MyDb = CurrentDb
 Set rsFrom = MyDb.OpenRecordset( _
 "SELECT * FROM T_Sub_Sales_Transactions WHERE [Local_Tran_No] = " & Forms![F_Sales_Transactions_UP]![Local_Tran_No] & "", dbOpenSnapshot)
 
 ***Do Until rsFrom.EOF

 Set cmd = New ADODB.Command
 
 With cmd
 
     .ActiveConnection = "DRIVER={SQL Server};" & "Server=123.456.789.012;DATABASE=My_DB;UID=ABCD;PWD=1234;"
 
     .CommandType = adCmdStoredProc
     .CommandText = "dbo.INS_Sub_Sales_Transaction"
 

         .Parameters.Append cmd.CreateParameter("@Tran_No", adInteger, adParamInput, 0, Me.Tran_No)
         .Parameters.Append cmd.CreateParameter("@Item_No", adInteger, adParamInput, 0, rsFrom!Item_No)
         .Parameters.Append cmd.CreateParameter("@Tran_Type_No", adInteger, adParamInput, 0, rsFrom!Tran_Type_No)
         .Parameters.Append cmd.CreateParameter("@Entered_IP_Address", adVarChar, adParamInput, 50, rsFrom!Entered_IP_Address)
 
         .Execute
  
 End With

***Set cmd = Nothing

***rsFrom.MoveNext
***Loop
  
 
 rsFrom.Close
 
 Set rsFrom = Nothing
 Set cmd = Nothing

[ADezii]

I agree with jforbes in that I think you sequence is backwards. Try the following (UNTESTED):

'*********************************** CODE INTENTIONALLY OMITTED ***********************************
With cmd
  .ActiveConnection = "DRIVER={SQL Server};" & "Server=123.456.789.012;" & _
                      "DATABASE=My_DB;UID=ABCD;PWD=1234;"
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.INS_Sub_Sales_Transaction"
End With

With cmd
  Do Until rsFrom.EOF
    .Parameters.Append cmd.CreateParameter("@Tran_No", adInteger, adParamInput, 0, Me.Tran_No)
    .Parameters.Append cmd.CreateParameter("@Item_No", adInteger, adParamInput, 0, rsFrom!Item_No)
    .Parameters.Append cmd.CreateParameter("@Tran_Type_No", adInteger, adParamInput, _
                                            0, rsFrom!Tran_Type_No)
    .Parameters.Append cmd.CreateParameter("@Entered_IP_Address", adVarChar, adParamInput, _
                                            50, rsFrom!Entered_IP_Address)
      .Execute     'Execute Stored Procedure for each Record in rsFrom
    
    rsFrom.MoveNext
  Loop
End With
'*********************************** CODE INTENTIONALLY OMITTED ***********************************

[madmax262]

No, I tried this and got the exact same result.

Can anyone else assist me with this one?

[ADezii]

For each iteration of the Recordset, prior to passing the Parameters to the Stored Procedure, try either Refreshing or Deleting the Parameters Collection (UNTESTED),

With cmd
  .ActiveConnection = "DRIVER={SQL Server};" & "Server=123.456.789.012;DATABASE=My_DB;UID=ABCD;PWD=1234;"
  .CommandType = adCmdStoredProc
  .CommandText = "dbo.INS_Sub_Sales_Transaction"
  
  .Parameters.Refresh
  'OR
  .Parameters.Delete
  
  .Parameters.Append cmd.CreateParameter("@Tran_No", adInteger, adParamInput, 0, Me.Tran_No)
  .Parameters.Append cmd.CreateParameter("@Item_No", adInteger, adParamInput, 0, rsFrom!Item_No)
  .Parameters.Append cmd.CreateParameter("@Tran_Type_No", adInteger, adParamInput, 0, rsFrom!Tran_Type_No)
  .Parameters.Append cmd.CreateParameter("@Entered_IP_Address", adVarChar, adParamInput, 50, rsFrom!Entered_IP_Address)
    .Execute
End With

[madmax262]

This makes sense but .Delete comes up with "Argument not optional" and .Parameters.Ref resh doesn't appear to do anything no matter where I put it?

If I add MsgBox .Parameters.Cou nt the number increases by the number Parameters each loop.

[ADezii]

1. Sorry about that, you actually need an Index into the Parameters Collection to indicted which Parameter to Delete.
2. BEFORE you attempt that approach:
   1. Remove Code Line #9 (Original Post) and relocate it within the Recordset Looping Structure.
      

      Set cmd = New ADODO.Command   'remove or Comment

   2. Code Line relocation (Line# 7):
      

      With cmd
        .ActiveConnection = "DRIVER={SQL Server};" & "Server=123.456.789.012;DATABASE=My_DB;UID=ABCD;PWD=1234;"
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.INS_Sub_Sales_Transaction"
        
           Do Until rsFrom.EOF
             Set cmd = New ADODB.Command
               .Parameters.Append cmd.CreateParameter("@Tran_No", adInteger, adParamInput, 0, Me.Tran_No)
               .Parameters.Append cmd.CreateParameter("@Item_No", adInteger, adParamInput, 0, rsFrom!Item_No)
               .Parameters.Append cmd.CreateParameter("@Tran_Type_No", adInteger, adParamInput, 0, rsFrom!Tran_Type_No)
               .Parameters.Append cmd.CreateParameter("@Entered_IP_Address", adVarChar, adParamInput, 50, rsFrom!Entered_IP_Address)
                  rsFrom.MoveNext
                    .Execute
           Loop
      End With

3. By relocating the Instantiating of the Command Object within
   

   Do Until rsFrom.EOF
     '......
   Loop

4. you will effectively Reset the Command Object and Clear its Parameters Collection.
5. This is all THEORY and hasn't actually been tested, good luck.

[madmax262]

I tried this and got the same error message?