FS社の Access「月次処理」の改修で、コーディングを終え検証したら‥‥
ググったら、Office.Microsoft.comに、次のようにあった。
"指定されたテーブルから削除できませんでした。" というエラー メッセージが表示される
1 回の操作でテーブルから複数のレコードを削除するには、削除クエリを使用します。削除クエリの処理が正常に行われるのは、以下の場合です。
- 他のテーブルとリレーションシップのない単一のテーブルを対象としている場合。
- 一対一リレーションシップを持つ 2 つのテーブルを組み合わせている場合。
- 一対多リレーションシップの "一" の側の単一のテーブルを使用しており、かつ、そのリレーションシップで連鎖削除が有効になっている場合。
- 一対多リレーションシップの "一" の側のテーブルと、同じリレーションシップの "多" の側のテーブルの両方が含まれている (たとえば、"多" の側のテーブルのフィールドに基づく抽出条件を使用している) 場合。
自分のケースは 2. に反していると思われる。
で、「ADOで処理するしかない」が、この10年間使っていないしなぁ...
■ Find か Seek か
- VB データ アクセスのための比較が、Find メソッドとシーク (Support.Microsoft.com)
Seek と find メソッドは、適用するレコードの種類では、パフォーマンスに違いがあります。
- Find メソッド (FindFirst、FindLast、FindNext、および FindPrevious) はダイナセットとスナップショットにはテーブル オブジェクトに適用されます。逆に、Seek メソッドは、Table オブジェクトでのみ使用できます。
- Seek メソッドは、find メソッドよりも大幅に高速です。シークの順序を変更するには、Table オブジェクトのインデックス プロパティを変更するためにもより柔軟です。集中的な検索は、find メソッドと Seek メソッドを使用するには、開いているダイナセットにするようにテーブル オブジェクトを作成する可能性があります。
- レコード検索(ADO編) (Access_VBA講座)
01.Findメソッド
Recordset から指定した条件を満たすレコードを検索します。条件が一致すれば、そのレコードをカレントレコードにします。それ以外のときは,RecordsetオブジェクトのBOFプロパティまたはEOFプロパティがTrueになり,カレントレコードは未定義になります。
【書式】 recordset.Find Criteria, SkipRows, SearchDirection, Start04.Seekメソッド
Seekメソッドはテーブルに設定されているインデックスを利用して検索を行うため,Findメソッドよりも高速にレコードを検索できます。RecordsetオブジェクトのOpenメソッドのoptions引数にadCmdTableDirectを指定して作成したRecordsetオブジェクトだけで実行可能です。
【書式】 recordset.Seek KeyValues, SeekOption
■ 結果
Seekを使うにはテーブル オブジェクトを作る必要があるが、その時間を入れても Seekの方が早い‥‥ と、判断した。
色々制約があり、試行錯誤した結果、下記コードで目的を達した。(所要時間 約 6時間)
- Public Function update作業日報(arg削除期限 As Date) As Boolean
- On Error GoTo Err_update作業日報
- Dim mbTitle As String
- Dim CNN As New ADODB.Connection
- Dim RST As New ADODB.Recordset, RS0 As New ADODB.Recordset
- Dim isDrop As Boolean
- Dim maxCTR As Long, setCTR As Long
- Dim SQLCmd As String, strSQL As String, rs0SQL As String
- mbTitle = "update作業日報/" & MYObjName
- maxCTR = DCount("*", "D_作業日報")
- VARAns = SysCmd(acSysCmdSetStatus, "月次処理/作業日報 更新中: " & maxCTR)
- 'D_作業指図の受付Noのテーブルを作成する。(リンクテーブルでは不可)
- DoCmd.SetWarnings False
- strSQL = "Delete tmp受付No.* from tmp受付No"
- DoCmd.RunSQL strSQL
- strSQL = "Insert into tmp受付No (受付No) Select 受付No from D_作業指図" _
- & " Where ([受付No] Is Not Null)"
- DoCmd.RunSQL strSQL
- DoCmd.SetWarnings True
- 'D_作業日報を1件ずつ読み、削除対象は所属コードに"$"を付け加える。
- Set CNN = CurrentProject.Connection
- RS0.Open "tmp受付No", CNN, adOpenKeyset, adLockOptimistic, adCmdTableDirect
- RS0.Index = "受付No"
- strSQL = "Select 所属コード, 受付No, 作業月日 from D_作業日報"
- RST.Open strSQL, CNN, adOpenDynamic, adLockOptimistic
- setCTR = 0
- Do Until RST.EOF
- VARAns = SysCmd(acSysCmdSetStatus, "月次処理/作業日報 更新中: " & maxCTR)
- isDrop = False
- If IsNull(RST!受付No) Then
- VARAns = RST!作業月日
- If Not IsNull(VARAns) Then
- If CDate(VARAns) <= arg削除期限 Then isDrop = True
- End If
- Else
- '受付Noがあるか否かSeekする。
- RS0.Seek RST!受付No, adSeekFirstEQ
- If RS0.EOF Then isDrop = True
- End If
- If isDrop Then
- VARAns = RST!所属コード
- If IsNull(VARAns) Then
- RST!所属コード = "$"
- ElseIf Len(VARAns) = 1 Then
- RST!所属コード = VARAns & "$"
- Else
- MsgBox "作業日報の更新に失敗しました。システム管理者に連絡してください。", vbCritical, mbTitle
- GoTo Exit_update作業日報
- End If
- RST.Update
- setCTR = setCTR + 1
- End If
- RST.MoveNext
- maxCTR = maxCTR - 1
- Loop
- RST.Close
- RS0.Close
- '累積に追加して落とす。
- ' DoCmd.SetWarnings False
- DoCmd.OpenQuery "Q_作業日報を累積に追加する"
- DoEvents
- 'D_作業日報から削除する。
- SQLCmd = "Delete * from D_作業日報 Where (Right([所属コード],1)='$')"
- DoCmd.RunSQL SQLCmd
- DoEvents
- update作業日報 = True
- Exit_update作業日報:
- DoCmd.SetWarnings True
- Set CNN = Nothing
- Exit Function
- Err_update作業日報:
- MsgBox Err.Number & "/" & Err.Description, vbCritical, mbTitle
- Resume Exit_update作業日報
- End Function