ADO(ActiveX Data Objects)の使い方の要点
ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です。
OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。
ここでは、ADOを使用したデータベースへの接続方法を解説します。
対応するドライバーや、プロバイダが使用可能であれば、どんなDBでも扱えます。
目次
データベースの種類
本ページで接続例として記載するデータベースは、
CSV
Excel
Access
MySql
SQLServer
Oracle
以上になります。
CSVもExcelもデータベースとして扱えます。
ただし、データがデータベース形式になっていることが前提です。
（各列に見出しがあり、列見出しの下に1行1件のデータが並んでいること）
Excel
Access
MySql
SQLServer
Oracle
CSVもExcelもデータベースとして扱えます。
ただし、データがデータベース形式になっていることが前提です。
（各列に見出しがあり、列見出しの下に1行1件のデータが並んでいること）
SQL（SQL:Structured Query Language）
※「SQLは何かの略語ではない」らしいですが、上記のように説明されていることが多いので記載しています。
ADOには、
レコードの追加・削除・検索・抽出・並べ替えなどの基本的な操作が用意されています。
この操作を記述するのが、SQL文です。
VBAでのSQLの基礎（SQL:Structured Query Language）
SQL文を書くことで、
複数のテーブルから、必要なデータセットを自由に抽出、また更新、追加する事ができます。
新シリーズ「SQL入門」を開始しました。
レコードの追加・削除・検索・抽出・並べ替えなどの基本的な操作が用意されています。
この操作を記述するのが、SQL文です。
VBAでのSQLの基礎（SQL:Structured Query Language）
・SQL文 ・SELECT文 ・SQLの学習について ・実践例
SQL文を書くことで、
複数のテーブルから、必要なデータセットを自由に抽出、また更新、追加する事ができます。
・DBとはSQLとは ・SQL入門の目次 ・SQLを使った既存サンプル
より詳しくSQLについて解説しています。ADOを使う準備
VBE（VBAの編集画面）で、
ツール→参照
ここで、
Microsoft ActiveX Data Objects 2.X Library
または
Microsoft ActiveX Data Objects 6.1 Library
を追加して下さい。
オブジェクトのバージョンは各種ありますが、現在なら2.8が無難でしょうか。
このように事前に参照設定することを、事前バインディングと呼びます。
事前に参照設定をしない方法もあります。
CreateObject関数を使います。
この方法は、実行時バインディングまたは遅延バインディングといいます。
ツール→参照
ここで、
Microsoft ActiveX Data Objects 2.X Library
または
Microsoft ActiveX Data Objects 6.1 Library
を追加して下さい。
オブジェクトのバージョンは各種ありますが、現在なら2.8が無難でしょうか。
このように事前に参照設定することを、事前バインディングと呼びます。
CreateObject関数を使います。
この方法は、実行時バインディングまたは遅延バインディングといいます。
参照設定、CreateObject、オブジェクト式の一覧
VBAでエクセル外のオブジェクトを使うときには、事前バインディングと遅延バインディング（実行時バインディング）の２通りがあります、この時それぞれ何を指定したらよいのか、指定する文字列が長いので結構探してしまうことが度々あります。そこで、自身の覚え書きとしての意味も含めて、参照設定、CreateObjectのclas…
基本的には、事前バインディングを使ってマクロを作成します。
入力候補に、オブシェクトのメンバーが表示されますので、マクロを書く際に役に立ちます。
VBAが完成後に配布用として、実行時バインディングに書き直すという場合もあります。
自身の備忘録も兼ねています。
ADOでのDB接続方法
CSV：VBAの全コード
Sub ReadCsv()
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String
'コネクション
With objCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No"
.Open ThisWorkbook.Path & "\" 'フォルダを絶対パスで
'以下でも良い
'.ConnectionString = ThisWorkbook.Path & "\"
'.Open
End With
'SQL作成
strSQL = ""
strSQL = strSQL & " SELECT *"
strSQL = strSQL & " FROM"
strSQL = strSQL & " CSVTEST.csv"
'SQL実行
Set objRS = objCn.Execute(strSQL)
'レコードセットをシート出力
With Worksheets("出力シート")
.UsedRange.ClearContents
.Range("A1").CopyFromRecordset objRS
End With
'切断
objRs.Close
objCn.Close
Set objRS = Nothing
Set objCn = Nothing
End Sub
CSVは1ファイル1テーブルになるので、CSVフアイル名がテーブル名になります。
ヘッターがない場合（HDR=No）は、フィールド名はF1,F2,F3,…となります。
・CSVテストデータ ・ADOでCSV読込のVBA ・ADO使用時の注意点 ・ADOレコードセットをCSV出力 ・ADOでTSVの読み込み ・ADOでCSVの読み込みについて ・本サイトにあるCSV関連記事一覧
ADOでテキストデータを集計する・#助けてVBA の元ツイート ・解答したツイート ・ADOとSQLの解説
以下、接続に関しては、
With objCn
この部分の違いだけですので、ここだけを掲載します。
End With
With objCn
この部分の違いだけですので、ここだけを掲載します。
End With
Excel
With objCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0"
.Open ThisWorkbook.Path & "\TEST.xlsx" '絶対パスで指定
End With
ExcelはブックがDBとなり、シートがテーブル名となります。
したがって、ブックをOpenし、SQLのテーブル指定でシート名を指定します。
以下を参照してください。
VBAでADOを使用し、マスターデータよりデータ付加します。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を料略します。自身の他シートから、マスタ情報を付加し、さらに、集計をします。
ADOでマスタ更新（SQL）VBAでADOを使用し、マスターデータを更新します。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を料略します。自身のブックの、他シートを更新します。シート「顧客マスタ」の、A列が顧客番号、B列が顧客名で、1行目が見出しになっているものとします。
Excelファイルを開かずにシート名を取得Excelファイルを開くときは、ファイルサイズに比して、とても遅い場合があります、ファイルを開かずに、Excelの情報を取得したいという要望は少なからずあるようです、そこで、処理の基本となるシート名を取得する方法になります。もちろん、開かないと言っても、厳密には必ず開いているわけですが、Excelのブックとして開か…
Excelファイルを開かずにシート名をチェック多くのExcelファイルから、特定のシート名のデータを取得する場合、当該シートが存在していないブックがあるならば、ブックを開く前にシートの存在をチェックしたくなります。このような、ファイルを開かずにExcelの情報を取得したいという要望は多くあります。
Access
Dim strDb As String
strDB = ThisWorkbook.Path & "\sample.accdb" '絶対パスで指定
With objCn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
& strDB
.Open
End With
VBA100本ノック 96本目：Accessデータを取得（マスタ結合&抽出）
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
VBA100本ノック 97本目：Accessデータを取得（グループ集計）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
SQLite
With objCn
.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=絶対パスで指定"
.Open
End With
SQLite3を使って、より詳しくSQLについて解説しています。
・DBとはSQLとは ・SQL入門の目次 ・SQLを使った既存サンプル
MySql
.ConnectionStringに入れる文字列の違いになります。
ドライバーは、
"MySQL ODBC 3.51 DRIVER"
"MySQL ODBC 4.1 DRIVER"
"MySQL ODBC 4.2 DRIVER"
"MySQL ODBC 5.1 DRIVER"
"MySQL ODBC 5.2 DRIVER"
"MySQL ODBC 5.3 DRIVER"
等、PCにインストールされているドライバーを指定します。
"Driver={MySQL ODBC 5.1 DRIVER}; SERVER=サーバー名(またはIPアドレス); DATABASE=DB名;
USER=ユーザーID; PASSWORD=パスワード;"
ドライバーは、
"MySQL ODBC 3.51 DRIVER"
"MySQL ODBC 4.1 DRIVER"
"MySQL ODBC 4.2 DRIVER"
"MySQL ODBC 5.1 DRIVER"
"MySQL ODBC 5.2 DRIVER"
"MySQL ODBC 5.3 DRIVER"
等、PCにインストールされているドライバーを指定します。
SQLServer
.ConnectionStringに入れる文字列の違いになります。
"Provider=SQLOLEDB;Data Source=サーバー名(またはIPアドレス);Initial Catalog=DB名;user
id=ユーザーID;password=パスワード"
Oracle
.ConnectionStringに入れる文字列の違いになります。
オラクルの場合は、Oracleクライアントのインストールが必要です。
そして、リスナーの登録をしておいて下さい。
"Provider=OraOLEDB.Oracle;Data Source=リスナー名;User ID=ユーザーID;Password=パスワード;"
オラクルの場合は、Oracleクライアントのインストールが必要です。
そして、リスナーの登録をしておいて下さい。
ODBCを使う場合
DBに依存しない方法としては、ODBCを使う事も出来ます。
ODBCを事前に作成しておきますが、ODBCの32bitと64bitに注意してください。
※OSが64bitのコントロールパネルにあるODBCは64bit用です。
"DSN=ODBC名;UID=ユーザーID;PWD=パスワード;"
ODBCを事前に作成しておきますが、ODBCの32bitと64bitに注意してください。
※OSが64bitのコントロールパネルにあるODBCは64bit用です。
32bit用の設定は、C:\Windows\SysWOW64\odbcad32.exe
VBAで使用する場合は、32bit用のODBCを使用した方がやりやすいかもしれません。
もちろん64bitでも問題ありません。
ODBC用のドライバーとbit数を合わせて使ってください。
VBAで使用する場合は、32bit用のODBCを使用した方がやりやすいかもしれません。
もちろん64bitでも問題ありません。
ODBC用のドライバーとbit数を合わせて使ってください。
ADODB.Recordsetの取得方法
ADODB.ConnectionのExecuteメソッド
最初のサンプルVBAで使っている方法です。
Set recordset = connection.Execute (CommandText, RecordsAffected, Options)
CommandText
実行する SQL文字列の値です。
RecordsAffected
省略可能です。
この操作の影響を受けたレコード数をプロバイダーが返す長整数型 ( Long ) の値です。
この操作の影響を受けたレコード数をプロバイダーが返す長整数型 ( Long ) の値です。
Options
省略可能です。
MoveRecordOptionsEnum値を指定します。
プロバイダーが CommandText引数を評価する方法を示す長整数型 (Long) の値です。
1つまたは複数のCommandTypeEnum値またはExecuteOptionEnum値のビットマスクを指定できます。
MoveRecordOptionsEnum値を指定します。
プロバイダーが CommandText引数を評価する方法を示す長整数型 (Long) の値です。
1つまたは複数のCommandTypeEnum値またはExecuteOptionEnum値のビットマスクを指定できます。
CommandTypeEnum
| 定数 | 値 | 説明 |
| adcmdunspecified | -1 | コマンドの種類の引数を指定しません。 |
| adcmdtext | 1 | CommandTextを、コマンドまたはストアド プロシージャのテキスト定義として評価します。 |
| adcmdtable | 2 | CommandTextを、内部的に生成された SQL クエリから返された列のみで構成されるテーブル名として評価します。 |
| adCmdStoredProc | 4 | CommandTextをストアド プロシージャ名として評価します。 |
| adcmdunknown | 8 | 既定値。 CommandTextプロパティのコマンドの種類が不明であることを示します。 |
| adCmdFile | 256 | CommandTextを、保存されたRecordsetのファイル名として評価します。Recordset.OpenまたはRequeryと組み合わせてのみ使用できます。 |
| adCmdTableDirect | 512 | CommandTextを、すべての列が返されたテーブル名として評価します。 Recordset.OpenまたはRequeryと組み合わせてのみ使用できます。 Seekメソッドを使用する場合、RecordsetはadCmdTableDirectを指定して開く必要があります。 この値は、ExecuteOptionEnumの値adAsyncExecuteと組み合わせて使用できません。 |
ExecuteOptionEnum
| 定数 | 値 | 説明 |
| adAsyncExecute | 0x10 | コマンドを非同期に実行することを示します。 この値は、CommandTypeEnumの値adCmdTableDirectと組み合わせて使用できません。 |
| adAsyncFetch | 0x20 | CacheSizeプロパティで指定した初期量の残りの行を非同期に取得することを示します。 |
| adAsyncFetchNonBlocking | 0x40 | 取得中にメイン スレッドがブロックしないことを示します。 要求された行がまだ取得されていない場合、現在の行が自動的にファイルの最後に移動します。 永続的に保存されたRecordsetを持つStreamからRecordsetを開いた場合、adAsyncFetchNonBlockingは無効になり、操作は同期で実行され、ブロッキングが発生します。 adCmdTableDirectオプションを使用してRecordsetを開いた場合、adAsynchFetchNonBlockingは無効になります。 |
| adExecuteNoRecords | 0x80 | コマンド テキストが、行を返さないコマンドまたはストアド プロシージャ (たとえば、データの挿入のみを行うコマンド) であることを示します。 取得した行があっても削除されるので、コマンドからは返されません。 adExecuteNoRecordsは、コマンドまたはConnectionのExecuteメソッドに、省略可能なパラメーターとしてのみ渡すことができます。 |
| adExecuteStream | 0x400 | コマンドの実行結果がストリームとして返されることを示します。 adExecuteStreamは、CommandExecuteメソッドにオプションのパラメーターとして渡すことができます。 |
| adExecuteRecord | CommandTextが、Recordオブジェクトとして返される単一の行を返すコマンドまたはストアド プロシージャであることを示します。 | |
| adoptionunspecified | -1 | コマンドが指定されていないことを示します。 |
内部処理を最小限に抑えてパフォーマンスを向上させるには、ExecuteOptionEnumの値adExecuteNoRecords を使用します。
Executeでは、CommandTypeEnumの値 dCmdFileまたはadCmdTableDirectを使用しないでください。 これらの値は、RecordsetのOpenメソッドおよびRequeryメソッドのオプションとしてのみ使用できます。
ADODB.ConnectionのExecuteの使用例
Executeでは、CommandTypeEnumの値 dCmdFileまたはadCmdTableDirectを使用しないでください。 これらの値は、RecordsetのOpenメソッドおよびRequeryメソッドのオプションとしてのみ使用できます。
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
'コネクション
'SQL作成
Set objRS = New ADODB.Recordset
Set objRS = objCn.Execute("SQL文字列")
ADODB.RecordsetのOpenメソッド
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Source
オプション。
有効なCommandオブジェクト、SQLステートメント、テーブル名、ストアドプロシージャコール、URL、または永続的に保存されたRecordsetを含むファイルまたはStreamオブジェクトの名前に評価されるVariant。
有効なCommandオブジェクト、SQLステートメント、テーブル名、ストアドプロシージャコール、URL、または永続的に保存されたRecordsetを含むファイルまたはStreamオブジェクトの名前に評価されるVariant。
ActiveConnection
オプション。
有効なConnectionオブジェクト変数名に評価されるVariant、またはConnectionStringパラメーターを含むStringのいずれか。
有効なConnectionオブジェクト変数名に評価されるVariant、またはConnectionStringパラメーターを含むStringのいずれか。
CursorType
オプション。
Recordsetを開くときにプロバイダーが使用するカーソルのタイプを決定するCursorTypeEnum値。
デフォルト値はadOpenForwardOnlyです。
Recordsetを開くときにプロバイダーが使用するカーソルのタイプを決定するCursorTypeEnum値。
デフォルト値はadOpenForwardOnlyです。
| 定数 | Value | 説明 |
| adOpenDynamic | 2 | 動的カーソルを使用します。 他のユーザーによる追加、変更、削除が表示され、プロバイダーがサポートしていない場合、ブックマークを除き、Recordsetを介したすべてのタイプの移動が許可されます。 |
| adOpenForwardOnly | 0 | デフォルト。 順方向専用カーソルを使用します。 レコードを順方向にしかスクロールできないことを除いて、静的カーソルと同じです。 これにより、レコードセットを1回だけ通過する必要がある場合のパフォーマンスが向上します。 |
| adOpenKeyset | 1 | キーセットカーソルを使用します。 動的カーソルに似ていますが、他のユーザーが追加したレコードは表示できませんが、他のユーザーが削除したレコードにはレコードセットからアクセスできません。 他のユーザーによるデータの変更は引き続き表示されます。 |
| adOpenStatic | 3 | 静的カーソルを使用します。 静的カーソルは、データの検索またはレポートの生成に使用できる一連のレコードの静的コピーです。 他のユーザーによる追加、変更、または削除は表示されません。 |
| adOpenUnspecified | -1 | カーソルの種類を指定しません。 |
LockType
オプション。
Recordsetを開くときにプロバイダーが使用するロックの種類（同時実行性）を決定するLockTypeEnum値。
デフォルト値はadLockReadOnlyです。
Recordsetを開くときにプロバイダーが使用するロックの種類（同時実行性）を決定するLockTypeEnum値。
デフォルト値はadLockReadOnlyです。
| Constant | Value | Description |
| adLockBatchOptimistic | 4 | 楽観的なバッチ更新を示します。 バッチ更新モードに必要です。 |
| adLockOptimistic | 3 | レコードごとに楽観的ロックを示します。 プロバイダーは楽観的ロックを使用し、Updateメソッドを呼び出すときにのみレコードをロックします。 |
| adLockPessimistic | 2 | レコードごとに悲観的ロックを示します。 プロバイダーは、通常、編集直後にデータソースでレコードをロックすることにより、レコードの正常な編集を保証するために必要なことを行います。 |
| adLockReadOnly | 1 | 読み取り専用レコードを示します。 データを変更することはできません。 |
| adLockUnspecified | -1 | ロックのタイプを指定しません。 クローンの場合、クローンはオリジナルと同じロックタイプで作成されます。 |
Options
オプション。
Commandオブジェクト以外のものを表す場合、または以前に保存されたファイルからRecordsetを復元する必要がある場合、プロバイダがSource引数を評価する方法を示すLong値。
1つ以上のCommandTypeEnum値またはExecuteOptionEnum値を指定できます。
これらの値は、ビット単位のOR演算子と組み合わせることができます。
※CommandTypeEnum値およびExecuteOptionEnum値については、ConnectionのExecuteに記載してあります。
Commandオブジェクト以外のものを表す場合、または以前に保存されたファイルからRecordsetを復元する必要がある場合、プロバイダがSource引数を評価する方法を示すLong値。
1つ以上のCommandTypeEnum値またはExecuteOptionEnum値を指定できます。
これらの値は、ビット単位のOR演算子と組み合わせることができます。
※CommandTypeEnum値およびExecuteOptionEnum値については、ConnectionのExecuteに記載してあります。
ADODB.RecordsetのOpenメソッドの使用例
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
'コネクション接続する
'SQL作成する
Set objRS = New ADODB.Recordset
Call objRS.Open("SQL文字列", objCn, adOpenStatic, adLockReadOnly)
ADODBのレコードセットの扱い方
レコードセットの概要
ADODBのレコードセットには多くのメソッドがあります。
以下は良く使う代表的なメソッド・プロパィについての使用例になります。
Recordsetを開くと、現在のレコードは最初のレコード（存在する場合）に配置され、BOFプロパティとEOFプロパティはFalseに設定されます。
レコードがない場合、BOFおよびEOFプロパティ設定はTrueです。
指定されたRecordsetオブジェクトの最初(MoveFirst)、最後(MoveLast)、次(MoveNext)、または前(MovePrevious)のレコードに移動し、そのレコードを現在のレコードにします。
以下は良く使う代表的なメソッド・プロパィについての使用例になります。
レコードがない場合、BOFおよびEOFプロパティ設定はTrueです。
Recordsetのシートへの一括出力
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
'コネクション接続する
'SQL作成する
Set objRS = New ADODB.Recordset
Call objRS.Open("SQL文字列", objCn, adOpenStatic, adLockReadOnly)
With Worksheets("出力シート")
.UsedRange.ClearContents
.Range("A1").CopyFromRecordset objRS
End With
RangeオブジェクトのCopyFromRecortsetメソッドを使用します。
CopyFromRecortsetメソッド
指定された範囲の左上隅を始点として、Recordsetオブジェクトの内容をワークシートにコピーします。
Rangeオブジェクト.Copyfromrecordset(Data、 MaxRows、 MaxColumns)
Recordsetオブジェクトのカレント レコードの位置からコピーが行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。
| 名前 | 説明 |
| Data | 必須です。 セル範囲にコピーするRecordsetオブジェクトを指定します。 |
| MaxRows | 省略可能です。 ワークシートにコピーするレコードの最大数を指定します。 この引数を省略すると、Recordset?オブジェクトのすべてのレコードをコピーします。 |
| MaxColumns | 省略可能です。 ワークシートにコピーするフィールドの最大数を指定します。 この引数を省略すると、Recordsetオブジェクトのすべてのフィールドをコピーします。 |
Recordsetオブジェクトのカレント レコードの位置からコピーが行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。
Recordsetの配列への出力
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
'コネクション接続する
'SQL作成する
Set objRS = New
ADODB.Recordset
Call objRS.Open("SQL文字列", objCn, adOpenStatic,
adLockReadOnly)
Dim ary()
ary = objRS.GetRows
RecordsetのGetRowsメソッドを使用します。
レコードセットオブジェクトの複数のレコードを配列に取得します。
値が2次元配列であるバリアントを返します。
array = recordset.GetRows(Rows, Start, Fields)
最初の添字はフィールドを識別し、2番目のインデックスはレコード番号を識別します。
GetRowsメソッドがデータを返すと、配列変数は自動的に正しいサイズに設定されます。
つまり、1次元目が列で、2次元目が行になっています。
したがって、シートに出力するためには、縦横を反転（Transpose等）させる必要があります。
値が2次元配列であるバリアントを返します。
| 名前 | 説明 |
| Rows | 省略可能。 取得するレコードの数を示すGetRowsOptionEnum値です。 既定値はadGetRowsRestです。（Startパラメーターによって指定された現在の位置またはブックマークから、レコードセット内の残りのレコードを取得します。） |
| Start | 省略可能。 GetRows操作の開始位置となるレコードのブックマークに評価される文字列値またはバリアント。 BookmarkEnum値を使用することもできます。 |
| Fields | 省略可能。 1つのフィールド名または序数位置、またはフィールド名または序数位置の配列を表すバリアント。 ADOは、これらのフィールド内のデータのみを返します。 |
最初の添字はフィールドを識別し、2番目のインデックスはレコード番号を識別します。
GetRowsメソッドがデータを返すと、配列変数は自動的に正しいサイズに設定されます。
つまり、1次元目が列で、2次元目が行になっています。
したがって、シートに出力するためには、縦横を反転（Transpose等）させる必要があります。
Recordsetの取得件数
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
'コネクション接続する
'SQL作成する
Set objRS = New ADODB.Recordset
Call objRS.Open("SQL文字列", objCn, adOpenStatic, adLockReadOnly)
If objRS.RecordCount <= 0 Then
MsgBox "レコードなし"
End If
RecordCountで件数を取得する場合は、adOpenStaticを指定しRecordCountで取得します。
カラム名の取得
Dim i As Long
'カラム名の出力
For i = 0 To objRS.Fields.Count - 1
Cells(1, i + 1) = objRS.Fields(i).Name
Next
Fieldsコレクションの要素を指定してFieldオブジェクトを特定し、Nameプロパティで取得します。
レコードセットの全件順処理
Dim i As Long, j As Long
i = 2
objRS.MoveFirst 'ここでは不要だが参考として
Do Until objRS.EOF
For j = 0 To objRS.Fields.Count - 1
Cells(i, j + 1) = objRS(j).Value
Next
objRS.MoveNext
i = i + 1
Loop
EOFプロパティがTrueになるまで、MoveNextでカレントレコードを次に進めていきます。
レコードの検索/追加/更新/削除
レコードの検索
recordset.Find (Criteria、 SkipRows、 searchdirection、 Start)
| adsearchbackward | -1 | 後方検索をし、Recordset?の先頭で終了します。一致するレコードが見つからない場合、レコード ポインターは?BOF?に移動します。 |
| adsearchforward | 1-d | 前方検索をし、Recordset?の末尾で終了します。一致するレコードが見つからない場合、レコード ポインターは?EOF?に移動します。 |
レコードの追加
recordset.AddNew FieldList, Values
FieldListおよびValuesは、配列を指定します。
FieldListおよびValuesは、配列を指定します。
レコードの更新
recordset.Update FieldList, Values
FieldListおよびValuesは、配列を指定します。
カレント行が更新されますので、事前に対象レコードに移動しておきます。
FieldListおよびValuesは、配列を指定します。
カレント行が更新されますので、事前に対象レコードに移動しておきます。
レコードの削除
recordset.Delete
カレント行が削除されますので、事前に対象レコードに移動しておきます。
カレント行が削除されますので、事前に対象レコードに移動しておきます。
検索/追加/更新/削除の使用例
With objRS
'追加
.AddNew Array("列1", "列2"), Array("値1", "値2")
'更新
.MoveFirst
.Find "列1 = 値1", 0, adSearchForward
.Update Array("列1", "列2"), Array("値1", "値2")
'削除
.MoveFirst
.Find "列2 = 値2", 0, adSearchForward
.Delete
End With
ADODBのトランザクション処理
ADODBでトランザクション処理を行うには、ADODB.Connectionオブジェクトのメソッドを使用します。
BeginTrans メソッド
CommitTrans メソッド
RollbackTrans メソッド
各DBのプロバイダーによっては、入れ子になったトランザクションをサポートしています。
BeginTransでトランザクションを開始した後、CommitTransすることなくDBをクローズした場合はロールバックされます。
トランザクションの更新内容は、CommitTransしない限り確定されることはありません。
トランザクション処理の詳細については、以下を参照してください。
CommitTrans メソッド
RollbackTrans メソッド
BeginTransでトランザクションを開始した後、CommitTransすることなくDBをクローズした場合はロールバックされます。
トランザクションの更新内容は、CommitTransしない限り確定されることはありません。
ADODB.Commandの使い方
ADOにはCommandオブジェクトがあり、パラメーター方式でSQLを実行できます。
SQL文字列中の「?」に対して値リストを順番に適用します。
同じSQLを実行する場合は、データだけをパラメーターとして渡すので効率的です。
以下で具体的に使用しています。
AdoCmd = New ADODB.Command
Set AdoCmd.ActiveConnection = 接続済コネンション
AdoCmd.CommandText = SQL文字列 '(?,?,?)
AdoCmd.Execute Parameters:=値リスト1 '(値1,値2,値3)
'・・・
AdoCmd.Execute Parameters:=値リスト2 '(値4,値5,値6)
SQL文字列中の「?」に対して値リストを順番に適用します。
同じSQLを実行する場合は、データだけをパラメーターとして渡すので効率的です。
データの挿入（INSERT）と全削除｜SQL入門
・テスト用のテーブル作成 ・データ挿入：INSERT INTO ・テストデータをシートで作成 ・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために ・テーブルの全削除 ・データを挿入の最後に
VBA100本ノックでの実践例
ADOでマスタ付加と集計（SQL）
VBAでADOを使用し、マスターデータよりデータ付加します。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を料略します。自身の他シートから、マスタ情報を付加し、さらに、集計をします。
ADOでマスタ更新（SQL）VBAでADOを使用し、マスターデータを更新します。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を料略します。自身のブックの、他シートを更新します。シート「顧客マスタ」の、A列が顧客番号、B列が顧客名で、1行目が見出しになっているものとします。
Excelファイルを開かずにシート名を取得Excelファイルを開くときは、ファイルサイズに比して、とても遅い場合があります、ファイルを開かずに、Excelの情報を取得したいという要望は少なからずあるようです、そこで、処理の基本となるシート名を取得する方法になります。もちろん、開かないと言っても、厳密には必ず開いているわけですが、Excelのブックとして開か…
Excelファイルを開かずにシート名をチェック多くのExcelファイルから、特定のシート名のデータを取得する場合、当該シートが存在していないブックがあるならば、ブックを開く前にシートの存在をチェックしたくなります。このような、ファイルを開かずにExcelの情報を取得したいという要望は多くあります。
VBA100本ノック 96本目：Accessデータを取得（マスタ結合&抽出）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
VBA100本ノック 97本目：Accessデータを取得（グループ集計）・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
SQL入門・DBとはSQLとは ・SQL入門の目次 ・SQLを使った既存サンプル
最後に注意点等
接続文字列等では大文字小文字が混在していますが、大文字小文字は区別されませんので問題ありません。
最後の;については、あっても無くても動きますが、書いておいた方が良いと思います。
全体を通しての注意点としては、
データに、'シングルクォーテーションを含む場合は、
それをエスケープするために、'シングルクォーテーションを2個重ねるようにします。
データを更新・追加する場合は、nullに注意してください。
Excelの空欄セルをデータとして使う時に、0バイト文字なのかnullなのか、DB仕様と合わせて記述に気を付けます。
更新・追加する場合は、トランザクション処理は必須だとお考え下さい。
さらにデータが多い時は、マルチプルインサートにすることも考えましょう。
これをバルクインサートという言い方もします、ただしDBによって構文がそれぞれ違ってきます。
またDBによりますが、
大抵はCSVから直接ロードする命令が用意されていて、これを使うとかなり高速で全件処理が可能ですので検討してみましょう。
最後の;については、あっても無くても動きますが、書いておいた方が良いと思います。
データに、'シングルクォーテーションを含む場合は、
それをエスケープするために、'シングルクォーテーションを2個重ねるようにします。
Excelの空欄セルをデータとして使う時に、0バイト文字なのかnullなのか、DB仕様と合わせて記述に気を付けます。
さらにデータが多い時は、マルチプルインサートにすることも考えましょう。
これをバルクインサートという言い方もします、ただしDBによって構文がそれぞれ違ってきます。
またDBによりますが、
大抵はCSVから直接ロードする命令が用意されていて、これを使うとかなり高速で全件処理が可能ですので検討してみましょう。
同じテーマ「マクロVBA技術解説」の記事
VBAでのSQLの基礎（SQL:Structured Query Language）
VBAで正規表現を利用する（RegExp）
VBAでメール送信する（CDO:Microsoft Collaboration Data Objects）
VBAでのOutlook自動操作
ADO(ActiveX Data Objects)の使い方の要点
特殊フォルダの取得（WScript.Shell,SpecialFolders）
参照設定、CreateObject、オブジェクト式の一覧
VBAのスクレイピングを簡単楽にしてくれるSelenium
VBA+SeleniumBasicで検索順位チェッカー作成
VBA+SeleniumBasicで検索順位チェッカー（改）
.Net FrameworkのSystem.Collectionsを利用
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- ADO(ActiveX Data Objects)の使い方の要点
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。