1

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?

Improve this question
3
  • 1
    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 stackoverflow.com/questions/44958471/… Also your code readability will benefit from some suitable variables/ utility functions. Commented Jul 9, 2018 at 14:57
  • @TimWilliams Can you please provide an example of batch update? Thanks Commented Jul 9, 2018 at 17:03
  • It's there in my link. Commented Jul 9, 2018 at 17:12
Related questions
4
MS Excel For Each Loop: Insert Rows
0
Insert a range of numbers in one cell
0
How to insert data by using VBA in excel
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.