Bulk Insert Range ADODB Excel Vba

Question

I have a spreadsheet UI where users will input data. I then need to collect data and insert it in SQL Server using ADODB connection.

Currently, my working solution is to loop from i = 7 (where data start) to i = LastRow, and perform an INSERT operation in each loop, followed by marking the row with 'Y' as sent.

This works fine, but I am afraid that this could cause lag or redundancy, especially if a user inputs more than 1000x rows of data. In other words, this would create more than 1000x INSERTS...

My code is:

Pre-loop code
            For i = 7 To LastRow

            'Check if it was sent - then skip iteration
                    With Worksheets("Admin")
                        If .Cells(i, 5).Value = "Y" Then
                            GoTo NextIteration
                        Else
                            Command.CommandText = "INSERT INTO [dbo].[TEP_Payments_Table] ([AA Number], [AA Name], [AA Role], [Project Name], [Series], [Paper No], [Task Name], [Amount], [Payment Justification], [Payment Identifier], [Date of Activity], [Half Day / Full Day], [Teacher Release (Y/N)], [Centre No], [Request Receipt Id], [Request Date], [Requested By], [Business Unit]) VALUES (" & _
"'" & Sheets("Project_Name").Cells(i, 2).Value & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 3).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 4).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Sheets("Project_Name").Cells(i, 5).Value & "'," & _
"'" & Sheets("Project_Name").Cells(i, 6).Value & "'," & _
"'" & Sheets("Project_Name").Cells(i, 7).Value & "'," & _
"'" & Sheets("Project_Name").Cells(i, 8).Value & "'," & _
"'" & Sheets("Project_Name").Cells(i, 9).Value & "'," & _
"'" & "Description: " & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 10).Value, "'", ""), "*", ""), Chr(34), "") & vbNewLine & vbNewLine & _
"  //Project: " & Sheets("Project_Name").Cells(i, 5).Value & vbNewLine & _
"  //Series: " & Sheets("Project_Name").Cells(i, 6).Value & vbNewLine & _
"  //Paper No: " & Sheets("Project_Name").Cells(i, 7).Value & vbNewLine & _
" //Task Name: " & Sheets("Project_Name").Cells(i, 8).Value & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 12).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 13).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 14).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 15).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & Replace(Replace(Replace(Sheets("Project_Name").Cells(i, 16).Value, "'", ""), "*", ""), Chr(34), "") & "'," & _
"'" & .Cells(i, 22).Value & "'," & _
"'" & Format(Now(), "yyyy-MM-dd hh:mm:ss") & "'," & _
"'" & Application.UserName & "'," & _
"'" & .Cells(i, 20).Value & "')"


                                'replace(replace(replace(Sheets("Project_Name").Cells(i, 10).Value,"'",""),"*",""),Chr(34),"")

                            Command.Execute

                            'Mark what was inserted with "Y"
                                .Cells(i, 5).Value = "Y"
                                Sheets("Project_Name").Cells(i, 19).Value = 

"Y"
'''''' After loop code ''''

I wonder if there is a quicker way of doing this rather than looping through each row?

Maybe first try it out with a lot of data and test the performance? It would probably be "safer" to use a recordset and perform a batch update, or to use a parameterised insert statement, to protect against possible user shennanigans if not for extra performance. E.g. see https://stackoverflow.com/questions/44958471/excel-exporting-to-access-via-vba-is-causing-instability/44959630#44959630 Also your code readability will benefit from some suitable variables/ utility functions. — Tim Williams, Jul 09 '18 at 14:57
@TimWilliams Can you please provide an example of batch update? Thanks — Oday Salim, Jul 09 '18 at 17:03

Bulk Insert Range ADODB Excel Vba

0 Answers0