連載
» 2015年12月10日 05時00分 UPDATE

VBA/マクロ便利Tips:Excelを入力画面にしてAccessデータベースを参照・更新・削除する際の便利テクニック (1/3)

業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、RecordsetのOpenメソッドでSQL文を使う方法や、MoveNext、Find、Deleteメソッド、EOFプロパティの使い方、Recordsetの各フィールドの参照方法などについて。

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

連載目次

※本Tipsの環境:Windows 8.1 Enterprise(64ビット)+Excel 2013+Access 2013


 今回は、「ExcelからAccessのデータを更新する」方法と「ExcelからAccessのデータを削除する方法」を中心にさまざまな連携テクニックを紹介する。

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

ExcelからAccessのデータを更新する

 Accessの「顧客管理テーブル」のデータをExcelシートに表示させ、シート上で各項目の値を変更して、「編集保存」ボタンをクリックすると、変更された内容がAccessの「顧客管理テーブル」に反映される処理を解説する。

Excelの入力セルとボタンを作る

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

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

 図1の「Accessデータの更新」のシートに、Accessの「商品管理テーブル」のデータを読み込むセルと、「Accessの商品管理データを読み込む」ボタンと、「編集保存」ボタンを作成しておく。また、項目名は事前に書式を設定して作成しておく(図2)。

 ボタンはExcelメニューの「挿入」→「図形」から「角丸四角形」を選択して作成している。

axs6_zu_02.png 図2 「Accessデータの更新」のシートに項目名と、ボタンを2つ配置した

Accessの「商品管理テーブル」のデータを読み込むVBAコード

 Excelメニューの「開発」→「Visual Basic」と選択して、「VBE」(Visual Basic Editor)を起動する。プロジェクト内のModule1に、リスト1のコードを記述する。

Sub Accessの商品管理データを読み込む()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim mySQL As String
  Dim dbFile As Variant
  Dim mySheetName As Variant
  Dim i As Integer
  
  dbFile = "K:\Access_Excel VBA Tips\商品管理データベース.accdb"
  myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  myCon.Open
  
  mySQL = "SELECT * FROM 商品管理テーブル"
  
  myRecordSet.Open mySQL, myCon, adOpenDynamic
  
  mySheetName = ActiveSheet.Name
  
  i = 2
  
  Do Until myRecordSet.EOF
    With Worksheets(mySheetName)
      .Cells(i, 1).Value = myRecordSet!商品NO
      .Cells(i, 2).Value = myRecordSet!商品名
      .Cells(i, 3).Value = myRecordSet!単価
      .Cells(i, 4).Value = myRecordSet!在庫
    End With
      i = i + 1
      myRecordSet.MoveNext
  Loop
  myRecordSet.Close
  Set myRecordSet = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub
リスト1 Accessの「商品管理テーブル」のデータを読み込むコード

 12行目までのコードはTips「ExcelからOLE DBプロバイダーを使ってAccessに接続する基本」と同様なので、省略する。以下、コードについては、異なる部分のみ解説する。

 なお今回は、「Data Source」に変数「dbFile」に格納されている値を指定している(9〜10行目)。

ADODB.Recordset.OpenでSELECT文のSQLを指定

 文字列型の変数「mySQL」に、「商品管理テーブル」の全てのレコードを取得するSQL文を指定する(13行目)。

 ADODB.Recordset.Openメソッドで、データソース内のテーブルからデータの読み書きを行う準備をするが、Tips「ExcelからOLE DBプロバイダーを使ってAccessに接続する基本」でのADODB.Recordset.Openメソッドの説明と異なり、「Source」にはSELECT文のSQLを指定している(15行目)。

 このように、「Source」にはテーブル名でもSQL文でも、どちらでも指定できる。

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

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


レコードの最後を表す「ADODB.Recordset.EOF」プロパティ

 変数「i」を「2」で初期化する(19行目)。変数iは行番号を表すので、Accessデータの読み込みを「2行目」から行う意味で「2」で初期化している。

 「商品管理テーブル」のレコードの最後(myRecordSet.EOFプロパティ)まで、22〜29行目の処理を繰り返す。

ADODB.Recordsetは「!」+フィールド名でフィールドを指定できる

 アクティブなシートの変数i行目の「1」列目に「商品NO」フィールドの値を指定する(23行目)。

 ADODB.Recordsetの後の「!」(感嘆符)は、「商品NO」フィールドがSQL文で指定したADODB.Recordsetの構成要素であることを表している。

 同様に、2列目には「商品名」、3列目には「単価」、4列目には「在庫」とそれぞれのフィールドの値を指定する(24〜26行目)。

次のレコードに移動する「ADODB.Recordset.MoveNext」メソッド

 変数iの値を1ずつ増加させ(28行目)、myRecordSet.MoveNextメソッドで、次のレコードに移動する(29行目)。

 これで、「商品管理テーブル」に登録されているデータが、順次Excelのセルに表示されるようになる。

Accessデータの読み込みを実行

 実行すると、図3のようになる。

axs6_zu_03.png 図3 Excelの「Accessデータの更新」シートに「商品管理テーブル」のデータが読み込まれた
       1|2|3 次のページへ

Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

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

RSSについて

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

メールマガジン登録

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