１０万件のデータに対してチェックを行う。

こんにちは。Access 2003を使用しています。Accessのテーブルには１０万件程度のデータがはいっています。

Excelを開き、１行目から順に読み取って、テーブルにインサート・アップデートをしていきます。

例えば、Excel A列にIDがあったとします。AccessのテーブルにそのIDがなければ、インサート、あればアップデートという仕組みにしています。

データ件数が増えてきて、今は１０万件に対し、８万件がアップデートの対象です。処理が終わるのに物凄く時間がかかります。

何かよいアイディアはないでしょうか？例えば、こういった技術を使えばいいとか、こっちの技術を勉強して乗り換えたほうがいいよとかです。よろしくお願いします。

No.3 ベストアンサー 回答者： piroin654 回答日時： 2010/12/19 19:13

ExcelをAccessにリンクしてリンクテーブル

とします。Accessのバージョンによっては
Excelは読み取りのみになりますが、この場合は
読み取りでいいのでそのままリンクします。
リンク方法は外部ファイルのテーブルをリンクする場合
と同じですが、ExcelのSheet名がテーブル名
になり、フィールド名はExcelで設定している
たとえば、A1、A2などにID、商品名などと
設定しているとそれをフィールド名として
設定できます。
ファイルから「外部データの取り込み」を
選択し、「テーブルのリンク」を選択し、
ファイルの種類をExcelに変更し、フォルダに
移動してExcelファイルを選択するとウィザードが
起動されます。

次に、クエリを作成します。たとえば、Excel
とAccessにID、商品名があるとして、
先に更新クエリを実行します。

UPDATE Tdata
INNER JOIN Sheet1
ON Tdata.ID=Sheet1.ID
SET Tdata.商品名=Sheet1.商品名
WHERE Tdata.ID=Sheet1.ID;

続いて、追加クエリを実行します。

INSERT INTO Tdata
SELECT * FROM Sheet1
WHERE ID NOT IN (SELECT ID FROM Tdata);

このような方法はどうでしょう。なお、
フィールドが他にある場合はクエリの
デザインビュー、あるいはSQL文の中で
追加してください。

なお、
実行時エラー「ファイルの共有ロック数が制限を超えています」
というエラーが出れば以下。
http://support.microsoft.com/kb/209940/

No.5 回答者： piroin654 回答日時： 2010/12/19 21:06

訂正です。

#3で、

＞たとえば、A1、A2などにID、商品名などと

としていましたが、

＞たとえば、A1、B1などにID、商品名などと

にしてください。

No.4 回答者： piroin654 回答日時： 2010/12/19 19:44

#3です。

Tdataというのはテーブル名です。

No.2 回答者： fuuten_no_neko 回答日時： 2010/12/19 06:54

８万件のデータはExcelに記録されているのでしょうか？

それならば一旦Accessに読み込み（Accessのテーブルにする）してから追加クエリで処理すれば圧倒的に早くなるはずです。「一旦Accessに読み込み」時の時間は試したことがないですが、それほどかからないのでは？

No.1 回答者： shupla1976 回答日時： 2010/12/19 03:10

上の内容を見る限りindexが付いていないような気がします。

AccessテーブルのID列にindexは付いていますか？
付いていなければ付けましょう。劇的に早くなる筈です。

付いていても遅いのであればAccessデータをSQL Serverに入れて試してみたらいかがでしょうか。