VBA查询、新增SQL数据库
天数无常 2018-11-05 10:33:12 我现在需要的是如何向SQL数据库批量新增、修改、删除数据。请举简单的例子。
本人初学,略懂一二,经过整理网上各种零碎信息,现总结如下两点,欢迎大家加Q:493196216一起讨论。
1,通过VBA查询SQL数据库数据:
Sub 查询()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim CnStr As String, SQL As String
CnStr = "Provider=SQLOLEDB;Persist Security Info=True;User ID=sa;Password=111111;Initial Catalog=DB_22"
cnn.Open CnStr
SQL = "select a.os_id,a.os_no,a.cus_no,b.name from mf_pos a left join cust b on a.cus_no=b.cus_no"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn
With Sheet9
.Range("a1").CopyFromRecordset rs
.Cells.EntireColumn.AutoFit
End With
rs.Close
End Sub
2,通过VBA向SQL插入单元格数据:
Sub EXCEL写入SQL2008()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim CnStr As String, SQL As String
Dim bgz As String
bgz = Sheet11.[a1].Value
CnStr = "Provider=SQLOLEDB;Persist Security Info=True;User ID=sa;Password=111111;Initial Catalog=DB_A"
cnn.Open CnStr
SQL = "select * from A1 where x='" & bgz & "'"
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
If rs.BOF And rs.EOF Then'当记录集为空时
rs.AddNew'记录集添加新记录
rs.Fields(1) = bgz'为新记录赋值
rs.Update'更新记录集
End If
rs.Close
End Sub