データベース（Access 等）に接続（ADO）

2016/10/10 2017/10/23

ADO で、データベースに接続

外部データベースを操作する方法のひとつに ADO（AcriveX Data Objects）があります。
使用するには、ライブラリファイルへの参照設定を行う方法と、CreateObject 関数 を使う方法があります。

Connection オブジェクト・Recordset オブジェクト

【書式】

＜CreateObject 関数を使用する場合＞
Dim cn As Object　　　……　Connection オブジェクト
Dim rs As Object　　　……　Recordset オブジェクト

Set cn = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)

＜参照設定を行う場合＞
Dim cn As New ADODB.Connection　　　……　Connection オブジェクト
Dim rs As New ADODB.Recordset 　　　……　Recordset オブジェクト

参照設定を行うライブラリファイルについて

Excel2003 以前では、Microsoft ActiveX Data Objects 2.8 Library を選択します。
Excel2007 以降では、Microsoft ActiveX Data Objects バージョン番号 Library を選択します。
※ 複数のバージョンがあります。特に必要がなければ最新バージョンを選択すると良いでしょう。
参照設定の方法については、ファイルシステムオブジェクト（FileSystemObject）の使い方を参照してください。

CreatObject 関数 を使用する場合は、参照設定の必要はありませんが、VBE で、自動メンバ表示等のコーディング支援機能が使用できません。また、マクロの実行速度も遅くなります。

データベースへ接続（Open メソッド）

【書式】

object.Open[ ( ConnectionString, UserID, Password, Options ) ]

引数・戻り値

object ・・・対象となる Connection オブジェクトを指定します。

ConnectionString ・・・接続情報を含む文字列型（String）の値（「変数名=設定値;」)を指定します（省略可）。
【ConnectionString 内で使用する変数について】
■　Provider：接続するデータベースの種類を指定します。

データベース	設定する文字列
Access 2007	Microsoft.ACE.OLEDB.12.0;
Access 2003 以前	Microsoft.Jet.OLEDB.4.0;
Oracle	MSDAORA;
SQLServer	SQLOLEDB;
DB2	IBMDADB2;
ODBC 接続	MSDASQL;
Excel 2007	Microsoft.ACE.OLEDB.12.0;
Excel 2003 以前	Microsoft.Jet.OLEDB.4.0;
CSV ファイル	Microsoft.Jet.OLEDB.4.0;

■　Data Source：接続するデータベースの場所（パス・ファイル名）を指定します。
　　※ CSV ファイルの場合、ファイルの存在するパスのみを指定します（最後は、\）。

■　Extended Properties：ExcelやCSVファイルに接続するときに使用します。

データベース	設定する文字列
Excel 2007	Excel 12.0;
Excel 2003 以前	Excel 8.0;
CSV ファイル	Text;

UserID ・・・データベースに接続するときに使用するユーザー名を指定します（省略可）。
Password ・・・データベースに接続するときに使用するパスワードを指定します（省略可）。

Options ・・・このメソッドが、接続が確立された後 (同期) と確立される前 (非同期) のいずれで終了するかを決定する ConnectOptionEnum 値を指定します。

定数	値	内容
adAsyncConnect	16	非同期で接続します。ConnectComplete イベントは、接続可能かどうかを決定するために使用されることがあります。
adConnectUnspecified	-1	既定値です。同期で接続します。

データベースのレコードを参照する（Open メソッド）

【書式】

object.Open[ ( Source, ActiveConnection, CursorTypes, LockType, Options ) ]

引数・戻り値

object ・・・対象となる Recordset オブジェクトを指定します。
Source ・・・テーブル名、クエリ、SQL文などを指定します。
ActiveConnection ・・・ Connection オブジェクト を指定します。

CursorTypes ・・・レコードを参照するカーソルの種類を CursorTypeEnum クラス の定数（またはその値）を指定します。

定数	値	内容
adOpenForwardOnly	0	前方専用カーソル。レコードのスクロール方向が前方向に限定されていることを除き、静的カーソルと同じ働きをします。（既定値）
adOpenKeyset	1	キーセットカーソルを使います。ほかのユーザーが追加したレコードは表示できない点を除き、動的カーソルと同じく、自分の Recordset からほかのユーザーが削除したレコードはアクセスできません。ほかのユーザーが変更したデータは表示できます。
adOpenDynamic	2	動的カーソルを使います。ほかのユーザーによる追加、変更、および削除を確認できます。プロバイダがブックマークをサポートしていない場合を除き、Recordset 内でのすべての動作を許可します。
adOpenStatic	3	キーセットカーソルを開きます。データの検索またはレポートの作成に使用するための、レコードの静的コピーです。ほかのユーザーによる追加、変更、または削除は表示されません。
adOpenUnspecified	-1	カーソルの種類を指定しません。

LockType ・・・省略可能です。プロバイダが Recordset を開くときに使うロック（同時作用）の種類を LockTypeEnum クラス の定数（またはその値）を指定します。

定数	値	内容
adLockReadOnly	1	読み取り専用。データの変更は出来ません（既定値）。
adLockPessimistic	2	レコード単位の排他的ロック。編集直後のデータソースでレコードをロックします。
adLockOptimistic	3	レコード単位の共有的ロック。Update メソッドを呼び出した場合にのみ、レコードをロックします。
adLockBatchOptimistic	4	共有的バッチ更新。バッチ更新モードの場合にのみ指定できます。
adLockUnspecified	-1	ロックの種類を指定しません。

Options ・・・引数 Source に指定した内容の種類を CommandTypeEnum クラス の定数（またはその値）を指定します（AND 演算子で組み合わせて使用可）。

定数	値	内容
adCmdText	1	SQL 文
adCmdTable	2	テーブル名
adCmdStoredProc	4	ストアドプロシージャ名
adCmdUnknown	8	不明（既定値）
adCmdFile	256	永続的に保存された Recordset のファイル名
adCmdTableDirect	512	列がすべて返されるテーブル名
adCmdUnspecified	-1	コマンドタイプ引数を指定しません

データベース（Access）へ接続する例

サンプルVBAソースその１

　
Sub Sample_ADO_Access1()
    
    '参照設定
    'Microsoft ActiveX Data Objects 6.1 Library

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim ConStr As String
    Dim DBFile As String
    Dim strSQL As String
    Dim FieldArray
    Dim i As Long
    Dim j As Long
    Dim v As Variant
    
    ReDim FieldArray(1 To 1)
        
    DBFile = ActiveWorkbook.Path & "\mydb1.accdb"
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile     'Access 2007 以降
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile     'Access 2003 以前
    
    cn.ConnectionString = ConStr
    cn.Open
    
    strSQL = "Select * from 社員名簿 order by ID desc;"
    rs.Source = strSQL
    rs.ActiveConnection = cn
    rs.Open

    With Worksheets("Sheet1")
        
        .Cells.Clear
        
        i = 1
        Do Until rs.EOF
            For j = 0 To rs.Fields.Count - 1
                .Cells(i, 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

Sub Sample_ADO_Access1()

'参照設定

'Microsoft ActiveX Data Objects 6.1 Library

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim ConStr As String

Dim DBFile As String

Dim strSQL As String

Dim FieldArray

Dim i As Long

Dim j As Long

Dim v As Variant

ReDim FieldArray(1 To 1)

DBFile = ActiveWorkbook.Path & "\mydb1.accdb"

ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile 'Access 2007 以降

'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile 'Access 2003 以前

cn.ConnectionString = ConStr

cn.Open

strSQL = "Select * from 社員名簿 order by ID desc;"

rs.Source = strSQL

rs.ActiveConnection = cn

rs.Open

With Worksheets("Sheet1")

.Cells.Clear

i = 1

Do Until rs.EOF

For j = 0 To rs.Fields.Count - 1

.Cells(i, 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

実行結果

データベース（Access）へ接続する例（CreateObject 関数を使用）

サンプルVBAソースその２

　
Sub Sample_ADO_Access2()
    
    'CreateObject 関数

    Dim cn As Object
    Dim rs As Object
    Dim ConStr As String
    Dim DBFile As String
    Dim strSQL As String
    Dim FieldArray
    Dim i As Long
    Dim j As Long
    Dim v As Variant
    
    ReDim FieldArray(1 To 1)
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    DBFile = ActiveWorkbook.Path & "\mydb1.accdb"
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile      'Access 2007 以降
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile      'Access 2003 以前
    
    cn.Open ConnectionString:=ConStr & ";"
    
    strSQL = "Select * from 社員名簿;"

    rs.Open strSQL, cn
    
    'フィールド名取得
    j = 1
    For Each v In rs.Fields
        
        ReDim Preserve FieldArray(1 To j)
        FieldArray(j) = v.Name
        j = j + 1
    
    Next

    'Sheet1 に表示
    With Worksheets("Sheet1")
        
        .Cells.Clear
        
        'フィールド名
        .Range(.Cells(1, 1), .Cells(1, UBound(FieldArray))) = FieldArray
        
        'レコード
        .Range("A2").CopyFromRecordset rs
    
    End With
    
DBEnd:
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing

End Sub