|
录入代码有问题,不知道怎么修改了。结果如下图,请大神指教该如何修改(如果按列名列分别指定,代码行数较多,所以想用循环)。
Sub 录入记录()
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim SQL As String
Dim val As String
Set ws = Sheet2 '确定录入数据所在表
'建立与SQL Server数据库的连接
conn.ConnectionString = "Provider=SQLOLEDB;" _
& "Server=192.168.1.162;" _
& "uid=abc;" _
& "pwd=sa;" _
& "Database=cwb"
conn.Open
'向数据表中添加新记录
SQL = "select * from " & Range("a1").Text & ""
Set rs = New ADODB.Recordset
rs.Open SQL, conn, adOpenKeyset, adLockOptimistic
For i = 3 To ws.Range("a50000").End(xlUp).Row
For j = 1 To 10
rs.AddNew
rs.Fields(j - 1) = ws.Cells(i, j)
Next j
Next i
rs.Update
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
MsgBox "成功保存数据!", vbInformation, "scdy 记录": Exit Sub
End Sub
|
|