excel-vba
Excel VBAのSQL - ベストプラクティス

VBAでADODB.Connectionを使用するには？

要件：

プロジェクトへの次の参照を追加します。

• Microsoft ActiveXデータオブジェクト2.8ライブラリ

• Microsoft ActiveXデータオブジェクトRecordset 2.8ライブラリ

変数を宣言する

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

接続を作成する

a。 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

b。 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 Phone：（503）555-9831

ShipperID：2会社名：United Package電話番号：（503）555-3199

ShipperID：3 CompanyName：連邦配送電話番号：（503）555-9931

ShipperID：4 CompanyName：スピーディーな発送電話：（503）555-1234