Microsoft Access 掲示板

Microsoft Access 掲示板

ADOで該当フィールドの全レコードを削除する方法

11 コメント
views
4 フォロー

質問したいことが2点あります。

1
最初に、番号フィールドの全レコードを削除、もしくはNullに変更したいのですが、方法が分かりません。

adoであれば、
 strSQL = "DELETE FROM 出勤データ where 番号>0"
 adoCn.Execute strSQL
で削除できるのですが、最初にadoで処理して閉じるのは、かなり不細工です。

ADOで、削除する方法がありましたら、お願いいたします。

2
1度番号を付けてから、再度janと番号で検索して、対象データを取得しているのですが、
該当する番号がヒットした時に、対象データだけを取得してLoopを抜けて、終了する方法は無いのでしょうか？

    Dim daoCn As dao.Database 'Databaseオブジェクトを扱う変数（DB）を宣言
    Dim daoRs As dao.Recordset 'Recordsetオブジェクトを扱う変数（RS）を宣言
    Dim strSQL As String
    Dim i As Long
    Dim i2 As Long

    strFileName = "出勤 - コピー.accdb"
    Set daoCn = DBEngine.Workspaces(0).OpenDatabase("C:\Users\tasuk\Desktop\VBA\経理\出勤\" & strFileName)

　　　　　　　　　＜　ここで、番号フィールドのレコードを削除したいです。

    jan = "1000000000016"
    i2 = 2

    Cd = "個人データ.氏名,出勤データ.月日,出勤データ.番号"
    Tn = "個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan"
    Sc = " 出勤データ.jan = '" & jan & "'" '検索条件の確定

    strSQL = "SELECT " & Cd & " FROM " & Tn & " WHERE " & Sc & " ORDER BY 出勤データ.月日 DESC" '古い順はASC
    Set daoRs = daoCn.OpenRecordset(strSQL, dbOpenDynaset) 'DAOレコードセットオブジェクトを作成

    If daoRs.RecordCount <> 0 Then
        i = 1
          Do
           daoRs.Edit
            daoRs!番号 = i
              If i = 2 Then
                  daoRs.Update
                   Exit Do
                 Else
                   i = i + 1
                     daoRs.MoveNext
                End If
         Loop Until daoRs.EOF = True
    End If
     daoRs.Close

     Sc1 = " 出勤データ.jan = '" & jan & "'" '第１検索条件の確定
     Sc2 = " 出勤データ.番号 = " & i2 '第2検索条件の確定
     Sc = Sc1 & " and" & Sc2 '検索条件の確定
     strSQL = "SELECT " & Cd & " FROM " & Tn & " WHERE " & Sc
     Set daoRs = daoCn.OpenRecordset(strSQL, dbOpenDynaset) 'ADOレコードセットオブジェクトを作成
     Worksheets(4).Range("A1").CopyFromRecordset daoRs

　　　daoRs.Close

End Sub

タークン
作成: 2023/08/22 (火) 19:12:06
通報 ...
1
りんご 2023/08/23 (水) 00:38:49 935bc@0e907

回答の前に、番号について説明をしてほしい。Aさんの8/23の番号1、2、3みたいになるとして、違いは何？

2
タークン 2023/08/23 (水) 10:14:11 33fc6@2705a

日付を降順でソートした順番です。

3
タークン 2023/08/23 (水) 10:51:34 33fc6@2705a

ひょっとすると、一括処理は無理なのでしょうか？
accessのSQLビューを見ても、役に立たなかったです。
となると、Loopで１件づつということになりますが。

ADOのstrSQL = "DELETE FROM 出勤データ where 番号>0"にしても、
結局は１件づつ処理してるわけですし。

１件づつ処理するしかないのであれば、コードは長くなるけどDAOの方が有利になります。

テーブルすべてのデータは一括で削除できるので、
フィールドを指定すれば済む話のような気がして
モヤモヤが治まりません。
4
hatena 2023/08/23 (水) 13:53:10

回答の前に

前の質問のやりとりでも感じたのですが、質問のキャッチボールがうまくできていない印象です。
質問内容があいまいなので、こちらが不足部分を推測しないと回答できないことが多いです。また、推測が入ると実際とは相違があり的確に回答にならない場合があります(つまり無駄骨になる確率が高い)
相手に正確に状況、ご希望のことが正確に伝わるようになるべく詳細に説明するように心がけてください。
回答者が質問する場合、回答に必要な情報ですので、それにも正確かつ詳細に回答してください。

回答の前の確認

1
最初に、番号フィールドの全レコードを削除、もしくはNullに変更したいのですが、方法が分かりません

レコードの削除と、フィールドの更新はまったく別の処理です。
やりたいことは、レコードの削除ですか。
(レコードを削除すると、他のフィールドのデータも失われますが大丈夫ですか)
それともフィールドの更新ですか。

レコードの削除なら、どのようなレコードを削除するのか、
フィールドの更新なら、どのフィールドを何に変換するのか、
詳細に説明してください。

あと、ADOにこだわっているようですが、ADOでもDAOでも基本的にできることに大差はありません。

2
1度番号を付けてから、再度janと番号で検索して、対象データを取得しているのですが、
該当する番号がヒットした時に、対象データだけを取得してLoopを抜けて、終了する方法は無いのでしょうか？

これもやりたいことを詳細に正確に説明してください。

コードをざっとみて、推測した感じでは、やろうとしていることは下記のようなことだ思いましたが、あってますか。

指定したjanに該当するデータの新しい順にならべて2番目のデータを取得して、それをエクセルシートに貼り付けたい。
5
hatena 2023/08/23 (水) 15:09:30 >> 4

もし、やりたいことが

指定したjanに該当するデータの新しい順にならべて2番目のデータを取得して、それをエクセルシートに貼り付けたい。

ということなら、下記のコードでいいでしょう。

Public Sub Sample()
    Dim daoCn As dao.Database 'Databaseオブジェクトを扱う変数（DB）を宣言
    Dim daoRs As dao.Recordset 'Recordsetオブジェクトを扱う変数（RS）を宣言
    Dim strSQL As String
    Dim i2 As Long

    strFileName = "出勤 - コピー.accdb"
    Set daoCn = DBEngine.Workspaces(0).OpenDatabase("C:\Users\tasuk\Desktop\VBA\経理\出勤\" & strFileName)

    jan = "1000000000016"
    i2 = 2

    Cd = "個人データ.氏名,出勤データ.月日,出勤データ.番号"
    Tn = "個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan"
    Sc = " 出勤データ.jan = '" & jan & "'" '検索条件の確定

    strSQL = "SELECT TOP " & i2 & Cd & " FROM " & Tn & " WHERE " & Sc & " ORDER BY 出勤データ.月日 DESC" '降順でi2番目までのデータを取得
    strSQL = "SELECT TOP 1 * FROM (" & strSQL & ") ORDER BY 出勤データ.月日" '上記のデータから昇順で1番目のデータを取得(結局降順でi2番目のデータ)
    Set daoRs = daoCn.OpenRecordset(strSQL, dbOpenDynaset) 'DAOレコードセットオブジェクトを作成
    
    Worksheets(4).Range("A1").CopyFromRecordset daoRs
    daoRs.Close

End Sub
6
りんご 2023/08/23 (水) 15:38:34 935bc@0e907

ひょっとすると、一括処理は無理なのでしょうか？…フィールドを指定すれば済む話のような気がしてモヤモヤが治まりません。

　個人的な意見です。MsAccessは、流行り廃れの成長期を過ぎて、こういうもんだよねと結論が出ています。たぶん検索すればLoopで1件ずつ処理がヒットするはずなので、そういうもんだと見切りをつけます。もしかすると、一括処理する機能が公式リファレンスのどこかに埋もれているかもしれませんが、もっと将来性がある開発基盤に期待したほうが有意義です。

1度番号を付けてから、再度janと番号で検索して、対象データを取得しているのですが、
日付を降順でソートした順番です。

　個人的には、1度番号(検索用)を付けたら変更しません。番号(表示順)であれば、動的に変わるのかしら、という設計をちらほら見かけたことはありますが、ちょっとすぐに例示できません。
　
>No4

　これは、あるあるなので共感します。主にコーディングで活動されているのかな、と思いながら回答してみましたが、なかなか大変です。

10
りんご 2023/08/23 (水) 19:20:49 935bc@0e907 >> 6

もしかして、If i = 2 Thenは、If i = i2 then の打ち間違えかしら。念の為、追記しておきます。
7
hatena 2023/08/23 (水) 16:55:05 修正

ひょっとすると、一括処理は無理なのでしょうか？

これに関しては、どのような一括処理なのか言及されてませんので、
無理とも可能とも判断のしようがありません。

連番を振りたいということに関してなら、更新クエリで一括でする方法はあります。

全レコードの番号フィールドをNullにしたいなら、更新クエリで一発です。

UPDATE 出勤データ SET 出勤データ.番号 = Null;
8
タークン 2023/08/23 (水) 18:31:36 33fc6@2705a

すみません。
DAOの存在を２日ほど前に知ったばかりで、コードの書き方も手探りの状態で、いっぱいいっぱいの状態で失礼なやり取りになっております。
daoRs.Editって、宣言が必要なんかい
そもそも、Editを.の後ろに付けて命令するのか
のレベルで、身動きが取れない状態です。

ただ、処理速度は抜群に早いです。

前回教えていただいた、ADOでサブクエリを使うコードのADO版ということだと思いますので、
サブクエリを使うときに利用させていただきます。

deleteを調べたら、
delete文は、該当する行をまるごと削除する用途で使われるSQL文です。一方で、特定のフィールドだけを削除することはできません。例えば「該当する行のうち、一部の列に格納されたデータだけを削除したい」といった用途では使えません。
delete文は行の削除には使える一方で、列を削る用途には使えません。この操作は、ALTER TABLE文に「DROP COLUMN (削除する列名)」をつけることで行なえます。
ということがく分かりました。

Nullに置き換えなければなりませんでした。
危なかったです。

Nullへの書き換えですが、
ADOでは、
strSQL = UPDATE 出勤データ SET 出勤データ.番号 = Null;
adoCn.Execute strSQL, lRecordAffected
で、一括処理できているようなのですが

DAOのコードは、４時間近くかけてLoopさせる方法から前進しません。
hatenaさんが言うとおり、たいていは同じ処理ができるはずだと思うのですが。
お手数かけますが、アドバイスお願いいたします。

9
hiroton 2023/08/23 (水) 19:08:24 cd38d@f966d

既に言われていることですが、用語の使い方が間違っているので質問の意図が正しく伝わってきません。「ADO」や「DAO」も、ところどころ、逆の用語ではないか？と思うところがあります
質問を投稿する前によく読みなおして、正しい質問になるようにしてください

DAOにもクエリ（SQL構文）をそのまま実行するメソッドが存在します。メソッド名もADOと同じExecuteです

Database.Execute メソッド (DAO)
QueryDef.Execute メソッド (DAO)
Connection.Execute メソッド (DAO)

参考：保存済みクエリーを使うか、VBAでSQL文を直接発行するか？（T'sWareさん）

単純なSQL構文であれば

CurrentDb.Execute SQL構文

だけで動作します

11
タークン 2023/08/24 (木) 12:02:47 33fc6@2705a

adoCn.Execute strSQL
を
daoCn.Execute strSQL
に変えるだけで良かったのですね。

    strSQL = "SELECT TOP " & i2 & Cd & " FROM " & Tn & " WHERE " & Sc & " ORDER BY 出勤データ.月日 DESC" '降順でi2番目までのデータを取得
    strSQL = "SELECT TOP 1 * FROM (" & strSQL & ") ORDER BY 出勤データ.月日" '上記のデータから昇順で1番目のデータを取得(結局降順でi2番目のデータ)

にした方が、並べ替えるだけなので、早いと思います。

色々と、有難うございました。