ExcelからOLE DBプロバイダーを使ってAccessに接続する基本：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、ADODB.ConnectionやRecordSetのOpenメソッドを使ってデータソースに接続し、データを追加する方法などについて解説します。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013＋Access 2013

　今回は、ExcelからAccessのテーブルにデータを追加する方法を解説する。

　Excelシート上に「商品NO」「商品名」「単価」「在庫」を入力するセルと、「追加」ボタンを配置し、データを入力して「追加」ボタンをクリックすると、1件のレコードが、Accessの「商品管理テーブル」に追加される。順次データを入力して「追加」ボタンをクリックするごとに、順次Accessのテーブルにレコードが追加されていく。

　使用するテーブルは、TIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」で作成しておいた、「商品管理テーブル」だ。「商品管理データベース.accdb」をクリックして、Accessを起動しておいてほしい。

Excelの入力セルを作る

　まずは、「商品管理データベース.xlsm」をクリックしてExcelを起動する。シートの下に表示されている「○に＋」のアイコンをクリックして、「Sheet1」を追加し、名前を「Accessにデータ追加」としておく（図1）。

図1　「Sheet1」を追加し、名前に「Accessにデータ追加」としておく

　図1の「Accessにデータを追加」のシートにAccessに追加するデータ用の入力画面を作成する。

　「商品NO」「商品名」「単価」「在庫」の入力セルと「追加」という「ボタン（角丸四角形）」を配置する（図2）

図2　ExcelのシートにAccessに追加するデータを入力するセルと「追加」ボタンを配置した

VBEを起動してVBAを記述する

　Excelメニューの「開発」→「Visual Basic」と選択して「VBE（Visual Basic Editor）」を起動する。プロジェクト内に、以前「Access起動」のコードを記述する際に、Module1がすでに作成されているので、この中にリスト1のコードを記述する。

Sub Accessにデータ追加()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim myString As String
  
  myString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\Access_Excel VBA Tips\商品管理データベース.accdb"
  myCon.Open ConnectionString:=myString
  myRecordSet.Open Source:="商品管理テーブル", ActiveConnection:=myCon, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
  
  If IsNumeric(Range("C4").Value) = False Or IsNumeric(Range("C5").Value) = False Or Range("C4").Value = "" Or Range("C5").Value = "" Then
    MsgBox ("単価または在庫の値が不正です｡")
    Exit Sub
  End If
  myRecordSet.AddNew
  myRecordSet.Fields("商品No").Value = Range("C2").Value
  myRecordSet.Fields("商品名").Value = Range("C3").Value
  myRecordSet.Fields("単価").Value = Range("C4").Value
  myRecordSet.Fields("在庫").Value = Range("C5").Value
  
  myRecordSet.Update
  myRecordSet.Close
  myCon.Close
  Set myRecordSet = Nothing
  Set myCon = Nothing
  
  MsgBox ("Accessの商品管理に1件のデータを追加しました。")
End Sub

リスト1　Excelから入力したデータをAccessに追加するコード

データソースへの接続オブジェクト「ADODB.Connection」

　ADODB.Connection型の変数「myCon」を宣言し、新しいインスタンスのmyConオブジェクトを作成する（2行目）。

　ADODB.Connectionオブジェクトは、ADO（ActiveX Data Objects）を使ってデータベースを操作するための基本オブジェクトだ。データソースへの接続や、レコードセットの作成には、このコネクションオブジェクトを使用している。

データソースのレコードセット「ADODB.Recordset」オブジェクト

　ADODB.Recordset型の変数「myRecordSet」を宣言し、新しいインスタンスmyRecordSetオブジェクトを作成する（3行目）。ADODB.Recordsetは、ADOを用いたデータの読み書きを行うオブジェクトだ。

OLE DBプロバイダーを使ってデータソースに接続

　次に、OLE DBプロバイダーを使ってデータソースに接続する（6行目）。OLE DB（Object Linking and Embedding DataBase）プロバイダーとは、AccessやSQL Serverなどのデータべ―スにアクセスする機能を提供するAPIだ。

　書式は下記の通りだ。これは、Accessデータベース用のOLE DBプロバイダーの接続文字列になる。この書式はAccess 2000以降で使用できる。

Accessデータベース用のOLE DBプロバイダーの接続文字列の書式

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=｛Accessファイルへの完全なパス｝

　上記書式にのっとって4行目で宣言した変数myStringに下記のように記述して、「商品管理データベース.accdb」への接続文字列を変数myStringに格納しておく。

myString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\Access_Excel VBA Tips\商品管理データベース.accdb"

データソースと接続する「ADODB.Connection.Open」メソッド

　次に、ADODB.Connection.Openメソッドを使って下記の書式でConnectionStringに接続文字列を指定してデータソースと接続する（7行目）。

ADODB.Connection.Openメソッドの書式

ADODB.Connection.Open ConnectionString:=｛接続文字列｝

　この書式を使って下記のように記述し、myStringに格納されている接続文字列をConnectionStringに指定して、「商品管理テーブル」に接続する。ConnectionStringには、データベースやデータソースに接続するための接続文字列を指定する

myCon.Open ConnectionString:=myString

データソース内のテーブルを開く「ADODB.Recordset.Open」メソッド

　次に、RecordSet.Openメソッドで、データソース内のテーブルからデータの読み書きを行う準備をする（8行目）。書式は下記の通りだ。

ADODB.Recordset.Openメソッドの書式

ADODB.Recordset.Open Source:="｛テーブル名｝", ActiveConnection:=｛データソースへの接続が完了しているコネクションオブジェクト｝, CursorType:=｛テーブルやクエリを開くときのカーソルの種類｝, LockType:=｛排他制御の方法｝

　「CursorType」には、表1の値を指定する。

表1　CursorTypeに指定する値

定数	説明
adOpenDynamic	動的カーソル
adOpenForwardOnly	前方専用カーソル（既定値）
adOpenKeyset	キーセットカーソル。他のユーザーが追加したレコードは表示できない
adOpenStatic	キーセットカーソル。データの検索、リポートの作成に使用する、レコードの静的コピー
adOpenUnspecified	カーソルの種類を指定しない
参考「ADO プログラマーズ リファレンス＞ADO API リファレンス＞ADO 列挙定数＞CursorTypeEnum（MSDN）」

　「LockType」には表2の値を指定する。

表2　LockTypeの値

定数	説明
adLockBatchOptimistic	共有的バッチ更新
adLockOptimistic	レコード単位の共有ロック
adLockPessimistic	レコード単位の排他的ロック
adLockReadOnly	読み取り専用レコード
adLockUnspecified	ロックの種類を指定しない

　ここでは、「Source」には「商品管理テーブル」を、「ActiveConnection」には変数myConオブジェクトを、「CursorType」には、「AdOpenKeySet」を、「LockType」には「adLockOptimistic」を、それぞれ指定している。

ADODB.RecordsetオブジェクトのAddNew、Update、Closeメソッド、Fieldsコレクション

　「Accessにデータ追加」シートのセル「C4」と「C5」に入力された値が数値ではなかったり、未入力であったりした場合は警告メッセージを表示して処理を抜ける（10〜13行目）。

　AddNewメソッドでレコードを追加し、Fieldsコレクションに「商品NO」「商品名」「単価」「在庫」のフィールドを指定して、それぞれのフィールドの（Valueプロパティ）に、Excelのセルに入力された値を指定する（14〜18行目）。

　Updateメソッドでレコードを更新し（20行目）、レコードセットとコネクションを閉じ（21〜22行目）、全ての関連付けから解放する（23〜24行目）。最後に、データを追加した旨のメッセージを表示する（26行目）。

実行結果

　図2の「追加」ボタンを選択して、マウスの右クリックで表示されるメニューから、「マクロの登録」を選択する。すると、「Accessにデータ追加」のマクロ名が表示されるので、これを選択して「OK」ボタンをクリックする。

　追加するデータを入力して「追加」ボタンをクリックすると、図3のようになる。

図3　Excelの「Accessにデータ追加」シートから入力したデータが、「商品管理テーブル」に追加された

メインがExcelでも、Accessを使うときもある

　今回は、これで終わりだ。「日ごろメインでExcelを使って業務をしている際、急にAccessのテーブルにデータを追加する必要に迫られた」「Excelにもデータを入力途中なので、いちいちAccessを起動するのは面倒だ」という場合、同じExcelの画面からAccessのテーブルにデータが追加できれば、非常に便利ではないだろうか。ぜひ皆さんの職場でも大いに利用していただきたい。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。