そこで今回は、SQL文を使った方法に変更してみましょう。以下のような、SQL文を使うメリットを感じていただけたらと思います。
・SQL文を使ったほうが簡単で強力
・データベースと対話するように操作できる
・SQL文は文字列なので、組み立てやすい
・最小限のプログラムで済む
・データの扱いはデータベースに任せられる
【1】Accessのファイル「SampleDB020.mdb」を起動し、更新前の「T01Prefecture2」を用意します。
【2】VBE を起動して、標準モジュールの test7 を表示します。
【3】標準モジュールの「test7」に以下のように、Subプロシージャ editDataTest2() を追加します。
Sub editDataTest2()
Dim db As DAO.Database
Set db = CurrentDb
Dim mySql As String
mySql = "UPDATE T01Prefecture2"
mySql = mySql & " SET PREF_NAME ='*' & PREF_NAME & '*'"
mySql = mySql & " WHERE PREF_CD >= 40"
Debug.Print mySql
db.Execute mySql, dbFailOnError
db.Close
Set db = Nothing
Debug.Print "レコードを更新しました。"
dispDataTest
End Sub
【4】イミディエイト ウィンドウから editDataTest2() を実行してください。
editDataTest2
＊この Subプロシージャは引数が無いので、 Call と ()カッコ は省略できます。
【5】SQL文、更新のメッセージ、レコードの一覧が表示されました。
【6】イミディエイト ウィンドウをスクロールすると、特定のレコードだけが更新されています。
【7】テーブル「T01Prefecture2」側でも、レコードが更新されていることを確認してください。
【解説】
赤枠が新しい部分です。
(1)Recordsetオブジェクトは不要です。
(2)SQL文を組み立てている部分です。
Dim mySql As String
mySql = "UPDATE T01Prefecture2"
mySql = mySql & " SET PREF_NAME ='*' & PREF_NAME & '*'"
mySql = mySql & " WHERE PREF_CD >= 40"
SQL文が長いので、わざと複数行に分割していますが、1行で書くこともできます。
↓同じ意味になる
mySql = "UPDATE T01Prefecture2 SET PREF_NAME ='*' & PREF_NAME & '*' WHERE PREF_CD >= 40"
＊文字列を連結するには「&」アンパサンドを使う
＊半角のスペースも重要
(3)SQL文の組み立てが上手くできているか、確認のためイミディエイト ウィンドウに表示している部分です。
Debug.Print mySql
(4)SQL文を実行している部分です。
db.Execute mySql, dbFailOnError
Execute の構文は以下のようになっています。
object.Execute source, [options]
・object Databaseオブジェクトを指定(変数)
・source SQLを指定
・options 省略可能 dbFailOnError は更新削除が不可能な時に取り消す
(5)UPDATE文はレコードの更新です。
UPDATE文の基本は以下のようになります。
UPDATE T01Prefecture SET PREF_NAME ='グアム' WHERE PREF_CD = 99;
更新するフィールドと値をペアで書きます。WHERE句で条件を指定するのを忘れると、全レコードが更新の対象となるため、注意が必要です。
SQLについては Access SQL初心者入門講座 をご覧ください。
【重要】
今回重要なことは、SQL文を代入している変数が文字列型(String)であることからもわかる通り、SQL文は単なる文字列なので、簡単に組み立てられるということです。
(1)引数にPREF_CDを指定できる。
例 call editDataTest2(40)
Sub editDataTest2(pCd As Integer)
省略
Dim mySql As String
mySql = "UPDATE T01Prefecture2"
mySql = mySql & " SET PREF_NAME ='*' & PREF_NAME & '*'"
mySql = mySql & " WHERE PREF_CD >= " & pCd
省略
End Sub
(2)WHERE句を引数にすることも可能。
例 editDataTest2(" WHERE PREF_CD >= 40")
Sub editDataTest2(condition As String)
省略
Dim mySql As String
mySql = "UPDATE T01Prefecture2"
mySql = mySql & " SET PREF_NAME ='*' & PREF_NAME & '*'"
mySql = mySql & condition
省略
End Sub
(3)極端な例では、SQL文自体を引数にすることも可能。
例 editDataTest2("UPDATE T01Prefecture2 SET PREF_NAME ='*' & PREF_NAME & '*' WHERE PREF_CD >= 40")
Sub editDataTest2(mySql As String)
省略
End Sub
要するに単なる文字列なので、いろいろな方法が使えるということです。プログラムをいじらずに抽出条件や不等号を変えることも簡単です。
またSQL文はまるでデータベースと対話している文章のようです。
UPDATE T01Prefecture2 SET PREF_NAME ='*' & PREF_NAME & '*' WHERE PREF_CD >= 40
↓
「テーブル T01Prefecture2 を更新して」
「PREF_NAMEの前後にアンパサンドを付けて」
「PREF_CDが40以上の都道府県だけ」
SQL文を使うと、処理自体はデータベースに任せられます。したがって処理部分はプログラミングする必要が無いので、プログラマーは楽をすることができます。
＊正確にはDBMS(data base management system)が行なう
課題1の解答例のプログラムには、更新する処理がありましたが、今回のプログラムにはありませんよね。
しかもSQLには様々な関数が用意されていて、集計などの複雑な処理が簡単にできるようになっています。
