Passing a table rrecordset to SQL server

Good afternoon experts!

I have encountered a very different type of problem than usual. In the past I have pass a single line to the server via 'Pass through Query' and at times when I need to pass more than a single record, I utilise the loop function to send the data to the server multiple times. However if I have over 40 lines of record that loop will take a considerable amount of time to complete. I am just wondering if there is a way to send a table set to the server in 1 move instead of X number of moves using loop.

This is the code I am using on Access side attached to a button within the form (recordsource is a local access table):

Dim db As dao.Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set rs = Me.Recordset

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
Set qdf = db.QueryDefs("Qry_Send_ClientData") 'local pass through query

strSQL = "EXEC dbo.SP_Client_Referral @JunctionID='" & Me.ClientID & "', @Note='" & Replace(Me.Txt_Note, "'", "") & "', @Value1='" & Txt_Value1 & "', @Value2='" & Txt_Value2 & "'"

qdf.SQL = strSQL
db.Execute "Qry_Send_ClientData"

rs.MoveNext
Loop

Msgbox "All Client Added!", , "Add client"

Now on the SQL server side I have the following Store Procedure (dbo.SP_Client_Referral) that receives the data from pass through query and insert the line of code onto a specific table

    @ClientID AS NVARCHAR(15),
    @Note As NVARCHAR(500),
    @Value1 As NVARCHAR(50),
    @Value2 As NVARCHAR(50)
AS  

BEGIN

    SET NOCOUNT ON;

        BEGIN
        INSERT INTO dbo.Client_Data(ClientID, Note, Value_1, Value_2)
        SELECT @ClientID, @Note, @Value1, @Value2
        END

END

For single record or even up to maybe 10 Records this method is relatively fast. However as the number of record increases, the amount of time required can be quite long. If there is a way to pass a table (i.e. Access side using SELECT * from LocalTable) to the SQL server as oppose to line by line would definitely save quite a lot of time. Just wondering if this method exists and if so how would I send a table and what must I use on SQL server side in the SP to receive a table record. Alternatively I may have to continue using this single line method and possibly making it more efficient so that it will execute faster.

Many thanks in advance for your assistance!

Solution

Actually, the fastest approach?

Well, it is one that seems VERY counter intuitive, and I could give a VERY long explain as to why. However, try this, you find it runs 10 times or better then what you have now. In fact, it may well be closer to a 100x then what you have.

We shall assume that we have a standard linked tale to dbo.Client_Data. Likely the link is Client_Data, or even dbo_Cliet_Data.

So, use this:

Dim rs      As DAO.Recordset
Dim rsIns   As DAO.Recordset

If Me.Dirty = True Then Me.Dirty = False  ' write any pending data

Set rsIns = CurrentDb.OpenRecordset("dbo_Client_Data", dbOpenDynaset, dbSeeChanges)

Set rs = Me.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF
   With rsIns
      .AddNew
      !ClientID = rs!ClientID
      !Note = Me.Txt_Note
      !Value_1 = Me.Txt_Value1
      !Value_2 = Me.Txt_Value2
      .Update
   End With

   rs.MoveNext
Loop
rsIns.Close
MsgBox "All Client Added!", , "Add client"

Note a number of bonus in above. Our code is clean - we do NOT have to worry about data types such as dates, or your messy quote's issue. If dates were involved, we again could just assign without having to worry about delimiters. We also get the bonus of injection protection to boot!

We also used me.RecordSetClone. This is not a must do. It will help performance but MOST significant is when you move the record pointer, the form record position does not attempt to follow along. this will get rid of a lot of potential flicker. It can also eliminate HUGE issues if a on-current event exists on that form.

So, while a VERY good idea (recordsetclone), it not the main reason for the huge performance increase you will see here. RecordSetClone is a the same as me.RecordSet, but you can "move" and traverse the recordset without the main form following.

So, really, the most "basic" code approach, and one that would work say on access without SQL Server turns out to be the best approach. It less code, less messy code, and will have saved you all the trouble to setup + build a SQL Server stored procedure. All your concepts were not necessary, and worse they will cause a performance penalty. Try the above concept.

Access will bulk up and manage the multiple inserts as one go. The concept and idea that always using SQL update/insert commands as compared to reocrdsets is a REALLY HUGE urban myth that so many access developers fall for. It is not true. What is REALLY the issue is if you can replace a VBA loop of a huge number of say separate executed updates with ONE single SQL update statement, then yes, you are miles ahead (to use one SQL update over some VBA loop).

However, if you have to do multiple operations and each operation is on a single row? Well in place of "many separate" SQL updates, then in this case, (being) the vast majority of cases, then a recordset will run circles around a whole bunch of separate update/insert commands to achieve the same goal. Its not even close, and you get 10x if not 100 times better performance by using the above concepts.