スポンサードリンク

Access VBA SQL文を使って検索、追加、更新、削除する方法。

VBA で、SQL文を使ってデータベースを操作することのメリットを、実感できたと思います。

そこで今回は、以前に作成した、標準モジュール test6 の検索、追加、更新、削除を、SQL文を使った方法に書き換えてみましょう。

・レコードの操作は、T01Prefecture に対して行ないます。(重要)
・test6 はそのままにして、新規に test8 を作成します。
・test8 の Subプロシージャ名は test6 と同じにします。


【1】Accessのファイル「SampleDB020.mdb」を起動し、VBE を起動してください。

*VBEは「Altキー」+「F11キー」で起動できます。


【2】新しく標準モジュール test8 を作成します。

access-vba-255.gif


【3】標準モジュールの「test8」に、以下のように Subプロシージャ を追加します。

access-vba-256.gif


Sub dispData()
  Dim db As DAO.Database
  Set db = CurrentDb
  
  Dim mySql As String
  mySql = "SELECT * FROM T01Prefecture"
  mySql = mySql & " ORDER BY PREF_CD ASC"
  
  Debug.Print mySql
  
  Dim rs As DAO.Recordset
  Set rs = db.OpenRecordset(mySql)
  
  Do Until rs.EOF
    Debug.Print rs.Fields("PREF_CD") & " " & rs.Fields("PREF_NAME")
    'Debug.Print rs!PREF_CD & " " & rs!PREF_NAME
    rs.MoveNext
  Loop

  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing
  
End Sub

Sub addData(pCd As Integer, pName As String)
  Dim db As DAO.Database
  Set db = CurrentDb

  Dim mySql As String
  mySql = "INSERT INTO T01Prefecture"
  mySql = mySql & " (PREF_CD, PREF_NAME)"
  mySql = mySql & " VALUES (" & pCd & ", '" & pName & "')"
  
  Debug.Print mySql
  db.Execute mySql

  db.Close
  Set db = Nothing
  
  Debug.Print "レコードを追加しました。"
  test8.dispData
End Sub

Sub seekData(pCd As Integer)
  Dim db As DAO.Database
  Set db = CurrentDb
  
  mySql = "SELECT * FROM T01Prefecture"
  mySql = mySql & " WHERE PREF_CD = " & pCd
  
  Debug.Print mySql
  
  Dim rs As DAO.Recordset
  Set rs = db.OpenRecordset(mySql)
  
  If rs.BOF And rs.EOF Then
    Debug.Print "該当するレコードは見つかりませんでした。"
  Else
    Debug.Print pCd & ":" & rs.Fields("PREF_NAME")
  End If

  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing
  
End Sub

Sub editData(pCd As Integer, pName As String)
  Dim db As DAO.Database
  Set db = CurrentDb
    
  Dim mySql As String
  mySql = "UPDATE T01Prefecture"
  mySql = mySql & " SET PREF_NAME = '" & pName & "'"
  mySql = mySql & " WHERE PREF_CD = " & pCd
  
  Debug.Print mySql
  db.Execute mySql, dbFailOnError
  
  db.Close
  Set db = Nothing
  
  Debug.Print "レコードを更新しました。"
  test8.dispData
End Sub

Sub deleteData(pCd As Integer)
  Dim db As DAO.Database
  Set db = CurrentDb
  
  Dim mySql As String
  mySql = "DELETE FROM T01Prefecture"
  mySql = mySql & " WHERE PREF_CD = " & pCd
  
  Debug.Print mySql
  db.Execute mySql, dbFailOnError
  
  db.Close
  Set db = Nothing
  
  Debug.Print "レコードを削除しました。"
  test8.dispData
End Sub


動作確認については、次回に説明します。


【解説】
今回のプログラムは、これまでにも何度か使っているので、重要な部分だけ解説します。

(1)本当は標準モジュールで、同じ名前のプロシージャを定義したくないのですが、今回は比較しやすいように、わざと Subプロシージャ名を test6 と同じにしました。


(2)同じSubプロシージャ名が定義されているので、Subプロシージャ の実行方法が、今までと少し変わります。(次回に説明)


(3)わかりやすくするため、エラー処理は省略しています。そのため想定外の値で実行した場合は、正しく動作しません。


(4)seekData()部分について

BOF かつ EOF でもあるということは、レコードが無いということです。
If rs.BOF And rs.EOF Then

BOF(Begin Of File)はレコードの始端を表す記号
EOF(End Of File)はレコードの終端を表す記号

以下のようにイメージすると、わかりやすいと思います。

・レコードがある場合

BOF
1 北海道 ←カレント レコード
EOF


・レコードが無い場合

BOF EOF


ちなみに以下でも同じ結果になります。
If rs.BOF Then
または
If rs.EOF Then

しかしレコードがある場合でも、「rs.MoveNext」で移動したりすると「EOF」になることがあるので、注意が必要です。やはり「rs.BOF And rs.EOF」が確実です。


(5)SQL実行部分の違いについて

db.Execute mySql, dbFailOnError
db.Execute mySql

dbFailOnError は、レコードの更新・削除が不可能な時に取り消すオプションなので、追加の場合は省略しています。


(6)レコードを表示している部分について

test8.dispData

追加、更新、削除の後には、dispDataを呼び出していますが、標準モジュールに、同じ名前のプロシージャがあるため、「標準モジュール名.プロシージャ名」の形式で明確に指定しています。

*間に半角の「.」ドットが入ります。


【ワンポイント】
SQLについては Access SQL初心者入門講座 をご覧ください。詳しく解説しています。



スポンサードリンク






Access VBA 初心者入門講座 TOPへ