Insert recordset into SQL Server Table (VB ADODB)

Question

I have a table on Excel, range A1:Sn (where n is the LastRow). Previously I used to to loop in each row and insert it one by one.

This works fine, and I can resort back to it, but I am looking to insert the entire recordset ("A1:S" & LastRow) into SQL Table, rather than looping row by row.

The reason for this is if I am inserting as a whole recordset will be treated as 1x operation, and therefore will make generating a receipt id for multiple users significantly easier.

Code

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
dim i as long
dim LastRow as long
LastRow = Sheets("Project_Name").Cells(Rows.count, "B").End(xlUp).row

con.Open "Provider=SQLOLEDB; Data Source=LO1WPFSASDB001 ; Initial Catalog=database; User ID=username; Password=password; Trusted_Connection=no"
rs.Open "SELECT * from table;", con, adOpenKeyset, adLockOptimistic

   With rs
for i = 1 to LastRow
  .addnew
  !somevalue = range("A1:S" & LastRow)
  .update
next
.Close
End With
con.Close
Set con = Nothing
Set rs = Nothing

I cannot seem to get it to work. I would appreciate your input.

You can add to the recordset, I think, and then update once you've made your updates. — Nathan_Sav
– Nathan_Sav, Commented Jul 12, 2018 at 15:32
Consider using MS Access to store data in tables for bulk append queries to SQL Server. — Parfait
– Parfait, Commented Jul 12, 2018 at 16:49

Dy.Lee · Accepted Answer · 2018-07-12 15:59:02Z

It seems that you need to change the loop structure.

Dim con As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim strConn As String
Dim i As Long, j As Integer
Dim LastRow As Long
Dim vDB

Set con = New ADODB.Connection
Set Rs = New ADODB.Recordset


LastRow = Sheets("Project_Name").Cells(Rows.Count, "B").End(xlUp).Row
vDB = Sheets("Project_Name").Range("A1:S" & LastRow)

strConn = "Provider=SQLOLEDB; Data Source=LO1WPFSASDB001 ; Initial Catalog=database; User ID=username; Password=password; Trusted_Connection=no"

    With Rs
        .ActiveConnection = strConn
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open
        For i = 1 To UBound(vDB, 1)
            .AddNew
            For j = 1 To UBound(vDB, 2)
                .Fields(j) = vDB(i, j)
            Next j
            .Update
        Next i
    End With
   With Rs
End With

Set Rs = Nothing

Ricardo A · Accepted Answer · 2018-07-12 15:59:00Z

You still need to loop the data on excel and insert/add it.

Following your current code. The second loop is to insert the columns.

for i = 1 to LastRow
    .addnew
    For n = 0 To .Fields.Count - 1
        .Fields(n).Value = Cells(i, n + 1)
    Next n
    .update
next

I would take a different approach to avoid the second loop. Instead of using .addnew I would loop the data in excel, create the INSERT Strings and do an .Execute "INSERT ..." instead. You can skip the rs.Open using this method, just opening the connection and executing on it is fine.

for i = 1 to LastRow
    sqlString = "INSERT INTO TableName (Field1, Field2, Field3, Field4...) VALUES (Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4)...)"
    con.Execute(sqlString)
next

Edit: Using the insert method, you must enclose text values with ' marks otherwise the INSERT statement will return an invalid value type.

Collectives™ on Stack Overflow

Insert recordset into SQL Server Table (VB ADODB)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related