【VBA】【ADO】【OLE DBドライバ】SQL Serverへアクセスする

前提
ポイント
サンプルプログラム
実行結果
備考

前提

１.SQL Serverのバージョンは2017

２.SQL Serverの認証方法は「Windows 認証」を指定する

３.「ADO」と「OLE DBドライバ」を使用してDBへアクセスする

４.以下のテーブルが作成済み

５．上記テーブルへSelect文を実行する

ポイント

１.接続文字列に「Integrated Security=SSPI;」を記載することにより「Windows 認証」となる

２.SQL Server の最新バージョンの新機能を利用できるように、「ADO」と
　「SQL Server Native Client(OLE DBドライバ)」を使用してDBアクセスする

３.「SQL Server Native Client」は「OLE DBドライバ」であり「ODBCドライバ」でもある

４.接続文字列のプロバイダで指定するのは「SQLNCLI11」でなく「MSOLEDBSQL」。
　「SQLNCLI11」は非推奨のため

５.新しいデータ型を使用できるよう、接続文字列に「DataTypeCompatibility=80」を記載

※参考
https://docs.microsoft.com/ja-jp/sql/relational-databases/native-client/applications/using-ado-with-sql-server-native-client?view=sql-server-ver15

https://docs.microsoft.com/ja-jp/sql/relational-databases/native-client/sql-server-native-client?view=sql-server-ver15

https://docs.microsoft.com/ja-jp/sql/connect/oledb/applications/using-ado-with-oledb-driver-for-sql-server?view=sql-server-ver15

サンプルプログラム

Option Explicit

Sub ADOSample()
    
    '===============================
    '接続文字列
    '===============================
    'プロバイダ
    'Const PROVIDER As String = "SQLNCLI11"
    Const PROVIDER As String = "MSOLEDBSQL"
    
    'SQL Serverをインストールしたコンピューター名
    Const DB_SERVER As String = "DESKTOP-TO07N7Q"

    'DB名
    Const DB_NAME As String = "testDB"
    
    'ログインユーザ名とパスワード ※Windows 認証の場合は不要
    'Const USER_NAME As String = "sampleUser"
    'Const PASSWORD As String = "password"
    
    '===============================
    'カーソルと読み取り専用の設定
    '===============================
    Const AD_OPEN_KEY_SET As Integer = 1
    Const AD_LOCK_READ_ONLY As Integer = 1
    
    '===============================
    'テーブルの列
    '===============================
    Const ID_COLUMN As Integer = 0
    Const NAME_COLUMN As Integer = 1
    Const BARTHDAY_COLUMN As Integer = 2
    Const GENDER_COLUMN As Integer = 3
    
    Dim con As Object
    Dim conStr As String
    
    Dim strSQL As String
    Dim recordset As Object
    Dim recordStr As String
    
    '接続文字列の組み立て
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source='" & DB_SERVER & "';" & _
              "Initial Catalog='" & DB_NAME & "';" & _
              "Integrated Security=SSPI;" & _
              "DataTypeCompatibility=80;"
    
    'オープン
    Set con = CreateObject("ADODB.Connection")
    con.Open conStr

    '実行するSelect文を設定
    strSQL = "SELECT Id, Name, Birthday, Gender FROM Employee"
    
    'Select文を実行し結果を取得
    Set recordset = CreateObject("ADODB.Recordset")
    recordset.Open strSQL, con, AD_OPEN_KEY_SET, AD_LOCK_READ_ONLY
    
    'レコード分繰り返し
    Do Until recordset.EOF
        ' 取得した1レコードをカンマ区切りでString型の変数に設定
        recordStr = recordset.Fields(ID_COLUMN).Value & "," & _
                    recordset.Fields(NAME_COLUMN).Value & "," & _
                    recordset.Fields(BARTHDAY_COLUMN).Value & "," & _
                    recordset.Fields(GENDER_COLUMN).Value

        ' 取得した1レコードをMsgBoxで出力      
        MsgBox recordStr
        
        ' 次のレコードへ進む
        recordset.MoveNext
    Loop
    
    '後片付け
    recordset.Close
    con.Close
    Set recordset = Nothing
    Set con = Nothing
    
End Sub

実行結果

備考

ODBCドライバを使用してSQL Serverへアクセスする方法は、以下の記事を参照。