連載
» 2015年11月26日 05時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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)。

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

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

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

vba_exlaxs5_zu_02.png 図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のようになる。

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

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

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

著者紹介

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

kuniyasu.jpg

薬師寺国安事務所代表。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)。


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。