【Access2007について】

質問

宜しくお願いします。

現在、Access2007のVBAを使って約22万件のレコードから検索条件を指定し、特定のレコードを抽出しようとしているのですが、抽出するテーブルのレコードが約22万件存在するため、抽出処理が途中で止まってしまい、最終的に（応答なし）の状態になってしまいます。

そのテーブルには検索条件で使うフィールドをインデックスとしているため、若干早いとは思うのですが・・・

この問題を解決する方法はあるのでしょうか・・？
どなたかご教授を宜しくお願い致します。。。

回答No.5

nda23 · ベストアンサー

ワイルドカード検索だからです。
この場合はインデックスによる検索は機能しないので、
１件ずつの条件抽出になります。

気になった点を挙げます。
(1) エンコード
If Nz(Me.txtカナ氏名, "") <> "" Then
　　　　If Len(zyuFilter) > 0 Then
　　　　　　　　zyuFilter = zyuFilter & " AND ((検索テーブル.カナ氏名) LIKE '*" & Me.txtカナ氏名 & "*')"
　　　　Else
　　　　　　　　zyuFilter = zyuFilter & " ((検索テーブル.カナ氏名) LIKE '*" & Me.txtカナ氏名& "*')"
　　　　End If
End If
↓
If Nz(Me.txtカナ氏名, "") <> "" Then
　　　　If Len(zyuFilter) > 0 Then　zyuFilter = zyuFilter & " AND "
　　　　zyuFilter = zyuFilter & "カナ氏名 LIKE '*"
　　　　zyuFilter = zyuFilter & Replace(Me.txtカナ氏名, "'", "''") & "*'"
End If
このようにすべきでしょう。
同じコーディングを続ける（コピペがあるのでやりがち）のはバグの元です。
また、入力データに'(アポストロフィ)が含まれるとSQL構文エラーになるので、
念のためエンコードします。
また、テーブルが１個しかないので修飾はなくても問題ありません。

(2)直接実行
DoCmd.OpenQuery "作業テーブル_削除"
DoCmd.RunSQL zyuSQL
↓
CurrentDb.Execute "DELETE FROM 作業テーブル"
CurrentDb.Execute zyuSQL
この方法だとSetWarningは不要です。

(3)DCount
この系統の関数は避けるべきです。
もし、DBが他のものに代わり、ODBCリンクテーブルになった場合、
物凄く時間がかかります。Dfirstなども同じです。面倒でも
SQLからレコードセットを作って値を得るようにする必要があります。
また、レコード件数ならMe.SUB.Form.Dynaset.RecordCountで
求められますので、関数やクエリは不要です。

(4)作業テーブル
キーやインデックスが多いのとか？
また、作業テーブルを使う必然性は何でしょう？サブフォームの
RecordSourceのSQLを直接書き直せば済むと思いますが・・・

お礼

ご回答有難うございます。

確かに作業テーブルのメリットがほぼない事に気づいたのでフォームのレコードソースをいじる方法に切り替えました。またDCountについても使用は控え、recordset.Recordcountで行いましたが、ここで処理が止まってしまいました。そのためレコードセットを別で行い、件数を抽出するだけの処理を行っています。エンコードについては知らなかった箇所も幾つかあったので教えて頂き有難うございます。

回答No.4

noname#182251

＃３です。補足されていたのに気付きませんでした。

回答ではないのですが、

１．補足で提示されたコードが生成するＳＱＬ文を具体的に示せないでしょうか
２．「抽出処理が途中で止まってしまい」と有りますが、止まっているのか、単に時間がかかっているだけなのか。パフォーマンスモニターや、タスクマネージャーでＣＰＵの状態を見ると、どうなのでしょうか
３．22万件を減らすとどうなるでしょうか

お礼

ご回答有難うございます。ご連絡が遅くなってしまい申し訳ありません。

あれから試行錯誤を繰り返したところ、作業テーブルを失くして検索するテーブルを直接見に行くように設定しました。（フォームのレコードソースを操作する方法です）
あと、ご回答頂きました内容についてタスクマネージャーで確認すると「応答なし」と表示されています。後22万件を減らすことはどうしても不可能です。。

現在は、検索自体はかなり処理速度が上がり、多少使えるようになってきたのですが、件数を抽出（recordset.Recordcountで行っております）する所で同じ現象が起こってしまうため、別でレコードセットして件数だけ抽出しています。

回答No.3

noname#182251

もしＶＢＡで１レコード毎調べているならば、恐ろしく時間がかかると思います。

ＳＱＬ文で処理しているならば、関係テーブルとそのフィールド、そしてＳＱＬ文を提示すれば、良いアドバイスが得られるかも知れません。

補足

ご回答有難うございます。

下にコードとテーブルの関係を記載しましたので、どうぞ宜しくお願い致します。

回答No.2

nda23

20万レコードくらいで、そう遅いはずはないと
思いますが、PCのスペック（特にメモリ量）が
低いのかも。

あと、技術的なことですが、レコード数の多い
レコードセットにFind系メソッドを使うと、物凄く
遅くなります。特にODBCリンクのものでは
少数のレコードしかない場合でも遅いです。
なので、Find系メソッド（DFirst関数等含む）は
普段から使わないように心がけるべきです。

また、レコードセットを作るSQLも注意すれば
効率を上げられる可能性があります。例えば
条件式の書き方やサブクエリの使用などです。
但し、劇的な効果は期待薄ですが・・・

補足

ご回答有難う御座います。

Private Sub 検索_Click()

Dim zyuFilter As String
Dim zyuCount As Long
Dim zyuSQL As String

'4つの検索項目入力チェックとWHERE句作成

zyuSQL = ""
zyuFilter = ""
zyuCount = 0

If Nz(Me.txtコード, "") <> "" Then
zyuFilter = zyuFilter & " ((検索テーブル.コード)= '" & Me.txtコード& "')"
End If
If Nz(Me.txtカナ氏名, "") <> "" Then
If Len(zyuFilter) > 0 Then
zyuFilter = zyuFilter & " AND ((検索テーブル.カナ氏名) LIKE '*" & Me.txtカナ氏名 & "*')"
Else
zyuFilter = zyuFilter & " ((検索テーブル.カナ氏名) LIKE '*" & Me.txtカナ氏名& "*')"
End If
End If
If Nz(Me.txt漢字氏名, "") <> "" Then
If Len(zyuFilter) > 0 Then
zyuFilter = zyuFilter & " AND ((検索テーブル.漢字氏名) LIKE '*" & Me.txt漢字氏名 & "*')"
Else
zyuFilter = zyuFilter & "((検索テーブル.漢字氏名) LIKE '*" & Me.txt漢字氏名 & "*')"
End If
End If
If Nz(Me.txt生年月日, "") <> "" Then
If Len(zyuFilter) > 0 Then
zyuFilter = zyuFilter & " AND ((検索テーブル.生年月日)= '" & Me.txt生年月日 & "')"
Else
zyuFilter = zyuFilter & " ((検索テーブル.生年月日)= '" & Me.txt生年月日 & "')"
End If
End If

If (Len(zyuFilter) = 0) Or IsNull(zyuFilter) Then
If MsgBox("検索条件を指定して下さい。", vbOKOnly, "検索項目確認") =
vbOK Then
Exit Sub
End If
Else
zyuSQL = "INSERT INTO 作業テーブル SELECT 検索テーブル.* FROM 検索テーブル WHERE("
zyuSQL = zyuSQL + zyuFilter + ");"
End If

'ワークテーブル再度削除
DoCmd.SetWarnings False
DoCmd.OpenQuery "作業テーブル_削除"

'抽出処理実行
DoCmd.RunSQL zyuSQL
DoCmd.SetWarnings False
Me.SUB.Requery

'画面抽出結果表示
zyuCount = DCount("*", "作業テーブル")
Forms!検索フォーム!SUB.Form!txt合計 = zyuCount
If zyuCount = 0 Then
MsgBox "対象データが存在しません。", vbOKOnly + vbInformation
End If
End Sub

となっておりまして、「検索テーブル」の情報をVBAで抽出して「作業テーブル」に追加しています。
RunSQLのところでいつも止まってしまうのですが、Do～Loop文にしたほうがよいでしょうか？

宜しくお願い致します。

回答No.1

ShowMeHow

アクセスに２２万件のレコードを入れて、業務用に利用しているのであれば他のデータベースに移行することをお勧めします。

リンクテーブルを使って、ほかのDBのデータを見ているのであれば、じかにそのDBに接続するほうが早い場合もあります。　また、クエリプロパティなどでODBCのタイムアウトの設定を変更することによってタイムアウトの時間を変えることができます。

どうしてもアクセスにデータを入れなくてはいけないなら、
適正なインデックスをつけるほかに有効な手段としては、テーブルを分散させるしかないと思います。
どんなレコードなのかはわかりませんので、たとえばがいいにくいのですが、
販売の記録などであれば、月ごとに別テーブルにするとか、、、

補足

ご回答有難うございます。

補足としてコードを記載しましたので、宜しければご回答をお願い致します。