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

SQLステートメントを使ったデータベース操作 †

SQLステートメントを使ったデータベース操作
SQLステートメント
Visual BasicでSQLステートメントを実行する

パラメータクエリを実行する方法 †

選択クエリの実行結果は、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つだけのクエリを実行する †

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メソッドがありません。

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

複数のパラメータをもったクエリを実行する †

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

書式

retvalue = Array(arglist)

retvalue    :戻り値(Variant型)
arglist     :配列を構成する値

例1

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

SQLステートメント †

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

Visual BasicでSQLステートメントを実行する †

選択クエリを表すSQLステートメント †

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

書式

SELECT fieldname1, fieldname2・・・
FROM tablename

fieldname   :取得するフィールド名
tablename   :テーブル名

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

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

レコードを抽出するSQLステートメント †

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ステートメント †

WHERE句に抽出条件を指定するときにLIKE演算子を利用すると、文字パターンを比較してレコードを抽出できます。

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

範囲を指定して抽出するSQLステートメント †

WHERE句に抽出条件を指定するときにBETWEEN・・・AND演算子を利用すると、範囲を指定してレコードを抽出できます。

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

複数の条件で抽出するSQLステートメント †

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 (都道府県 = '大阪府'))
```

レコードを並べ替える †

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ステートメント †

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_

選択クエリを実行する。 †

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ステートメント †

アクションクエリ †

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

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

更新クエリ †

UPDATEステートメントを利用します。

書式

UPDATE tablename
SET newvalue

tablename  :編集するレコードが含まれるレコード
newvalue   :編集する内容

例1

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

削除クエリ †

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

書式

DELETE FROM tablename

tablename   :削除するレコードが含まれるテーブル名

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

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

新規のレコードを追加する †

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

書式

INSERT INTO tablename(fields1, fields2,・・・)
VALUES (value1, value2,・・・)

tablename   :レコードを追加するテーブル名
fieldname   :値を代入するフィールド名
value       :フィールドに代入する値

例

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

他のレコードセットからレコードをコピーする †

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

書式

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
```

テーブル作成クエリ †

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

書式

SELECT fieldname1, fieldname2,・・・
INTO newtable
FROM sourcetable
WHERE criteria

fieldname   :テーブルに追加するフィールド名
newtable    :作成するテーブル名
sourcetable :レコードを抽出するテーブル名
criteria    :抽出条件

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

アクションクエリを実行する †

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

ナビゲーション

検索

ツールボックス

最新の40件