Microsoft Access 掲示板

Microsoft Access 掲示板

ＳＱＬの実行方法

17 コメント
views
4 フォロー

★先ほど間違えて、新しく作成で投稿しませんでした（すみません）同じ内容ですが投稿します
先日　strSQL = strSQL & " WHERE " & Me.Filter･･･を教えて頂いた者です。
関連のフォームでＳＱＬ実行させたい事があるのですが分からないことがでてきました。先日の応用が出来なくて、もどかしいです・・・。やりたい事は部品現品は入荷済みで納品書が未達の分を抽出するフォームを作成しています。そのフォームには仕入先、入荷日、直送先等の抽出条件コンボボックスがあり前回同様　strFilter = mid(strFilter, 6) で抽出出来ています。レコードにはチェックボックスがありチェックYesにしたものだけに後日届いた納品書の日付をフォーム上のテキストボックスから代入して一括入力させたいのです（レコードに１件づつ直接日付入力も可能）。レコード１件づつチェックYesにする事も可能ですがFilter抽出した分にその一括処理をする更新クエリをしているのですが、現状抽出条件毎にクエリを複数作成しています。それを集約させたいのでWHERE " & Me.Filterでしようと思っていたのですが、いざ記述しようとするとどうやって？となってしまったのです。更新クエリのSQLビューは下記です。
UPDATE T納品書まだ分 SET T納品書まだ分.ﾁｪｯｸ1 = Yes
WHERE (((T納品書まだ分.納品書入り日) Is Null) AND ((T納品書まだ分.発注先ID)=[Forms]![F納品書未達処理]![cb発注先選択]));
この[cb発注先選択]部を　Me.Filter　にしたいのです。T納品書まだ分.納品書入り日) Is Nullの後にどうコードをつないだらいいのかが分からなくて。一括でチェックYes処理はフォーム上のボタンにDoCmd.OpenQuery・・・で処理させてましたがＳＱＬ勉強中なのでそれで処理させようと思ったのですがやり方が分からないのが現状です。
最終的にはチェックＹｅｓ分に納品書日付を一括代入するボタンもＳＱＬ（更新）で処理させたいです。
またこの更新ＳＱＬの実行もＶＢＡでどう実行させたらいいのかも分からなくて。
応用力がなく情けないのですが、アドバイスを宜しくお願いします。

tom
作成: 2024/05/14 (火) 16:10:27
通報 ...
1
hiroton 2024/05/14 (火) 16:35:50 43b60@f966d

フォームでフィルターを使いつつ、フォームに読み込んでいるデータを扱いたいのなら、DAOとか使ったほうが楽だと思います

フォームに表示されているレコードのチェックボックスを全てYesにする

Me.Refresh 'カレントレコードの編集中によるレコードロック対策
With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
        .Edit
        ![T納品書まだ分.ﾁｪｯｸ1] = True
        .Update
        .MoveNext
    Loop
End With

いろいろ追加

Me.Refresh '現在編集中のレコードを保存する（編集中によるレコードロック対策）
With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
        If IsNull(![T納品書まだ分.納品書入り日]) Then
            .Edit
            ![T納品書まだ分.ﾁｪｯｸ1] = True
            !納品書日付 = Me!納品書日付入力テキストボックス
            .Update
        End If
        .MoveNext
    Loop
End With

SQLを発行するメリットが見つからないので、知識レベルの話としてになりますが、

フォームのボタンを押してSQLを実行するならば、

UPDATE T納品書まだ分 SET T納品書まだ分.ﾁｪｯｸ1 = Yes
WHERE (((T納品書まだ分.納品書入り日) Is Null) AND ((T納品書まだ分.発注先ID)=[Forms]![F納品書未達処理]![cb発注先選択]));

このクエリの実行で動作上の不具合はないですよね？

一括でチェックYes処理はフォーム上のボタンにDoCmd.OpenQuery・・・で処理させてましたがＳＱＬ勉強中なのでそれで処理させようと思ったのですがやり方が分からないのが現状です。

「処理させてましたが」「やり方が分からない」
「処理させていた」のなら「出来ている」のでは？

2
hiroton 2024/05/14 (火) 17:05:54 43b60@f966d

この[cb発注先選択]部を　Me.Filter　にしたいのです。T納品書まだ分.納品書入り日) Is Nullの後にどうコードをつないだらいいのかが分からなくて。

Me.Filterの中には

そのフォームには仕入先、入荷日、直送先等の抽出条件

ということで、[cb発注先選択]以外の情報が含まれている可能性があります。この中（Me.Filter）から[cb発注先選択]だけの情報を抜き取りたいということであれば、それは普通やらないです。[cb発注先選択]の情報はそのままMe!cb発注先選択で参照できるので、わざわざMe.Filterから抜き出したりしません

strSQL = _
    " UPDATE T納品書まだ分 SET T納品書まだ分.ﾁｪｯｸ1 = Yes" & _
    " WHERE (((T納品書まだ分.納品書入り日) Is Null) AND ((T納品書まだ分.発注先ID)=" & Me![cb発注先選択] & "));"

フィルターを何かの手法で直接操作して、その状態でフィルター文字列から何かの情報を取得したいというのは方向性が逆ですね。元となる個別の情報を個別に扱えるような設計を考えましょう

DoCmd.OpenQuery

動的にSQLを生成するなら、そのままSQLを実行するコマンドがあります
DoCmd.RunSQL メソッド (Access)

もっといえば、VBAで処理をするならDoCmd オブジェクト (Access)はあまり使う場面がありません。たいていは、より直接的に扱える手法が用意されています。クエリ、SQLの実行であればDatabase.Execute メソッド (DAO)が使いやすいと思います

使用例

strSQL = _
    " UPDATE T納品書まだ分 SET ﾁｪｯｸ1 = Yes" & _
    " WHERE 納品書入り日 Is Null AND 発注先ID=" & Me!cb発注先選択 & ";"
CurrentDb.Execute strSQL

最終的にはチェックＹｅｓ分に納品書日付を一括代入するボタンもＳＱＬ（更新）で処理させたいです。

納品書日付に入力する値もフォームのテキストボックスで事前に入力しておけば、そのテキストボックスを参照してSQLの文字列に入れ込めばいいですね

If Not IsDate(Me!納品書日付) Then
    MsgBox "納品書日付を入力してください"
    Me!納品書日付.SetFocus
    Exit Sub
End If
strSQL = _
    " UPDATE T納品書まだ分 SET ﾁｪｯｸ1 = Yes, 納品書日付 = #" & Me!納品書日付入力テキストボックス & "#" & _
    " WHERE 納品書入り日 Is Null AND 発注先ID=" & Me!cb発注先選択 & ";"
CurrentDb.Execute strSQL

改めてですが、フォームに読み込んだデータをフォーム上の作業で更新するならDAO処理のほうが楽ですね。それはそれとして、VBAでSQLを動的に扱うことはあるので、それ用のケーススタディということで
3
hatena 2024/05/14 (火) 18:25:11

やりたいことを整理すると下記のようなことですか。

帳票フォームのヘッダーに条件用の複数のテキストボックスがあり、その値によりフォームにフィルターをかけている。
VBAで抽出条件式を生成してそれをFilterプロパティに設定している。

表示されているレコードのうち、納品書入り日が未入力(Null)のレコードのチェックボックスにチェックを入れたいということですか。

SQLで更新するなら既に出ていますが、DoCmd.RunSQL か CurrentDb.Execute を使います。

下記でどうでしょう。

strSQL = _
    "UPDATE T納品書まだ分 SET ﾁｪｯｸ1 = Yes " & _
    "WHERE 納品書入り日 Is Null AND (" & Me.Filter & ");"
CurrentDb.Execute strSQL

フォームのレコードセットをループで回して直接更新してもいいです。(hirotonさんの回答)
4
hatena 2024/05/14 (火) 18:58:18

フォームのレコードセットを使って更新する場合のメリットは、
抽出条件などは考えずに機械的にコーディングできる点。
デメリットは、SQLでの更新よりは処理が遅い点。

ただし、体感できる差がでるのは、よほど件数が多いときです。

下記で処理速度の検証をしていますのでご参考に。

フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？ - hatena chips

10
hiroton 2024/05/16 (木) 08:52:20 17372@f966d >> 4

いまさら改めて見てですけど、「cmdSQLExecute_Click」の

CurrentDb.Execute "UPDATE Tbl1 SET Tbl1.F1 = Rnd();"

って、Rnd()がキャッシュ使われて一度しか乱数取得処理入らないんで、ほかのDAO処理のrs!F1 = Rndとやってること違いますね
12
hatena 2024/05/16 (木) 10:10:42 修正 >> 10

あら、そうですね。
となると、RecordsetCloneとSQLの差は縮まりそうですね。あるいは差はないかも。
要検証ですね。時間がとれたらやってみます。

ご指摘ありがとうございます。

5
tom 2024/05/15 (水) 13:18:14 ddfe5@07fee

hirotonさん　hatenaさん　ありがとうございます。まずはベタな方法ですが下記で記述しました。
Dim strSQL As String
  strSQL =
       "UPDATE Q納品書未達F用Rs SET check = Yes " &
       "WHERE 納品書入り日 Is Null AND (" & Me.Filter & ");"
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
  Me.Refresh
これで一応更新処理はできました。次にＤＡＯでもやってみようと思ってます。それで大変初歩的な質問なのですが先頭に下記を記述でいいでしょうか？　これの後にアドバイス頂きましたコードを記述でいいのでしょうか？

'変数宣言
Dim db As DAO.Database
Dim rs As DAO.Recordset
'現在開いているデータベースに接続する。
Set db = CurrentDb

あとこれも初歩的な質問ですが　 "WHERE 納品書入り日 Is Null AND (" & Me.Filter & ");"　のANDの後は()が必要なのですね？　勉強不足ですみません。
6
hatena 2024/05/15 (水) 16:27:38

あとこれも初歩的な質問ですが　 "WHERE 納品書入り日 Is Null AND (" & Me.Filter & ");"　のANDの後は()が必要なのですね？　勉強不足ですみません。

比較演算子には優先順位があり、ORよりANDの方が優先度が高いです。

ですので、FilterにOR演算子があった場合、意図せぬ結果になる場合があります。

例えば、Filterが A = 1 OR B = 2　の場合、()がないと、

納品書入り日 Is Null AND A = 1 OR B = 2
となり、これは
(納品書入り日 Is Null AND A = 1) OR B = 2
と解釈されます。

意図している下記とは結果が異なります。
納品書入り日 Is Null AND (A = 1 OR B = 2)
7
hatena 2024/05/15 (水) 16:38:55

これで一応更新処理はできました。次にＤＡＯでもやってみようと思ってます。それで大変初歩的な質問なのですが先頭に下記を記述でいいでしょうか？　これの後にアドバイス頂きましたコードを記述でいいのでしょうか？

DAOでSQLを実行するという意味なら、変数宣言は不要です。
CurrentDb はAccessファイルを開いた時点で自動で宣言されてますので。
つまり、下記でOKです。

  Dim strSQL As String
  strSQL =
       "UPDATE Q納品書未達F用Rs SET check = Yes " &
       "WHERE 納品書入り日 Is Null AND (" & Me.Filter & ");"
 CurrentDb.Execute strSQL

DAOのレコードセットを利用して更新するということなら、フォームを開いた時点で、すでにDAOレコードセットは開いていて、フォームと連結しています。
そのレコードセットは、Me.Recordset または Me.RecordsetClone でアクセスできます。
ゆえに、やはり、変数宣言は不要です。
レコードセットは宣言してそれに代入してもいいですが、DAO.Database の宣言は不要です。
hirotonさんの#1の回答のコードや、#4の私の回答のリンク先のコードを参照してください。

8
tom 2024/05/15 (水) 16:40:10 ddfe5@07fee

haenaさん　ありがとうございます。そういう()だったのですね。こういう事も認識ないと単純に()なしで記述してしまうので勉強になりました。
別件のhirotonさんからの「処理させていた」のなら「出来ている」のでは？に回答出来ていませんでした。（遅くなりました）
元々更新クエリを数ヶ作成してフォームのフィルターに合わせて実行させていたのです。条件1のときはクエリ１、条件2の時はクエリ2の形で（If分で分岐）。今回色々フォーム変更が必要になり抽出条件が異なるだけの複数のクエリを１つにまとめたいのが目的でした。それを出来るならクエリ作成せずに出来ればと思ったのです。

9
tom 2024/05/16 (木) 08:39:03 ddfe5@07fee

hatenaさんの最後の回答を見る前に投稿してしまいました。失礼しました。
この場合ＤＡＯとの接続宣言は不要なのですね。因みにDim db As DAO.DatabaseやDim rs As DAO.Recordsetの宣言はどういう時に必要なのでしょうか？　ネットでのコード見るとこれがあるので、毎回必要と思ってました。
初歩的な事ばかりですみません。

14
sk 2024/05/16 (木) 10:51:31 d8f29@fc3ed >> 9

Dim db As DAO.DatabaseやDim rs As DAO.Recordsetの宣言はどういう時に必要なのでしょうか？

16
sk 2024/05/16 (木) 11:27:52 d8f29@fc3ed >> 9

あとこれも。
11
hatena 2024/05/16 (木) 10:08:05

まず、AccessのUIでのデータベース操作はDAOを使っています。
クエリ、フォーム、レポートなど、DoCmd系のデータベース操作など。
まずは、これを理解しておいてください。

そこで、
ネットでよくあるコードの一例

Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("C:\daosample\sample.accdb") 'データベースを開く 
Set rs = db.OpenRecordset("テーブル名") 'テーブルをレコードセットしてと開く

これは現在開いてコードを記述しいるAccessファイル以外のAccessファイルを操作するコードになります。具体的には捜査対象は C:\daosample\sample.accdbファイルです。

現在開いてコードを記述しいるAccessファイル内のテーブルを操作する場合は、
Accessファイルを開いた時点で、DAOデータベースは自動で開かれています。よって、OpenDatabaseの記述は不要です。現在開かれているデータベースはCurrentDBで取得できます。

Dim db As Database
Dim rs As Recordset
Set db = CurrentDB 
Set rs = db.OpenRecordset("テーブル名") 'テーブルをレコードセットしてと開く

これは変数宣言を省略して下記のように記述してもOKです。

Dim rs As Recordset
Set rs = CurrentDB.OpenRecordset("テーブル名") 'テーブルをレコードセットしてと開く

次に、フォームのデータ操作する場合、フォームを開いた時点で、DAOレコードセットも自動で開かれます。フォームのUIでこのレコードセットを操作している仕組みになってます。
ですので、フォームのデータをVBAで操作する場合、OpenRecordsetでわざわざ開く必要はありません。
フォームのレコードセットにアクセスする方法は、Me.Recordset、Me.RecordsetClone、Me.Recordset.Clone があります。それらの違いについては、#4の回答のリンク先で解説しています。

Dim rs As Recordset
Set rs = Me.RecordsetClone 'フォームのレコードセットを取得

Withステートメントを使用すれば変数宣言も不要になります。

With Me.RecordsetClone
   'レコードセットに対するデータ操作
End With

これは、OpenRecordsetの場合も同様に省略することはできますが、
あまり見かけませんね。

With CurrentDB.OpenRecordset("テーブル名") 
   'レコードセットに対するデータ操作
   .Close
End With

こんな感じで理解できたでしょうか。

13
tom 2024/05/16 (木) 10:38:15 ddfe5@07fee

hatenaさん何度も回答ありがとうございます。
大変分かり易い説明をお手数かけました。そういう事だったのですね。納得しました。
色々な情報がごっちゃになっており、混乱してました。
本当にありがとうございました。

15
hiroton 2024/05/16 (木) 11:04:36 17372@f966d

オブジェクト変数の作成

変数宣言が必要なのは同一のオブジェクトを操作する必要があるとき、ですかね

間違った実装

OpenDatabase("C:\daosample\sample.accdb").Execute strSQL　'データベースを開いて（仮想A）クエリを実行
OpenDatabase("C:\daosample\sample.accdb").Close           'データベースを開いて（仮想B）、そのデータベース（仮想B）を閉じる
'//操作不能な仮想Aデータベースが開かれたままで不具合の原因になる

hatenaさんも解説されていますが、Withステートメントを使うとこんな時でも変数宣言をしなくてもよくなります

With OpenDatabase("C:\daosample\sample.accdb")
    .Execute strSQL
    .Close
End With

複雑になってくると、どのオブジェクトをWithステートメントで省略したらいいか悩みどころで、省略しないほうが見やすいコードという場合もあります

Dim rsA As Recordset, rsB As Recordset, rsMe As Recordset

Set rsMe = Me.Recordset.Clone
Set rsA = CurrentDb.OpenRecordset("T_A")
Set rsB = CurrentDb.OpenRecordset("T_B")

Do Until rsMe.EOF
    If Me!テキストボックス = "なんかやる" Then
        Do Until rsA.EOF
            Do Until rsB.EOF
                If rsA.f1 = rsB.f1 Then
                    '//なにかする
                    Exit Do
                End If
                rsB.MoveNext
            Loop
            '//なにかする
            rsA.MoveNext
        Loop
    End If
    rsMe.MoveNext
Loop
rsA.Close
rsB.Close
rsMe.Close

コードが長くなってくると、//なにかするのところでWithで省略した記述がrsMeなのか、rsAなのか・・・と本人でも混乱するようになってくるので見やすさ重視で変数を使うなんてこともよくあります

Application.CurrentDb メソッド (Access)
Access の CurrentDB はキャッシュしよう（IT raises ITさん）

CurrentDbについても、本来は

Dim db As Database
Set db = CurrentDb

db.Execute sql1
db.Execute sql2
db.Execute sql3

としたり、またはWithステートメントで参照して使用したほうが正しい使い方ができます。上記のような、クエリを複数回実行するのような場合を考えると、実は「CurrentDb.Execute」を繰り返すとは少し違う動作をします

まぁ、よほどのことがない限り、ただクエリを実行するだけであればCurrentDb.Executeの1行だけで済ませられるメリットを優先で問題ないので、それで回答している感じですね

17
tom 2024/05/16 (木) 12:33:22 ddfe5@07fee

hirotonさん・skさんもありがとうございます。実際使って試しなががら勉強していく様にします。それにしても奥が深いものですね。でも上手く出来た時は感激しますので頑張っていきます。
色々とありがとうございました。