Copyright © 2015-2020 いつも隣にITのお仕事 All Rights Reserved. エクセルVBAからAccessデータベースを操作する方法についてシリーズでお伝えしています。, さて、今回からいよいよ実際にエクセルVBAからAccessデータベースに接続をしていきたいと思います。, 都道府県ごとの面積や人口、区市町村数などをまとめたデータです。テーブル名は「データ」という名前です。, このテーブルを含むAccessデータベースファイルを「test.accdb」というファイル名で、今回のエクセルVBAを記述したファイルと同じフォルダに格納しました。, Accessデータベースと接続をしてやり取りをする際には、ADODB.Connectionオブジェクトを使います。, ADOとは、Microsoftが提唱しているデータアクセス技術のことである。 ADOで特定フィールドのデータを取得する方法. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法, 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す, 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法, エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について, 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する, エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法, エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法, エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について, エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方, エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方, エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出, 詳解! 接続をして切断するだけの超簡単プログラムです。. Northwind.mdb には画像データが格納されていますが、今回の方法でExcelに書き出したとき、画像などのバイナリデータが抜け落ちてしまうようです。 また、真偽を示す True, False は、Excelに出力した段階で数値の 1, 0 になります。 ActiveX Data objects【アクティブエックスデータオブジェクツ】(以下ADO)で、特定のフィールドのデータを取得するには、レコードのフィールドを表すField【フィールド】オブジェクトのValue【バリュー】プロパティを使用します。, データを取得するフィールドを指定するには、Recordset【レコードセット】オブジェクトのFields【フィールズ】プロパティを使用してField【フィールド】オブジェクトを参照します。, 外部データベースの特定のフィールドを表すField【フィールド】オブジェクトを参照するには、Recordset【レコードセット】オブジェクトのFields【フィールズ】プロパティを使用します。, 外部データベースの特定のフィールドの値を取得または設定するには、Field【フィールド】オブジェクトのValue【バリュー】メソッドを使用します。, 【取得】 オブジェクト.Value 【設定】 オブジェクト.Value = 設定値, 外部データベースのカレントレコードの位置がデータベースの範囲を超えたかをブール型の値で返します。値の取得のみ可能です。 BOFは「Begin Of File【ビギインオブファイル】」の略で、カレントレコードの位置が外部データベースの先頭のレコードより前にあるかないかを判定する場合に使用します。, EOFは「End OF File【エンドオブファイル】」の略で、カレントレコードの位置が外部データベースの最後のレコードより後にあるかないかを判定する場合に使用します。, Recordset【レコードセット】オブジェクト内のカーソルを移動するには以下の表のRecordset【レコードセット】オブジェクトの各メソッドを使用します。, オブジェクト変数.MoveNext オブジェクト変数.MovePrevious オブジェクト変数.MoveFirst オブジェクト変数.MoveLast, 3行目【Dim レコード As New ADODB.Recordset】 Dim【デム】ステートメントでNew【ニュー】キーワード使用して外部データベースのレコードを参照するRecordset【レコードセット】オブジェクトのインスタンスを生成してオブジェクト変数「レコード」に格納します。, 4行目【Dim i As Long】 繰り返し処理の中でワークシートの行番号を格納する変数iを長整数型(Long)で宣言します。, 5行目~7行目【コネクション.Open ConnectionString:= _ “Provider=Microsoft.ACE.OLEDB.12.0;” & _ “Data Source=C:¥Users¥Desktop¥Database1.accdb;”】 Connection【コネクション】オブジェクトのOpen【オープン】メソッドを使用して外部データベースの接続をします。, 8行目【レコード.Open Source:=”商品マスター”, ActiveConnection:=コネクション】 Recordset【レコードセット】オブジェクトのOpen【オープン】メソッドを使用して外部データベースのレコードを参照します。, 10行目【Do Until レコード.EOF】 Do Loop【ドウ ループ】 ステートメントを使用して繰り返し処理の始まりです。繰り返しの条件をUntil【アンティル】の「~ではない間」に設定してEOF【エンドオブフィールド】プロパティがTrueではない間(外部データベースの最後のレコードまでの間)繰り返す条件を設定します。, 11行目【Cells(i, 1).Value = レコード.Fields(“商品名”).Value】 1列目の変数i行目のセルにValue【バリュー】プロパティで取得した外部データベースの「商品名」フィールドの値を代入します。, 12行目【レコード.MoveNext】 Recordset【レコードセット】オブジェクトMoveNext【ムーブネクスト】メソッドを使用して外部データベースのカーソルの位置を次のレコードに移します。, 13行目【i = i + 1】 セルの行番号が格納されている変数iに1を加算してセルの次の行を参照する準備をします。, 14行目【Loop】 10行目からここまでの処理を外部データベースのレコードの数(EOFがTrueになるまで)繰り返してワークシートに「商品名」フィールドのデータを代入します。, 15行目【レコード.Close: Set レコード = Nothing】 Recordset【レコードセット】オブジェクトのClose【クローズ】メソッドでレコードの参照を切断し、Nothing【ナッシング】を代入してメモリー領域を解放します。, 16行目【コネクション.Close: Set コネクション= Nothing】 Connection【コネクション】オブジェクトのClose【クローズ】メソッドで外部データベースの接続を切断し、Nothing【ナッシング】を代入してメモリー領域を解放します。, 以上で、ADOで外部データベースの特定フィールドのデータを取得する方法についての解説を終了します。ありがとうございました。, 外部データベースの特定のフィールドを表すField【フィールド】オブジェクトを指定します。, 外部データのレコードを表すRecordset【レコードセット】オブジェクトのインスタンスが格納されている, 外部データのレコードを表すRecordset【レコードセット】オブジェクトのインスタンスが格納されているオブジェクト変数を指定します。, 8行目【レコード.Open Source:=”商品マスター”, ActiveConnection:=コネクション】, 11行目【Cells(i, 1).Value = レコード.Fields(“商品名”).Value】. エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。 A‘̏d‚©‚ç‚È‚éƒf[ƒ^ƒx[ƒX, TableBF@IDAE‹ÆA–Æ‹–‚Ì—L–³A¶”NŒŽ“ú‚©‚ç‚È‚éƒf[ƒ^ƒx[ƒX, ADOF@C:\Program Files\Common Files\System\ado\msado15.dll, ADOXF@C:\Program Files\Common Files\System\ado\msadox.dll, TblAry(0)(2)F@DateCreatedAƒe[ƒuƒ‹‚̍쐬“úŽž, TblAry(0)(3)F@DateModifiedAƒe[ƒuƒ‹‚̍XV“úŽž. エクセルVBAとAccessを連携するメリットと注意点についてお伝えしました。 さて、今回からいよいよ実際にエクセルVBAからAccessデータベースに接続をしていきたいと思います。. 引用:Weblio辞書-ADO, ADOはその目的によりさらにいくつかに区別されますが、そのうちデータの操作をする場合に使用するのがADODBとなります。, さらにさらにADODBはその目的に応じていくつかのオブジェクトを使い分けることになりますが、AccessをはじめとしたOracle、MySQLなどの各種データベースの接続をする際にはADODB.Connectionオブジェクトを使います。, このADODB.ConnectionオブジェクトがAccessデータベースとエクセルVBAの橋渡しの役割を果たします。, ちなみに、別記事でUTF-8のCSVを取り扱う際にはADODB.Streamオブジェクトを使うということをお伝えしていましたね。, その前に、ADODB.Connectionを使うためには、ADODBライブラリを追加する必要があります。, Visual Basic Editorのメニューから「ツール」→「参照設定」を開いて、ライブラリの中から「Microsoft ActiveX Data Objects x.x Library」を見つけてチェックを入れます。執筆時のバージョンは「2.8」ですが、最新のものを選べばOKです。, では、実際にAccessデータベースに接続するVBAプログラムを書いてみましょう。, まず、7行目でADODB.Connectionオブジェクトを生成して、adoCnという変数で取り扱えるようにセットをしています。, これは決まり文句ですので、Accessデータベースを取り扱うときにはコピペをしてプログラム冒頭入れておきましょう。, これで指定したAccessデータベースへの接続がオープン状態になり、データベースの操作ができるようになります。, ProviderとData Sourceという二つのパラメータがありますが、Accessデータベースの場合はProviderは「Microsoft.ACE.OLEDB.12.0」で固定です。Data SourceにAccessデータベースのファイルをフルパスで指定してあればOKです。, 今回は、エクセルVBAのファイルと同じフォルダにあるという前提ですので、ThisWorkbook.Pathを活用して指定をしています。, データベースの操作が終わったら、データベースへの接続をクローズします。プログラムの10行目です。, この二行もデータベースを利用した後はお決まりフレーズとして入れて頂ければOKです。, 実際にデータベースに接続できているのか…?と心配になりますが、できています。(たぶん), 何にも起きないつまらないプログラムを紹介してしまいました。こんなことは初めてです。, ですが、エクセルVBAでAccessデータベースに接続する際には必須の手続きになりますので、ぜひしっかり押さえておいて頂ければと思います。, ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。, エクセルと連携するデータベースの第一候補であるMicrosoft Access。今回は、なぜエクセルとAccessを連携するのが良いのか、またその際の注意点についてお伝えしたいと思います。, エクセルVBAのLine Input命令でUTF-8のCSVファイルを取り込むと文字化けを起こします。今回は、ADODB.Streamを使ってUTF-8のCSVファイルを取り込む方法についてお伝えします。, Set オブジェクト変数 = CreateObject(“ADODB.Connection”), 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム, エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。, エクセルVBAで請求データ一覧から請求書を自動で作成する方法のシリーズ。今回は、Enumステートメントで定義する「列挙体」というものを使って、シートの列の挿入も簡単に対応する方法をお伝えします。, Outlookのタスクを、VBAで登録する方法をご紹介しています。やらないといけないこと、やりたいことをメモや付箋に書いておくのもいいですが、Outlookのタスクに登録しておけば、期限の手前でアラームを鳴らしてくれたり、タスクをやったか、やっていないかまで管理することができて便利です。, エクセルVBAでCSVを爆速で取り込むQueryTableオブジェクトには様々なプロパティが存在します。このプロパティを使いこなすことであらゆる種類のCSVをシンプルなソースコードで取り込むことができます。, 「初心者でもわかるエクセルVBAのクラスモジュール」をテーマにシリーズ連載をしております。今回は、エクセルVBAで自作のコレクションに要素を追加するAddメソッドを作成する方法について見ていきます。, エクセルVBAでIEを操作する初心者向けのシリーズの6回目です。今回はタグ名で要素をゴソっと取得するgetElementsByTagNameの使い方です。様々なHTML要素を簡単に取得できます。, エクセルVBAでPDF出力や印刷をする際のテクニックについて引き続きお伝えしていきますが、今回はエクセルVBAで全てのシートをまとめてプレビューまたはPDF出力をする方法についてお伝えします。, 当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。, マネーフォワードクラウド請求書の請求書一覧の全件をスプレッドシートに書き出すスクリプト, GASでマネーフォワードクラウド請求書の請求書一覧をスプレッドシートに書き出す方法. ActiveX Data objects【アクティブエックスデータオブジェクツ】(以下ADO)で、特定のフィールドのデータを取得するには、レコードのフィールドを表すField【フィールド】オブジェクトのValue【バリュー】プロパティを使用します。 GoogleAppsScript完全入門 ~GoogleApps & G Suiteの最新プログラミングガイド~, VBAやGoogle Apps Scriptのツール開発と研修をセットで依頼される企業が増えている理由, ADODB.Connectionオブジェクトの接続をオープンして目的のAccessデータベースに接続. では行ってみましょう!