vb 查询，连接数据库

最新推荐文章于 2022-07-21 19:48:01 发布

转载最新推荐文章于 2022-07-21 19:48:01 发布 · 743 阅读

本文介绍如何使用VBA代码通过ADODB组件连接到SQL Server数据库，并执行SQL查询将结果填充到Excel工作表中。代码示例展示了设置数据库连接字符串、执行SQL语句及处理查询结果的具体步骤。

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Conn = New ADODB.Connection
On Error GoTo ErrorSub
Conn.ConnectionString = "Driver={SQL Server};Server=192.168.1.1;Database=test;User ID=sa;Password= 123456"
Conn.Open
On Error GoTo 0

Dim sqlcount As String
sqlcount = "select * from test where ID ='" & Sheet1.Range("B2").Value & "'"
On Error GoTo ErrorSub
Set Rs = Conn.Execute(sqlcount)
On Error GoTo 0

Dim i, j As Integer
i = 2
Sheet2.Range("A1").Value = "a"
Sheet2.Range("B1").Value = "b"
Sheet2.Range("C1").Value = "c"

Do Until Rs.EOF
Sheet2.Range("A" & i).Value = Rs.Fields("ID")
Sheet2.Range("B" & i).Value = Rs.Fields("username")
Sheet2.Range("C" & i).Value = Rs.Fields("password")
i = i + 1
Rs.MoveNext
Loop

Sheet2.Columns("A:A").EntireColumn.AutoFit
Sheet2.Columns("B:B").EntireColumn.AutoFit
Sheet2.Columns("C:C").EntireColumn.AutoFit

Set Rs = Nothing

Conn.Close
Exit Sub

ErrorSub:
Call f_ErrorInfo(Conn)
End Sub