时间戳

首页 > 解决方案 > 将记录集插入 SQL Server 表 (VB ADODB)

vba - 将记录集插入 SQL Server 表 (VB ADODB)

问题描述

我在 Excel 上有一个表格，范围为 A1:Sn（其中 n 是 LastRow）。以前我习惯在每一行中循环并一一插入。

这工作正常，我可以求助于它，但我希望将整个记录集("A1:S" & LastRow)插入 SQL 表，而不是逐行循环。

这样做的原因是，如果我作为一个完整的记录集插入将被视为 1x 操作，因此将为多个用户生成收据 ID 会变得更加容易。

代码

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

我似乎无法让它工作。我会很感激你的意见。

标签： vbaexcel

解决方案

看来您需要更改循环结构。

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

推荐阅读