如何在 VBA 中使用 ADODB.Connection

要求:

添加以下对项目的引用:

  • Microsoft ActiveX 数据对象 2.8 库

  • Microsoft ActiveX 数据对象 Recordset 2.8 库

StackOverflow 文档

声明变量

Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command

创建连接

一个。使用 Windows 身份验证

Private Sub OpenConnection(pServer As String, pCatalog As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI")
    mCmd.ActiveConnection = mDataBase
End Sub

湾使用 SQL Server 身份验证

Private Sub OpenConnection2(pServer As String, pCatalog As String, pUser As String, pPsw As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI;User ID=" & pUser & ";Password=" & pPsw)
    mCmd.ActiveConnection = mDataBase
End Sub

执行 sql 命令

Private Sub ExecuteCmd(sql As String)
    mCmd.CommandText = sql
    Set mRS = mCmd.Execute
End Sub

从记录集中读取数据

Private Sub ReadRS()
    Do While Not (mRS.EOF)
        Debug.Print "ShipperID: " & mRS.Fields("ShipperID").Value & " CompanyName: " & mRS.Fields("CompanyName").Value & " Phone: " & mRS.Fields("Phone").Value
        Call mRS.MoveNext
    Loop
End Sub

关闭连接

Private Sub CloseConnection()
    Call mDataBase.Close
    Set mRS = Nothing
    Set mCmd = Nothing
    Set mDataBase = Nothing
End Sub

如何使用它?

Public Sub Program()
    Call OpenConnection("ServerName", "NORTHWND")
    Call ExecuteCmd("INSERT INTO [NORTHWND].[dbo].[Shippers]([CompanyName],[Phone]) Values ('speedy shipping','(503) 555-1234')")
    Call ExecuteCmd("SELECT * FROM [NORTHWND].[dbo].[Shippers]")
    Call ReadRS
    Call CloseConnection
End Sub

结果

ShipperID:1 公司名称:Speedy Express 电话:(503)555-9831

ShipperID:2 公司名称:United Package Phone:(503)555-3199

ShipperID:3 公司名称:Federal Shipping 电话:(503)555-9931

ShipperID:4 公司名称:快速发货电话:(503)555-1234