データベース（SQL Server）に接続する（ADO）

2017/09/25

データベース（SQL Server）に接続

ODBC または OLE DB で接続します。ODBC 接続する場合は、ODBCデータソースを作成する必要があります。

接続文字列

ConnectionString（ADO）に設定する文字列

＜ODBC 接続＞
DNS = DataSourceName;
[ UID = UserName;]
[ PWD = Password;]

＜OLE DB 接続＞
Provider = SQLOLEDB;
Data Source = ServerName;
[Ninsyo;]
[ User ID = UserName;]
[ Password = Password;]

引数・戻り値

DataSourceName ・・・ ODBC データソース名を指定します。
UserName ・・・ユーザー名を指定します（Windows認証の場合は不要）。
Password ・・・パスワードを指定します（Windows認証の場合は不要）。
ServerName ・・・サーバーのホスト名、IPアドレス、ネットワーク上のPC名等を指定します。またポートも指定できます。
（例）localhost、127.0.0.1,1433、DesktopPC\SQLEXPRESS
※DesktopPC はネットワーク上に表示されるPC名で、SQLEXPRESS はインスタンス名です。ポートを指定する場合は、「,」のあとに指定します。
Ninsyo ・・・ SQL Server認証の場合は不要です。Windows認証の場合は次のいずれかを指定します。Trusted_connection=yes　または、Integrated Security=SSPI

データベース（SQL Server）接続

サンプルVBAソース

Sub Sample_ADO_SQLServer()


    Dim constr As String

    'OLE DB
    Const MYPROVIDERE = "Provider=SQLOLEDB;"
    Const MYSERVER = "Data Source=localhost,1433;"                'サーバー,ポート
    Const MYNINSYO = "Trusted_connection=yes;"                    'Windows認証の場合（「"Integrated Security=SSPI;"」でもよい）
    Const MYDATABASE = "Initial Catalog=TestDB01;"                '接続するデータベース名
    constr = MYPROVIDERE & MYSERVER & MYNINSYO & MYDATABASE       'Windows認証の場合
    'Const USER = "User ID=sqluser;"                               'SQL Server認証の場合のみ指定
    'Const PSWD = "Password=psword;"                               'SQL Server認証の場合のみ指定
    'constr = MYPROVIDERE & MYSERVER & MYDATABASE & USER & PSWD    'SQL Server認証の場合
    
    'ODBC
    'Const DSN = "DSN=SQLServer_TESTDB01;"        'ODBCユーザーデータソース名
    'constr = DSN                                 'Windows認証の場合
    'Const USER = "UID=sqluser;"                  'ユーザー名（SQL Server認証を使用する場合のみ指定）
    'Const PSWD = "PWD=psword;"                   'パスワード（SQL Server認証を使用する場合のみ指定）
    'constr = DSN & USER & PSWD                   'SQL Server認証の場合
    
    
    Dim cn As New ADODB.Connection
    Dim Rs As New ADODB.Recordset
    Dim strSQL As String
    Dim i As Long
    Dim j As Long
    
    cn.ConnectionString = constr
    cn.Open
    
    strSQL = "Select * from T_SYAIN;"
    Rs.Source = strSQL
    Rs.ActiveConnection = cn
    Rs.Open
    
    With Worksheets("sqlserver")
        
        .Cells.Clear
        i = 1
        Do Until Rs.EOF
            For j = 0 To Rs.Fields.Count - 1
                If i = 1 Then .Cells(i, j + 1) = Rs.Fields(j).Name
                .Cells(i + 1, j + 1) = Rs(j).Value
            Next j
            Rs.MoveNext
            i = i + 1
        Loop
    
    End With
    
    Rs.Close
    Set Rs = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Sub