AccessのVBAにて、テーブル内のデータの読み取り・書き込みを行うには、「DAO」と「ADO」という2つの方法があります。
ここでは、「ADO」（ActiveX Data Objects）を使った方法ご説明いたします。
なお、「そもそもDAO・ADOとは何か？」という説明および、「DAO」（Data Access Objects）を使った方法については、以下の記事をご参照ください。
ADOを使う事前準備
ADOを使うには、以下の手順で「Microsoft ActiveX Data Objects x.x Library」への参照設定を追加する必要があります。
これをやらないと、以下で紹介するサンプルプログラムの先頭で、「ユーザー定義型は定義されていません。」というエラーが出てしまいます。
[ツール] ➡ [参照設定] を選択
「Microsoft ActiveX Data Objects x.x Library」にチェックを入れて [OK] をクリック
ADOでの読み取り処理
以下のようなテーブル[社員マスタ]を、1件ずつ順に読み取るサンプルコードをご紹介します。
サンプルコード
'データベースに接続する
Dim objCn As ADODB.Connection
Set objCn = CurrentProject.Connection
'レコードセットオブジェクトを使ってテーブルを開く
Dim objRs1 As ADODB.Recordset
Set objRs1 = New ADODB.Recordset
objRs1.Open "社員マスタ", objCn, adOpenForwardOnly, adLockReadOnly
'レコードセットを全件読み込むまでループ
Do Until objRs1.EOF
'レコードセットを参照する処理
Debug.Print objRs1!社員番号 & "," & objRs1!氏名
'次のレコードに移動
objRs1.MoveNext
Loop
'最後にお作法として、オブジェクトを解放
objRs1.Close: Set objRs1 = Nothing
objCn.Close: Set objCn = Nothing実行結果
101,テスト 一郎
102,テスト 次郎
103,テスト 三郎
204,テスト 史郎
205,テスト 吾郎
206,テスト 六郎解説
データベースへの接続
ADOでデータベースの読み取り・書き込みを行うには、次の2つの手順が必要になります。
- データベースに接続する
- テーブルを開く
ここで言う「データベース」とは、ものすごくざっくり言うと「テーブルの集まり」だと思って下さい。
Accessでは、「.accdb」ファイルが1つのデータベースで、その中に、テーブルが存在します。
ADOでは、まずは「.accdb」ファイルというデータベースに接続したうえで、その次に「社員マスタ」というテーブルにアクセスする流れになります。
サンプルコードでは、自分自身の「.accdb」ファイル内のテーブルを扱いますので、
Set objCn = CurrentProject.Connectionで自分自身に接続します。
テーブルを開く
ポイントは、以下の1文になります。
objRs1.Open "社員マスタ", objCn, adOpenForwardOnly, adLockReadOnlyまずは、開きたいテーブル “社員マスタ” を指定します。
なお、DAOと同様に、テーブルだけでなくSQLのSELECT文やクエリ名も指定可能です。
objCn は、データベースへの接続で指定したオブジェクト変数です。
adOpenForwardOnly, adLockReadOnly は、それぞれ「カーソルタイプ」と「ロックタイプ」で、データの取得方法や排他制御の方法について、以下のような指定が可能です。
カーソルタイプ
| 定数 | 特徴 |
|---|---|
| adOpenForwardOnly | 前方スクロールカーソル。 レコードを前方向にのみ移動できる。 大量データを順番に処理する場合などに利用する。 必要リソースが少ない。 |
| adOpenDynamic | 動的カーソル。 レコードを前後に移動できる。 他のユーザーによる更新・追加・削除結果がリアルタイムで参照できる。 必要リソースが非常に多い。 |
| adOpenStatic | 静的カーソル。 レコードを前後に移動できる。 他のユーザーによる更新・追加・削除結果が参照できない。 |
| adOpenKeyset | キーセットカーソル。 レコードを前後に移動できる。 他のユーザーによる更新結果は参照できるが、追加・削除結果が参照できない。 必要リソースが多い。 |
ロックタイプ
| 定数 | 特徴 |
|---|---|
| adLockReadOnly | 読み取り専用。 必要リソースが少ない。 |
| adLockPessimistic | レコード単位の排他的ロック。 データを取得した時点でロックする。 ロック中は他のユーザーがデータを変更できない。 |
| adLockOptimistic | レコード単位の共有的ロック。 データの更新時にのみロックする。データの取得時はロックしない。 他のユーザーもデータにアクセスできるため、 複数ユーザーが同時にデータを変更する可能性がある。 |
| adLockBatchOptimistic | 共有的バッチ更新。 複数の行を一度に更新するためのロック。 他のユーザーもデータにアクセスできるため、 複数ユーザーが同時にデータを変更する可能性がある。 |
レコードの読み取り
以下の流れは、DAOと同様です。
- Do Until objRs1.EOF でレコードセットを全件読み込むまでループ
- objRs1!フィールド名 でレコードセットを参照する
- objRs1.MoveNext で次のレコードに移動する
ADOでの書き込み（レコード追加）処理
先ほどのテーブル[社員マスタ]に、レコードを1件追加してみます。
サンプルコード
'データベースに接続する
Dim objCn As ADODB.Connection
Set objCn = CurrentProject.Connection
'テーブルを開く
Dim objRs1 As ADODB.Recordset
Set objRs1 = New ADODB.Recordset
objRs1.Open "社員マスタ", objCn, adOpenForwardOnly, adLockPessimistic
'レコードを1件追加する場合は「AddNew」を記述する
objRs1.AddNew
'追加するレコードの各フィールドに値を設定する
objRs1!社員番号 = 207
objRs1!氏名 = "テスト 七子"
objRs1!生年月日 = #7/7/2005#
'最後に「Update」を記述して編集内容を反映する
objRs1.Update
'最後にお作法として、オブジェクトを解放
objRs1.Close: Set objRs1 = Nothing
objCn.Close: Set objCn = Nothing実行結果
社員番号「207」のレコードが追加されました。
解説
今度は排他をかける必要がありますので、テーブルを開く文を、以下のようにしました。
objRs1.Open "社員マスタ", objCn, adOpenForwardOnly, adLockPessimisticあとは、DAOと同様です。
- レコードを1件追加する場合は「AddNew」を記述する
- 追加するレコードの各フィールドに値を設定する
- 最後に「Update」を記述して編集内容を反映する
ADOでの書き込み（レコード更新）処理
社員番号「204」の役職を、「係長」に更新してみます。
サンプルコード
'データベースに接続する
Dim objCn As ADODB.Connection
Set objCn = CurrentProject.Connection
'テーブルを開く
Dim objRs1 As ADODB.Recordset
Set objRs1 = New ADODB.Recordset
objRs1.Open "select * from 社員マスタ where 社員番号 = 204", objCn, adOpenForwardOnly, adLockPessimistic
'追加するレコードの各フィールドに値を設定する
objRs1!役職 = "係長"
'最後に「Update」を記述して編集内容を反映する
objRs1.Update
'最後にお作法として、オブジェクトを解放
objRs1.Close: Set objRs1 = Nothing
objCn.Close: Set objCn = Nothing実行結果
社員番号「204」の役職が「係長」に更新されました。
解説
レコード追加と同様に、テーブルを開くときに排他をかける必要があります。
あとは追加処理とほぼ同じなのですが、DAOと異なる点として、更新時に「objRs1.Edit」の記述は不要です。
objRs1.Edit を記述すると、「メソッドまたはデータ メンバーが見つかりません。」というエラーになります。
なお、該当データが複数件存在する可能性がある場合に、ループ処理が必要な点は、DAOと同様です。
※次の削除処理のサンプルで、ループ処理を組み込んでみます。
ADOでのレコード削除処理
役職がNullのレコードを、削除してみます。
今度は該当データが複数件存在するサンプルとして、ループ処理を組み込んでみます。
サンプルコード
'データベースに接続する
Dim objCn As ADODB.Connection
Set objCn = CurrentProject.Connection
'テーブルを開く
Dim objRs1 As ADODB.Recordset
Set objRs1 = New ADODB.Recordset
objRs1.Open "select * from 社員マスタ where 役職 is null", objCn, adOpenForwardOnly, adLockPessimistic
'レコードセットを全件読み込むまでループ
Do Until objRs1.EOF
'レコードを削除する場合は「Delete」を記述する
objRs1.Delete
'次のレコードに移動
objRs1.MoveNext
Loop
'最後にお作法として、オブジェクトを解放
objRs1.Close: Set objRs1 = Nothing
objCn.Close: Set objCn = Nothing実行結果
役職がNullのレコードが削除されました。
コメント