連載

2015年12月10日

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

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

[薬師寺国安，PROJECT KySS] PC用表示関連情報

LINE

Hatena

連載目次

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

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

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

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

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

図3　Excelの「Accessデータの更新」シートに「商品管理テーブル」のデータが読み込まれた

ExcelからAccessのデータを削除する

　　　　　　 | 次のページへ