CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Insert recordset into SQL Server Table (VB ADODB)

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.

Upvotes: 1

Views: 8200

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 3

Ricardo A
Ricardo A

Reputation: 1815

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.

Upvotes: 0

Related Questions