2017年7月19日水曜日 VBA

ADOでSQLServerへ接続 (OLEDB使用)

※ODBC設定必要なし。Microsoft ActiveX DataAccess Objectsの参照設定が必要

Function test2()
 
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim tmp As Variant
 
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    With cnn
        .Provider = "SQLOLEDB"
        .Properties("Data Source").Value = "DB01\ISADB"
        .Properties("Initial Catalog").Value = "UsersAdmin"
        .Properties("Integrated Security").Value = "SSPI"
        .Open
    End With
    
    With cmd
        .ActiveConnection = cnn
        .CommandText = "SELECT * FROM dbo.Users;"
        Set rst = .Execute
        If Not rst.EOF Then
            '文字列データ格納 (全データをカンマ区切りで)
            tmp = rst.GetString(adClipString, , ",", vbNewLine)
            Debug.Print tmp
        End If
    End With
    
    '終了処理
    Set rst = Nothing: Set cmd = Nothing
    cnn.Close: Set cnn = Nothing
 
End Function