データベース(Access 等)に接続(ADO)
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ソース その1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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ソース その2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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 |
実行結果
関連記事
-
レコードの更新(ADO)Update メソッド
データベースのレコードを更新する Update メソッド カレントレコードの内容 …
-
Excelファイルに接続(ADO)
ADOでExcelファイルに接続 ADO を使用してExcelファイルを読み込み …
-
Recordset オブジェクト内のレコード数(ADO)RecordCount プロパティ
RecordCount プロパティ Recordset オブジェクトのレコード数 …
-
フィールド名(ADO)Fields コレクション
Fields コレクション Recordset オブジェクト内の1つ以上の列(フ …
-
カレントレコードの移動(ADO)MoveFirst、MoveLast、MoveNext、MovePrevious、Move
MoveFirst メソッド、MoveLast メソッド、MoveNext メソ …
-
レコードを並べ替える(ADO)Sort プロパティ
データベースのレコードを並べ替える Sort プロパティ 【書式】 object …
-
SQL文を実行する(ADO)Execute メソッド
Command オブジェクトの Execute メソッドで、SQL文を実行 Co …
-
テーブルの作成・削除/フィールドの作成・削除(ADOX)
ADOX を使用してテーブル・フィールド(列)を作成・削除 Table オブジェ …
-
データベースを作成する(ADOX)
ADOX を使用してデータベース(Access)を作成 Catalog オブジェ …
-
データベース(Oracle)に接続する(ODBC を使用)
ODBC接続(ADO) ADOで、ODBC(Open Database Conn …