AccessVBA/SQLステートメントを使ったデータベース操作の編集

*SQLステートメントを使ったデータベース操作 [#p404b15b]

#contents

**パラメータクエリを実行する方法 [#o41c7cec]
選択クエリの実行結果は、RecordsetオブジェクトのOpenメソッドを利用して取得する事ができます。~
しかし、この方法では、パラメータクエリを実行する事ができません。パラメータクエリの実行結果を取得するには

+CommandオブジェクトのActiveConnectionプロパティを設定する。
+CommandオブジェクトのCommandTextプロパティを設定する。
+CommandオブジェクトのExecuteメソッドを実行する。

-書式
 Set recordset = command.Execute(recordsaffected, parameters, options)
 
 recordset   :Recordsetオブジェクト型のオブジェクト変数
 command     :開いているCommandオブジェクト
 recordsetsaffected  :(パラメータクエリを実行するときは設定しない)
 parameters  :パラメータ
 options     :(省略可)プロバイダがCommandTextプロパティを評価する方法

-例1
 command.Execute(, "東京都")

**パラメータが1つだけのクエリを実行する [#m2cb4264]
Accessでパラメータクエリを実行すると、[パラメータの入力]ダイアロボックスが表示さえます。~
Commandオブジェクトを使ってパラメータクエリを実行するとき、同じようにパラメータ入力用のダイアログボックスを表示するには、InputBox関数を利用します。

-書式
 retvalue = InputBox(prompt, title)
 
 retvalue    :戻り値(String型) ユーザが入力した値
 prompt      :ダイアログボックスに出力する文字列
 tite        :ダイアログボックスのタイトルバーに出力する文字列

-例1
[Q_パラメータ]クエリを作成して、[都道府県]フィールドの抽出条件のところに[param]と入力したクエリをつくっておく

Public Sub adoPam()
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim com As New ADODB.Command
   Dim param As String
   
   Set cn = CurrentProject.Connection
   
   param = InputBox("都道府県を入力")
   If param = "" Then '---------------------------------------------①
     Exit Sub
   End If
   
   com.ActiveConnection = cn   '------------------------------------②
   com.CommandText = "Q_パラメータ"  '------------------------------③
   Set rs = com.Execute(, param)  '---------------------------------④
   
   Do Until rs.EOF
     Debug.Print rs!顧客ID, rs!氏名, rs!都道府県
     rs.MoveNext
   Loop
   Set com = Nothing   '--------------------------------------------⑤
   rs.Close: Set rs = Nothing: cn.Close: Set cn = Nothing
 End Sub

-①入力文字がNullの場合の処理
-②ActiveConnectionプロパティに、接続中のデータベースを設定する。
-③CommandTextプロパティに、実行するパラメータクエリの名前を設定する。
-④ダイアログボックスに入力された値を使って、パラメータクエリを実行する。
-⑤オブジェクト変数とCommandオブジェクトの関連を無効にする。~
CommandオブジェクトにはCloseメソッドがありません。

--&color(red){参考};~
この方法で選択クエリを実行したり、テーブルからレコードを取得したりすることもできます。~
ただし、取得したレコードセットは、つねに読み取り専用です。~
レコードの内容を編集する事ができないので、注意してください。

**複数のパラメータをもったクエリを実行する [#rbb10bc4]
パラメータクエリに複数のパラメータが含まれる場合は、クエリに渡す値を配列で指定しなければなりません。~
このときにはArray関数を使います。

-書式
 retvalue = Array(arglist)
 
 retvalue    :戻り値(Variant型)
 arglist     :配列を構成する値

-例1
 Set recordset = command.Execute(, Array("東京都","大阪府")

*SQLステートメント [#s8d24c41]
SQL(Structured Query Language)ステートメントは使用するデータベースエンジンによって書き方が少しずつ異なります。~
クエリの「SQLビュー」を利用すると、作成したクエリをSQLステートメントに変換する事ができます。

*Visual BasicでSQLステートメントを実行する [#afbd64dd]

**選択クエリを表すSQLステートメント [#ef925927]
選択クエリとは、テーブルに登録されているデータを、そのままの形で取得するクエリです。~
取得するフィールドは自由に指定する事ができます。~
また、レコードを抽出すう条件や並べ替えを指定する事もできます。

-書式
 SELECT fieldname1, fieldname2・・・
 FROM tablename
 
 fieldname   :取得するフィールド名
 tablename   :テーブル名

-例1~
[T_顧客]テーブルから[顧客ID]フィールドと[顧客名]フィールドを取得する
 SELECT 顧客ID,顧客名 FROM T_顧客

-例2~
[T_顧客]テーブルからすべてのフィールドを取得する
 SELECT * FROM T_顧客

***レコードを抽出するSQLステートメント [#xd96265a]
WHERE句を利用すると、レコードの抽出条件を指定する事ができます。

-書式
 SELECT fields1, fields2,・・・
 FROM tablename
 WHERE criteria
 
 fieldname   :取得するフィールド名
 tablename   :テーブル名
 criteria    :レコードを抽出する条件

-例1~
[T_顧客]テーブルから[都道府県]フィールドが「東京」のレコードを抽出する。
 SELECT * FROM T_顧客 WHERE 都道府県 = '東京都'

-例2~
[T_顧客]テーブルから[性別]フィールドが「1」のレコードを抽出する。
 SELECT * FROM T_顧客 WHERE 性別 = 1

***文字パターンを比較して抽出するSQLステートメント [#o2bec630]
WHERE句に抽出条件を指定するときにLIKE演算子を利用すると、文字パターンを比較してレコードを抽出できます。

-例~
[T_顧客]テーブルから[ﾌﾘｶﾞﾅ]フィールドが「あ行」で始まるのレコードを抽出する。
 SELECT * FROM T_顧客 WHERE ﾌﾘｶﾞﾅ LIKE '[ｱ-ｵ]%

***範囲を指定して抽出するSQLステートメント [#o77c8727]
WHERE句に抽出条件を指定するときにBETWEEN・・・AND演算子を利用すると、範囲を指定してレコードを抽出できます。

-例~
[T_顧客]テーブルから[生年月日]フィールドの値が「1970年1月1日～1970年12月31日」のレコードを抽出する。
 SELECT * FROM T_顧客 WHERE 生年月日 BETWEEN #01/01/1970# AND #12/31/1970#

***複数の条件で抽出するSQLステートメント [#icc0223e]
WHERE句には、複数の条件を指定する事ができます。~
「AかつB」はAND、「AまたはB」はOR演算子を利用します。

-例1~
[T_顧客]テーブルから[性別]が「1」かつ[生年月日]フィールドの値が「1970年1月1日～1970年12月31日」のレコードを抽出する。
 SELECT * FROM T_顧客
 WHERE((性別=1)AND(生年月日 BETWEEN #01/01/1970# AND #12/31/1970#))

-例2~
[T_顧客]テーブルから[都道府県]フィールドが「東京都」または「大阪府」のレコードを抽出する。
 SELECT * FROM T_顧客
 WHERE((都道府県='東京都') OR (都道府県 = '大阪府'))

***レコードを並べ替える [#ccc5f69d]
ORDER BY句を利用すると、指定したフィールドを使ってレコードを並べ替えることができます。

-書式
 SELECT fields1, fields2,・・・
 FROM tablename
 ORDER BY sortorder
 
 fieldname   :取得するフィールド名
 tablename   :テーブル名
 sortorder   :並べ替え順序
              ASC  :昇順
              DESC :降順

-例1~
[T_顧客]テーブルを[生年月日]フィールドを使って昇順で並べ替え
 SELECT * FROM T_顧客 ORDER BY 生年月日 ASC

-例2~
[T_顧客]テーブルを[生年月日]フィールドを使って降順で並べ替え
 SELECT * FROM T_顧客 ORDER BY 生年月日 DESC

***テーブルを結合するSQLステートメント [#n1128f5c]
INNERJOINを利用します。

-書式
 SELECT fields1, fields2,・・・
 FROM table1 INNERJOIN table2
 ON table1.field = table2.field
 
 fieldname       :取得するフィールド名
 table1,table2   :結合するテーブル名
 field           :結合に使うフィールドの名前

-テーブル名とフィールドの区切りはドット(.)です。
内部結合する場合、複数のテーブルを指定します。~
どちらのテーブルから参照するフィールドか明らかにするために下記のようにします。
 テーブル名.フィールド名

-例~
[T_注文履歴メイン]テーブルと[T_顧客]テーブルを結合して、[注文NO]フィールド、[日付]フィールド、[氏名]フィールドを参照する。
 SELECT T_注文履歴メイン.注文NO, T_注文履歴メイン.日付, T_顧客.氏名
 FROM T_注文履歴 INNER JOIN T_顧客
 ON T_注文履歴メイン.顧客ID = T顧客.顧客ID_

***選択クエリを実行する。 [#q897b6e1]
RecordsetオブジェクトのSourceプロパティに、選択クエリを表すSQLステートメントを設定してOpenメソッドを実行します。~
Sourceプロパティを設定する代わりにOpenメソッドのsource引数にSQLステートメントを設定しても、同じようにレコードセットが取得できます。

-例1
 Dim mySQL AS String
 mySQL = "SELECT * FROM T_顧客 WHERE 性別 = 1"
 recordset.Open mySQL, connection, adOpenKeyset, adLockOptimistic

-例2
 Public Sub adoSelect()
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim mySQL As String
   
   mySQL = "SELECT * FROM T_顧客 WHERE フリガナ LIKE '[ﾔ-ﾖ]%'"
   
   Set cn = CurrentProject.Connection
   rs.Open mySQL, cn, adOpenKeyset, adLockOptimistic
   
   Do Until rs.EOF
     Debug.Print rs!顧客ID, rs!ﾌﾘｶﾞﾅ
     rs.MoveNext
   Loop
   rs.Close: Set rs = Nothing: cn.Close: Set cn = Nothing
 End Sub

**パラメータクエリを表すSQLステートメント [#t4dbc5bd]

**アクションクエリ [#wc563158]
アクションクエリとは、テーブルに登録されているレコードを変更するクエリです。

-更新クエリ         :レコードを更新する
-削除クエリ         :レコードを削除する
-追加クエリ         :テーブルレコードを追加する
-テーブル作成クエリ :取得したレコードセットをもとにあらたにテーブルを作成する。

***更新クエリ [#h8a01fb4]
UPDATEステートメントを利用します。

-書式
 UPDATE tablename
 SET newvalue
 
 tablename  :編集するレコードが含まれるレコード
 newvalue   :編集する内容

-例1
 UPDATE T_顧客 SET 氏名 = 氏名 & ' さま'

***削除クエリ [#j2a1c463]
削除クエリはテーブルから一度にレコードを削除するクエリです。DELETEステートメントを利用します。

-書式
 DELETE FROM tablename
 
 tablename   :削除するレコードが含まれるテーブル名

-例1~
[T_顧客]テーブルからすべのコードを削除する
 DELETE FROM T_顧客

-例2~
[T_顧客]テーブルから[都道府県]フィールドが「東京都」のレコードだけ削除する。
 DELETE FROM T_顧客
 WHERE 都道府県 = '東京都'

***新規のレコードを追加する [#y67cc009]
既存のテーブルに新規にレコードを追加する場合、INSERT INTOステートメントを使います。

-書式
 INSERT INTO tablename(fields1, fields2,・・・)
 VALUES (value1, value2,・・・)
 
 tablename   :レコードを追加するテーブル名
 fieldname   :値を代入するフィールド名
 value       :フィールドに代入する値

-例
 INSERT INTO T_顧客 (顧客ID, 氏名, 生年月日)
 VALUES (21, '山田 太郎', #12/10/1970#)

***他のレコードセットからレコードをコピーする [#yeba1509]
フィールドの構成が同じレコードセットどうしであれば、レコードをコピーすることもできます。

-書式
 INSET INTO target
 SELECT fieldname1, fieldname2,・・・
 FROM source
 
 target     :レコードを追加するテーブル名
 fieldname  :追加したレコードに値を代入するフィールド
 source     :コピー元のテーブル名

-例1~
[T_商品]テーブルに[T_新商品]テーブルの[商品名]フィールドの値をコピーする。
 INSERT INTO T_商品
 SELECT 商品名 FROM T_新商品

-例2~
[T_商品]テーブルに[T_新商品]テーブルの内容をすべてコピーする。
 INSERT INTO T_商品 FROM * T_新商品

--コピー元テーブルから特定のレコードだけ抽出してコピーする場合はWHERE句を使います。~
[T_新商品]テーブルから[単価]フィールドが1000円以下のレコードを抽出して[T_商品]テーブルに追加
 INSERT INTO T_商品
 SELECT * FROM T_新商品 WHERE 単価 <= 1000

***テーブル作成クエリ [#o3efb1f2]
テーブル作成クエリは、テーブルから抽出したレコードを使って、新たにテーブルを作成するクエリです。~
新たに作成したテーブルに含まれるフィールドは、もとのテーブルと同じ構成となります。

-書式
 SELECT fieldname1, fieldname2,・・・
 INTO newtable
 FROM sourcetable
 WHERE criteria
 
 fieldname   :テーブルに追加するフィールド名
 newtable    :作成するテーブル名
 sourcetable :レコードを抽出するテーブル名
 criteria    :抽出条件

-例~
[T_顧客]テーブルから[性別]フィールドの値が「2」のレコードを抽出し、[T_女性]という名前のテーブルを作成する。
 SELECT * INTO T_女性 FROM T_顧客
 WHERE 性別=2

***アクションクエリを実行する [#za0cb707]
CommandオブジェクトのCommandTextプロパティにアクションクエリを表すSQLステートメントを設定して、Executeメソッドを実行すると、そのアクションを実行する事ができます。

-例1
 command.ActiveConnection = connection
 command.CommandText = "UPDATE T_顧客 SET 氏名 = 氏名 & ' さま'"
 command.Execute

-例2
 Public Sub adoaction()
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim com As New ADODB.Command
   Dim mySQL As String
   
   mySQL = "UPDATE T_顧客 SET 氏名 = 氏名&' 様' WHERE 性別=1"
   Set cn = CurrentProject.Connection
   com.ActiveConnection = cn
   com.CommandText = mySQL
   com.Execute
   
   rs.Open "T_顧客", cn, adOpenKeyset, adLockOptimistic
   Do Until rs.EOF
     Debug.Print rs!顧客ID, rs!性別, rs!氏名
     rs.MoveNext
   Loop
   
   Set com = Nothing
   rs.Close: Set rs = Nothing: cn.Close: Set cn = Nothing
 End Sub

タイムスタンプを変更しない