【MS Access】ADO入門|非連結フォームとADOで作る検索・更新処理のサンプルコードと解説

今回の記事では、Microsoft Accessの非連結フォームと、ADOのレコードセットを活用して、データベースのデータを取得してフォーム内に表示する一連の実装方法と、表示したデータを編集してデータベースへ更新を掛けるまでの実装方法を解説していきます。

今回の記事で紹介する「非連結フォーム」と「レコードセット」の扱い方を理解していただければ、Accessでも本格的な業務アプリが作れます。
是非参考にしてください。

今回の記事の概要
連結フォームと非連結フォームの特徴
非連結フォームとADOのレコードセットの使い方
コンボボックスやリストボックスにレコードセットを代入する方法
1. コンボボックスやリストボックスのデータとしてレコードセットを使用する方法
最後に

今回の記事の概要

Accessでデータベースと連携してフォームに値を表示する実装方法は色々あります。
例えば以下の方法です。

連結フォーム機能を使用した実装。
VBA内でDLookUp関数やDCount関数などのテーブル参照系組み込み関数を使用した実装。
DAOやADOを利用してSQLを発行し取得したデータをレコードセットに格納して実装。

上記以外にも方法はありますが、上記の例で言えば、下に行くほど上級者向きの実装です。

Accessで業務アプリケーションを開発する場合、誰しも最終的にはDAOやADOとレコードセットを組み合わせた実装をできるようにするべきだと考えています。

そこで、DAOやADOとレコードセットを使用した実装経験がない人を対象に、当実装方法のメリットや具体的な実装方法について紹介していきます。

尚、多少語弊はありますが、一般的にDAOとADOは以下のように使い分けます。

DAO：自身のテーブル群のデータ操作や、他Accessファイルとの接続や各種データ操作で使用。

ADO：Access以外のRDBMSとの接続や各種データ操作で使用。

厳密にはADOでも自身のテーブル群や自身以外のAccessファイルに対してのデータ操作が可能ですが、Accessに対する操作だけであれば、DAOの方が使い勝手が良いです。

今回の記事ではDAOは扱わず、より汎用的である「ADO」を用いて他RDBMSへ接続してフォームにデータを表示する各種実装方法を紹介していきます。

また、フォームのテキストボックスに対してデータを表示するだけではなく、フォーム上で編集したデータをデータベースに反映させる方法や、コンボボックスやリストボックスにレコードセット使用する場合など、知っておいたほうがよい実装例もいくつか紹介する想定です。

連結フォームと非連結フォームの特徴

まずは、Access固有の機能である「連結フォーム」と、今回の記事で使い方を解説する「非連結フォーム」の違いについて説明していきます。

両フォームの違いを表したイメージ画像は以下になります。

事項で具体的に両フォームの違いを説明していきます。

「連結フォーム」とは

Accessはプログラミング経験がなくても簡単な業務アプリケーションが作れます。
その特徴に大きく貢献している機能が「連結フォーム」です。

Accessファイル内で管理しているテーブルやクエリ、外部データベースを参照する「リンクテーブル」などの表をフォームと紐付けてあげることで、VBAでのコードを記述することなく、表のレコードをフォーム内のテキストボックスなどのコントロールに表示することができる機能です。

フォームが特定のテーブルやクエリ結果の表と連結し、そのフォーム内の各テキストボックスのプロパティには、その連結したテーブルやクエリ結果の表の、各フィールドを指定することができます。

それをすることで、そのフォームを読み込む際に連結対象のテーブルやクエリも読み込まれ、そのフォームのテキストボックスには、その読み込んだテーブルやクエリで指定したフィールドの値が表示されます。

フォームの各コントロールとテーブルやクエリの値は完全に連結しているため、テキストボックスの値を書き換えることで、連結対象のレコードの値も書き換わります。

また、「フォームウィザード」機能では、作成画面に沿って連結対象のテーブルやクエリを選択し、そのフォームに表示するフィールドを指定していくことで、ラベルやテキストボックスも設置された連結フォームを自動的に作成してくれます。

フォームウィザード画面

車で言えば「オートマチック車」です。

データベースのデータを参照してフォームに値を表示しようとした場合、本来はプログラミング技術が不可欠ですが、Accessであればコードを書くことなくマウスの操作だけで行うことができます。

アプリケーション開発の初心者にとっては、非常に便利な機能です。

「非連結フォーム」とは

前述した「連結フォーム」ではテーブルやクエリ結果の表とフォームが紐付いており、各コントロールのプロパティを設定することで、連結先のテーブルなどのレコードの値をフォームに表示できると説明しましたが、「非連結フォーム」では、名前のとおり、このフォームはテーブルやクエリと連結されていません。

「非連結フォーム」内に作られたテキストボックスなどのコントロールに対して、データベースの値を表示させるには、VBAを用いてデータベースからデータを取得し、取得したデータをどのテキストボックスなどのコントロールに表示させるかを、VBAのコードから指定する実装が必要になります。

また、フォーム内の各コントロールとテーブルとは連結していないため、フォーム内のテキストボックスの値を書き換えても、テーブルに反映はされません。

テキストボックスの値を書き換えて、書き換えた値をテーブルに反映させる場合は、VBAでコードを書く必要があります。

車で言えば「マニュアル車」です。

連結フォームのようにGUIだけで簡単に作れるものではなく、VBAに関する本格的な知識も必要になってきますが、Accessをより高度に活用するためには必ず必要になる技術です。

「連結フォーム」と「非連結フォーム」のメリットデメリット

当項では、「連結フォーム」「非連結フォーム」のメリット、デメリットを紹介します。

「連結フォーム」のメリットデメリット

メリット

VBAでコードを記述しなくても、データベースを参照するフォームを作成することができる。
連結対象のテーブルやクエリを選択し、どのフィールドをどのコントロールに紐付けるかをGUIで設定できる。
「フォームウィザード」を活用することで、連結フォーム作成作業の大半を自動化できる。

デメリット

テキストボックスの値を書き換えることで、連結先のフィールドの値も書き換わってしまう(トランザクション管理ができない)。
連結先に指定したテーブルやクエリのレコードの読み込みが終わらないとフォームが表示されず、フォームの表示に時間が掛かる。
フォームからテーブルを参照や更新する場合の細かい制御ができない。

連結フォームは、ノーコード、ローコードでデータベースと連携できるフォームが作れる初心者向けの機能であり、一般的な業務システムで実装されるような細かい制御はできません。
特に、正確なデータ更新を求められる業務システムにおいて、「トランザクション管理ができない」点は致命的な欠点です。

「トランザクション管理」ってなに？という場合は、当ブログで過去に掲載した関連記事をご参照ください。

また、フォームの読み込みに時間が掛かるのも大きな欠点です。

フォーム作成直後は短時間で読み込めていたフォームであっても、連結先テーブルのデータが蓄積され、知らない間に業務に支障が出るほどフォームの読み込みに時間が掛かるようになってしまったケースは多く見受けられます。

また、連結フォームを使用する場合、テーブルをそのまま結合するケースより、何らかの条件で絞り込んだクエリの表を指定するケースの方が多いのですが、Accessのクエリは仕様上遅く、大きなデータを持つテーブルを参照したクエリを連結することは極力避けるべきです。

Accessのクエリが遅い原因についても、過去に当ブログで記事にしております。

良ければ併せてご一読ください。

「非連結フォーム」のメリットデメリット

メリット

データベースのデータを参照する処理を全てVBAで実装することで細かい制御ができる。
フォームの読み込み時にテーブルやクエリを読み込みを待つことがなくなり高速になる。
フォームを介してデータを更新する場合にトランザクション処理を実装できる。

デメリット

データベースへの接続やレコードの取得などの一連の処理をすべてVBAで記述する必要がある。
フォーム内のテキストボックスの値を書き換えても実データは更新されない。

連結フォームのデメリットは非連結フォームですべて解消されます。

また、デメリットについては、データベースの接続やテーブルの参照、更新など処理をすべてVBAを書いて実装する必要があり、技術的な難易度が一気に高くなると言えます。

その代わり、フォームの読み込み速度は改善し、データの参照処理や更新処理の信頼性は向上します。

中の人

非連結フォームは作成の手間が掛かりますが、信頼性の高い業務アプリケーションの作成が可能です。

習得する価値はあるので、積極的にチャレンジしてみましょう！

非連結フォームとADOのレコードセットの使い方

当項では、当記事の主題である、非連結フォームとADOのレコードセットを使用してデータをフォームに表示させる場合の実装方法の解説や、フォーム内で編集したデータをデータベースに反映させる処理を解説していきます。

今回の記事で使用するサンプルフォーム

今回の記事では以下のレイアウトの非連結フォームに対して、ADOを使用してデータ取得処理及び更新処理を組み込む場合の実装例を紹介していきます。

サンプルフォーム(フォームビュー)

データを取得してテキストボックスに表示する一連の処理は、「検索」ボタン押下時イベントのなかに実装し、データベースへの反映処理は「更新」ボタン押下時のイベント内に実装します。
当フォームの「デザインビュー」は以下です。

サンプルフォーム(デザインビュー)

尚、当サンプルフォーム内の各コントロールは以下の設定とします。

番号	コントロール名	コントロール種類	説明
①	txt_SearchID	テキストボックス	検索対象のCustomerIDを入力します。
②	btn_Search	コマンドボタン	①のテキストボックスに入力されたIDで検索処理を実行します。
③	txt_ID	テキストボックス	検索結果のCustomerIDを表示します。使用不可(編集不可)とします。
④	txt_Name	テキストボックス	検索結果のCustomerNameを表示します。使用可能(編集可)とします。
⑤	txt_Address	テキストボックス	検索結果のCustomerAddressを表示します。使用可能(編集可)とします。
⑥	btn_Update	コマンドボタン	編集されたtxt_Name又はtxt_Addressの値を元にデータベースに更新する処理を実行します。

ADOでレコードセットを取得する基本知識

Access VBAでADOを使用して、データベースへ接続してレコードセットを取得する一連の基本的な知識を解説します。

尚、当記事で詳細な内容まで解説をしてしまうと記事が冗長になるため、過去に当ブログで公開した以下の記事のリンクをご参照ください。

今回の記事では、上記のリンク内で解説している「ADODB.Connection」オブジェクトの「Execute」メソッドを使用してデータを取得する実装で解説していきます。

また、上記のリンクのなかで解説している参照設定も入れてください。
今回の記事で紹介するサンプルコードでは、ADOで使用するライブラリを予め参照設定で指定(事前バインディング)されていることを前提とします。

参照設定に追加するライブラリは「Microsoft ActiveX Data Objects 2.8 Library」です。

当記事のサンプルコードで使用しているデータベースへの「接続文字列」はSQL Server(ODBC)用の書式です。
上記のリンクでは、接続先RDBごとの接続文字列も紹介しているため、詳しくはそちらをご確認ください。

レコードセット取得処理と非連結テキストボックスへの値の表示

当項では、今回のサンプルフォームに配置した「検索」ボタン押下時のイベントに組み込む、レコードセットの取得処理と、取得したレコードセットをフォーム内のテキストボックスに表示させる処理を紹介します。

尚、当サンプルコードでは、入力値のチェック処理などの細かい制御を省略しております。
本来は、テキストボックスの入力値に対して、数値や文字列などの判定処理など細かい制御も必要になるため、ご使用される環境に併せて処理を追加してください。

  Private Sub btn_Search_Click()

  Dim objCon As ADODB.Connection
  Dim ConString As String
  Dim strSQL As String
  Dim objRs As ADODB.Recordset

      '画面の初期化
      Me.txt_ID.Value = ""
      Me.txt_Name.Value = ""
      Me.txt_Address.Value = ""

      'データベースと接続します。
      Set objCon = New ADODB.Connection
      ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"
      objCon.Open ConString
      'SQLを生成して実行し、レコードセットを取得します。
      strSQL = "SELECT * FROM Customer WHERE CustomerID = " & Me.txt_SearchID
      Set objRs = objCon.Execute(strSQL)
      'レコードセットの存在チェックをします。
      If objRs.EOF Then
          'レコードセットがEOF=存在しない場合はメッセージを表示します。
          MsgBox "対象のIDは存在しません。"
      Else
          'レコードセットの値をテキストボックスに代入します。
          Me.txt_ID.Value = objRs("CustomerID").Value
          Me.txt_Name.Value = objRs("CustomerName").Value
          Me.txt_Address.Value = objRs("CustomerAddress").Value
      End If
      'ADODBの各オブジェクトを閉じて破棄します。
      objRs.Close
      objCon.Close
      Set objRs = Nothing
      Set objCon = Nothing

  End Sub

上記のコードについて補足説明をしていきます。

15行目では接続文字列を生成して変数に代入しています。

ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"

こちらはご使用されている環境ごとに設定値が変わります。
適切な値を設定してください。

18行目では、SQL文字列を生成して、変数に代入しています。

strSQL = "SELECT * FROM Customer WHERE CustomerID = " & Me.txt_SearchID

生成したSQL文は、接続先RDBに対して文字列のまま渡しますが、今回のサンプルコードでは、テキストボックス「txt_SearchID」で入力された値をSQL文に対して付与しています。

今回のコードでは、列「CustomerID」は数値が入っている想定でコードを作成していますが、もしアルファベットなどの文字列型の値をSQL文の検索条件(WHERE以降)に付与する場合は以下のように書きます。

strSQL = "SELECT * FROM Customer WHERE CustomerID = '" & Me.txt_SearchID & "'"

シングルクォーテーションで囲むことで、SQLでは「文字列の塊」として扱われます。
このコードで生成したSQL文は以下になります。

strSQL = "SELECT * FROM Customer WHERE CustomerID = 'txt_SearchIDに入力した値'"

20行目では、RecordsetオブジェクトのEOFプロパティを使用して、RDBからデータが取れたか否かを判定しています。

If objRs.EOF Then

EOFプロパティは、Recordsetオブジェクトの現在のカーソルが最終行の次に到達すると、Trueになります。

カーソルを動かす前からEOFプロパティがTrueの場合は、そのレコードセットにはレコードが存在しないと見なすことができます。

レコードセットにレコードが存在しない状態で、23行目以降のように、レコードセットから値を取り出そうとするとエラーになるため、当コードのように、必ずRecordsetオブジェクトにレコードが入っているかを確認することが必要です。

取得したレコードセットを使用して、25行目から、レコードセットの列ごとの値を取り出して、テキストボックスの「Value」プロパティに代入しています。

Me.txt_ID.Value = objRs("CustomerID").Value
Me.txt_Name.Value = objRs("CustomerName").Value
Me.txt_Address.Value = objRs("CustomerAddress").Value

今回の記事で紹介する「非連結フォーム」では、フォームに配置した各コントロールは、クエリやテーブルの列と連携していないため、明示的にコード上からテキストボックスの「Value」プロパティにレコードセットの列を指定して紐付てあげる必要があります。
非連結フォームを扱う場合の重要なポイントです。

また、レコードセットの列ごとの値を取り出す際には、以下のような書き方も可能です。

Me.txt_ID.Value = objRs(0).Value
Me.txt_Name.Value = objRs(1).Value
Me.txt_Address.Value = objRs(2).Value

Recordsetオブジェクトに対して、配列の添字のように数字で列を指定できます。
先頭の添字は1ではなく0なので、間違えないように注意してください。

列名が可変となるような場合に使用しますが、番号で列を指定した場合、その列にどのカラムが紐付いているのかをコードだけで後から追うのが困難になるため、可能な限り明示的に「列名」を指定する記述を使用してください。

フォーム内の非連結テキストボックスの値をテーブルに更新

当項では、今回のサンプルフォームに配置した「更新」ボタン押下時のイベントに組み込む、非連結テキストボックスの値を使用してデータベースのテーブルを更新する処理を紹介します。

尚、ADOを使用してデータベースのレコードを更新する場合、一般的には主に以下の方法があります。

更新対象データを取得したレコードセットを書き換えて、Recordsetオブジェクトの「Update」メソッドを実行する。
「UPDATE」文などのSQLを生成してデータベースに対して実行する。

今回の記事では、より汎用的である後者の「SQL」を使用する実装例を元に解説していきます。

尚、ADOを使用したデータの更新処理の例として、トランザクション管理を一切使わないシンプルな実装例と、適切にトランザクション処理を組み込んだ実装例を紹介します。

トランザクション処理を使わない更新処理例

当項では、まずトランザクション処理を組み込まずに更新処理を実装してみます。

  Private Sub btn_Update_Click()

  Dim objCon As ADODB.Connection
  Dim ConString As String
  Dim strSQL As String
      'データベースに接続します。
      Set objCon = New ADODB.Connection
      ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"
      objCon.Open ConString
      'SQL文を生成して実行します。
      strSQL = "UPDATE Customer "
      strSQL = strSQL & " SET CustomerName = '" & Me.txt_Name.Value & "' "
      strSQL = strSQL & " ,CustomerAddress = '" & Me.txt_Address.Value & "' "
      strSQL = strSQL & " WHERE CustomerID = " & Me.txt_ID.Value
      objCon.Execute strSQL
      
      MsgBox "データを更新しました。"
      'Connectionオブジェクトを閉じて破棄します。
      objCon.Close
      Set objCon = Nothing
  End Sub

上記のコードについて補足説明をしていきます。

前項で紹介した、データを取得する場合の処理ではRecordsetオブジェクトも使用しましたが、今回のデータ更新処理では、レコードセットを取得する必要はないため、Connectionオブジェクトのみ使用します。

11行目から14行目までで複数行に分けてSQL文を生成しています。
SQL文自体は一行でまとめて書いても良いのですが、長いSQL文を1行で書いてしまうと、SQLの構文を後から目視で解析することが困難になるため、上記のサンプルコードのように、変数を結合しながら複数行に分割しつつコード上で生成する書き方が一般的です。

15行目ではADODB.ConnectionオブジェクトのExecuteメソッドの引数にSQLを渡して実行しています。

尚、前項のデータ取得処理とは異なり、レコードセットを戻り値で取得する必要がないため、Executeメソッド実行時の戻り値取得処理は不要です。

また、Long型の変数を宣言したうえで、以下のようにExecuteメソッドを実行すると、UPDATE文などで実際に更新されたデータ件数をその変数に格納してくれます。

objCon.Execute strSQL,Long型の変数

当コードでは、各テキストボックスの値を直接SQL文に組み込んでいますが、本来はテキストボックスの値をいったん変数に格納して、その変数をSQL文に組み込む方がより適切です。

トランザクション処理の必要性

今回のサンプルコードでは、一件のレコードを更新するだけなので、必ずしもトランザクション処理を組み込む必要はないのですが、例えばループ処理のなかで1レコードずつUPDATEを実行していくような処理の場合は、トランザクション処理が必要になります。

例えば、ループ処理内で1レコードずつ更新を掛ける際に、何らかの理由でエラーになり処理が止まってしまった場合、その直前までに処理したレコードは更新され、エラー発生して更新できていないレコードも存在し、更新済みと未更新のレコードが混在することになります。

その様な状態になることを防ぐために、一連のループ処理のように、予定していた全ての処理が正常に完了するまで、UPDATEなどの更新内容を実際のレコードに反映させることを保留にする仕組みを「トランザクション処理」と呼びます。

トランザクション処理を組み込んだ更新処理例

前項のサンプルコードに対して、トランザクション処理を組み込んだ実装を紹介します。

  Private Sub btn_Update_Click()

  Dim objCon As ADODB.Connection
  Dim ConString As String
  Dim strSQL As String
      'データベースに接続します。
      Set objCon = New ADODB.Connection
      ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"
      objCon.Open ConString
      'SQL文を生成して実行します。
      strSQL = "UPDATE Customer "
      strSQL = strSQL & " SET CustomerName = '" & Me.txt_Name.Value & "' "
      strSQL = strSQL & " ,CustomerAddress = '" & Me.txt_Address.Value & "' "
      strSQL = strSQL & " WHERE CustomerID = " & Me.txt_ID.Value
      
  On Error GoTo ErrorProcess
      'トランザクションを開始します。
      objCon.BeginTrans
      'SQLを実行します。
      objCon.Execute strSQL
      'トランザクションをコミットします。
      objCon.CommitTrans
      
      MsgBox "データを更新しました。"
      'Connectionオブジェクトを閉じて破棄します。
      objCon.Close
      Set objCon = Nothing

      Exit Sub
      
  ErrorProcess:
      'トランザクションをロールバックします。
      objCon.RollbackTrans
      MsgBox "データを更新に失敗しました。"
      
      objCon.Close
      Set objCon = Nothing

  End Sub

上記のコードについて補足説明をしていきます。

前半はトランザクション無しの前項のコードと同じですが、16行目からエラー処理が追加されています。
GoToで「ErrorProcess」という名称のラベルが指定されており、この行以降は、エラーが発生すると、31行目に処理が飛びます。

18行目でConnectionオブジェクトの「BeginTrans」メソッドを実行しています。

BeginTransを実行することで、それ以降は同じConnectionオブジェクトを使用したExecuteメソッドで更新処理を実行しても、その更新処理は実レコードに反映されず保留になります。

20行目でSQLを実行しています。
当コードでは、エラーが出るならこの位置で発生する想定であり、エラーが出たら前述した通り31行目に処理が移動します。

22行目でConnectionオブジェクトの「CommitTrans」メソッドを実行しています。
CommitTransを実行することで、BeginTransで保留になっていた実レコードへの更新結果の反映が一括で実行されます。

31行目はエラーが発生した場合に実行されます。
Connectionオブジェクトの「RollbackTrans」メソッドを実行しています。

RollbackTransを実行することで、BeginTransで保留になっていた実レコードへの更新結果の反映をすべてキャンセルします。

上記のサンプルコードでは、1行しかUPDATEが実行されないため、実際には20行目でエラーになったとしても、その時点ではUPDATEに成功したトランザクションが存在しておらず、ロールバックするデータも存在しないため、あまり意味を成しません。

前述したように、トランザクション管理が必ず必要になるのは、ループ処理のなかでUPDATEを繰り返し実行したり、更新対象の異なる複数のUPDATE処理を組み合わせて順次実行し、その複数のUPDATE処理のすべてに成功しないとデータの整合性が保てなくなるようなケースです。

よって、上記のサンプルコードは、Connectionオブジェクトが持っているメソッド「BeginTrans」、「CommitTrans」、「RollbackTrans」の使い方を簡単に理解してもらうために実用性を無視したコードです。

トランザクション管理が活きるのは以下のようなコードです。

  On Error GoTo ErrorProcess
    objCon.BeginTrans
    Do Until objRs.EOF
      strSQL = "UPDATE Customer SET 何らかの更新内容 WHERE CustomerID = " & intID
      objCon.Execute strSQL
      objRs.MoveNext
    Loop
    'ループ内のすべての更新処理が成功したらコミット
    objCon.CommitTrans

とか、以下のように複数の更新処理を組み合わせる場合などです。

  On Error GoTo ErrorProcess
  objCon.BeginTrans
  
  strSQL = "UPDATE Customer1 SET 何らかの更新内容 WHERE CustomerID = " & intID
  objCon.Execute strSQL
  
  strSQL = "UPDATE Customer2 SET 何らかの更新内容 WHERE CustomerID = " & intID
  objCon.Execute strSQL
  
  strSQL = "UPDATE Customer3 SET 何らかの更新内容 WHERE CustomerID = " & intID
  objCon.Execute strSQL
  
  'Customer1とCustomer2とCustomer3のすべての更新処理が成功したらコミット
  objCon.CommitTrans

中の人

業務アプリケーションを開発する場合、信頼性の高いシステムを構築するにはトランザクション管理処理の実装は不可欠です。

積極的に取り入れてみてください。

コンボボックスやリストボックスにレコードセットを代入する方法

ADOのレコードセットをAccessで活用する場合、非連結フォームと非連結テキストボックスの使い方をまず覚えてほしいのですが、次に便利な使い方としては、ADOで取得したレコードセットをコンボボックスやリストボックスに代入してアイテムとして表示する実装方法です。

例えばコンボボックスであれば、データベースからマスタ系のレコードをADOで取得して、そのレコードセットをコンボボックスに代入するといった実装は多いですし、リストボックスの場合は、トランザクション系のレコードをADOで取得して、そのレコードセットをリストボックスに一覧表示させるといった実装はよくあります。

当項では、コンボボックスやリストボックのアイテムとしてレコードセットを使用する方法を紹介していきます。

コンボボックスやリストボックスのデータとしてレコードセットを使用する方法

当項では、コンボボックスやリストボックスのデータとしてレコードセットを使用する方法を紹介していきます。

尚、コンボボックスの実装方法については、以前に当ブログの別の記事で詳細に解説しているため、以下のリンク先をご参照ください。

尚、上記のリンク先にも記載しておりますが、コンボボックスやリストボックスをレコードセットで扱う場合の注意点としては、レコードセットを取得する方法として、今回の記事のなかで紹介していた「Connection」オブジェクトの「Execute」メソッドを使用した方法では、レコードセットの代入時にエラーになるため、「Recordset」オブジェクトの「Open」メソッドを使用していただく必要があります。

コンボボックスでの実装例

当コンボボックスをフォームビューから開くと以下のように表示されます。

  Sub Set_ComboBox_List()

  Dim objCon As ADODB.Connection
  Dim objRs As ADODB.Recordset
  Dim ConString As String
  Dim strSQL As String

      'コンボボックスのプロパティを設定
      Me.cmb_Test.ColumnCount = 2
      Me.cmb_Test.BoundColumn = 1
      Me.cmb_Test.ColumnWidths = "0cm;4cm"
      Me.cmb_Test.RowSourceType = "Table/Query"
      
      'データベースに接続します。
      Set objCon = New ADODB.Connection
      ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"
      objCon.Open ConString
      
      'SQLを生成します。
      strSQL = "SELECT "
      strSQL = strSQL & " CustomerID,CustomerName "
      strSQL = strSQL & "FROM "
      strSQL = strSQL & " Customer "
      strSQL = strSQL & "WHERE "
      strSQL = strSQL & " 条件1 "
      strSQL = strSQL & " AND 条件2 "
      
      'Recordsetオブジェクトの新規インスタンスを作成してSQLを実行します。
      Set objRs = New ADODB.Recordset
      objRs.Open strSQL, objCon, adOpenKeyset
      
      'コンボボックスにレコードセットを代入します。
      Set Me.cmb_Test.Recordset = objRs
      
      'ADODBの各オブジェクトを閉じて破棄します。
      objRs.Close
      objCon.Close
      Set objRs = Nothing
      Set objCon = Nothing

  End Sub

上記のコードでは、「cmb_Test」という名称のコンボボックスに対して、最初にプロパティを設定しています。
列数は2ですが、以下のように定義しているため、

Me.cmb_Test.ColumnWidths = "0cm;4cm"

レコードセットの先頭列の「テストID」はコンボボックスに表示されません。

ただ、内部的には「テストID」の値も保持しており、以下のプロパティの指定により、「連結列」としては「テストID」が使用されます。

Me.cmb_Test.BoundColumn = 1

おそらくよくある実装方法だと思います。

あと、余談ですが、フォーム自体のプロパティや、各コントロールのプロパティは、フォームの「デザインビュー」からではなく、可能な限りVBAで定義することをおススメします。
VBAで定義することで、明示的に指定したプロパティの値がテキストとしてコード内で識別できるため、プロパティの設定漏れや間違いを防ぐことになります。

リストボックスでの実装例

当リストボックスをフォームビューから開くと以下のように表示されます。

  Sub Set_ListBox_List()

  Dim objCon As ADODB.Connection
  Dim objRs As ADODB.Recordset
  Dim ConString As String
  Dim strSQL As String

      'コンボボックスのプロパティを設定
      Me.lst_Test.ColumnCount = 3
      Me.lst_Test.BoundColumn = 1
      Me.lst_Test.ColumnWidths = "2cm;4cm;4cm"
      Me.lst_Test.RowSourceType = "Table/Query"
      Me.lst_Test.ColumnHeads = True
      
      'データベースに接続します。
      Set objCon = New ADODB.Connection
      ConString = "Driver={SQL Server};Server=DBサーバーのIPアドレス又はホスト名;Uid=ログインユーザー;Pwd=パスワード;Database=データベース名"
      objCon.Open ConString
      
      'SQLを生成します。
      strSQL = "SELECT "
      strSQL = strSQL & " CustomerID,CustomerName,CustomerAddress "
      strSQL = strSQL & "FROM "
      strSQL = strSQL & " Customer "
      strSQL = strSQL & "WHERE "
      strSQL = strSQL & " 条件1 "
      strSQL = strSQL & " AND 条件2 "
      
      'Recordsetオブジェクトの新規インスタンスを作成してSQLを実行します。
      Set objRs = New ADODB.Recordset
      objRs.Open strSQL, objCon, adOpenKeyset
      
      'コンボボックスにレコードセットを代入します。
      Set Me.lst_Test.Recordset = objRs
      
      'ADODBの各オブジェクトを閉じて破棄します。
      objRs.Close
      objCon.Close
      Set objRs = Nothing
      Set objCon = Nothing

  End Sub

上記のコードも、コンボボックスの場合のコードと殆ど同じです。
尚、コンボボックスでは各列の列名を表示させることはあまりないのですが、リストボックスでは一覧としてデータを表示させたい場合に使用することが多い為、ヘッダー(列名)の表示を有効化しています。

Me.lst_Test.ColumnHeads = True

因みに、リストボックスのヘッダーは背景色を変えたりすることができないため、リストボックスのヘッダー表示は無効化したまま、フォーム側でラベルや四角形のコントロールなどを使用してヘッダーに見立てたデザインを作る場合もあります。

最後に

今回の記事では、Accessの非連結フォームや非連結テキストボックスとADOを使用して、データベースから取得したレコードセットを元に、テキストボックスに値を表示させたり、テキストボックスの値を編集して、データベースへ更新を掛ける処理のサンプルコードを紹介しつつ、その実装内容を解説していきました。

当記事のなかでも記載していますが、クエリやテーブルと連結したフォームを使用して業務アプリケーションを作る場合、細かな制御が出来なかったり、トランザクション処理なども取り入れた信頼性の高いアプリケーションを作ることは難しいです。
また、アプリの規模によっては、大量のクエリを用意することになり、後からメンテナンスをするのも非常に手間が掛かります。

システム開発会社が作るような、本格的な業務アプリケーションを作る場合は、やはりADOを使用してデータベースを操作する知識と、非連結フォームを使用して、VBAのコード上でレコードセットの値とテキストボックスなどのコントロールを関連付けて扱う知識は不可欠です。

Accessで業務アプリケーションを開発してて、今回の記事で紹介した「ADO＋非連結フォーム」を自由に取り扱えるようになれば、Access上級者として名乗っても差支えないと言えます。

これまで「クエリ＋連結フォーム」の組み合わせでAccessの業務アプリケーションを作られているのであれば、是非今回の記事を参考に、「ADO＋非連結フォーム」の組み合わせで実装する方法も積極的に取り入れてみてください。

長々と読んでいただきありがとうございました。
それでは皆さまごきげんよう！