＠IT
アジャイル/DevOps
Coding Edge
Excelを入力画面にしてAccessデータベースを参照・...

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

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

» 2015年12月10日 05時00分公開

[薬師寺国安，PROJECT KySS]

※本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文でも、どちらでも指定できる。