人に聞けない痔の悩み、これでスッキリ >>

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

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

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

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

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

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/
    • good
    • 0

訂正です。



#3で、

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

としていましたが、

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

にしてください。
    • good
    • 0

#3です。


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

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


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

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


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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QSQLの速度をあげるには・・・

テキストファイルからキーワードを拾って
SQLをなげています
SQLの質問になってしまうかもしれません
いまはADO接続でやっています

Open ファイル as input......

SQL = select * from tbl where name like '%キーワード%'
execute(SQL)

レコードセットの値で処理をいろいろ・・・
Loop

もともとのDBの件数がものすごくおおくてselect文に結構な時間が
かかってしまいます。速度をあげるほうほうってあるのでしょうか
私にはおもいつかなくて・・・

こういったほうほうは どう? ってのがありましたら
おしえていただきたいのですが よろしくおねがいします。

Aベストアンサー

確かに・・・Like演算子・・・あまり使いたくないですね・・・
文字列比較は処理を遅くさせるし、増してや「=」ではなくLikeですから、膨大な時間がかかる恐れが・・・

ちなみにぼく自身、DB系を多くしています。今の仕事もDB系なのですが、元となるホストは他の会社が行っており、それにあわせて作らなければなりません。
で、そのホスト連携部分に文字列を比較しなければならない部分があるんですよ・・・・
自分の会社ならまだしも、他の会社がすでに設計済みのDBだから変えようがないのです。。。

まぁ愚痴っても仕方ないか・・・


なのでぼくも(不本意ながら)Like演算子を使用しています。


長い前置きはさておき・・・

本題のSQLのスピードなのですが、
http://homepage2.nifty.com/inform/vbdb/addnew.htm
こちらに面白い記述がありました。

AddNewにかかるスピードの検証で
Access データベースの場合: AddNew のほうが INSERT INTO より5倍以上速い
SQL Server の場合: INSERT INTO のほうが AddNew より 1.4倍 くらい速い
とあります。


たぶんで物を言ってはいけないと思うけど、言っちゃいます。

(1)もしDBがアクセスで
 >レコードセットの値で処理をいろいろ・・・
 のところがUpdate用のSQL文で処理を行ってる場合

 Recordsetをして、処理を行う


(2)もしDBがアクセス以外で
 >レコードセットの値で処理をいろいろ・・・
 のところがRecordsetで処理を行ってる場合

 Recordsetをせずに、UPDATE用のSQLを実行する


未検証なのですが多分イメージとして、こういうパターンが各DBに適してるのかな?

すでにこのパターンなのであれば、意味ないですね(^^;)

参考URL:http://homepage2.nifty.com/inform/vbdb/addnew.htm

確かに・・・Like演算子・・・あまり使いたくないですね・・・
文字列比較は処理を遅くさせるし、増してや「=」ではなくLikeですから、膨大な時間がかかる恐れが・・・

ちなみにぼく自身、DB系を多くしています。今の仕事もDB系なのですが、元となるホストは他の会社が行っており、それにあわせて作らなければなりません。
で、そのホスト連携部分に文字列を比較しなければならない部分があるんですよ・・・・
自分の会社ならまだしも、他の会社がすでに設計済みのDBだから変えようがないのです。。。

...続きを読む

QAccessのRefresh・Requery・Repaintの違い

Requeryはもう一度ソースレコード(テーブル)を読み込むようです。このとき、テーブルの先頭レコードに移動してしまいます。
Refreshは最新のレコード(テーブル)を再表示するような気がします。レコードの移動は起こらない気がします。
Repaintは、VBAでキャプションなどを変更したとき使っています。
でも、よくわかっていません。
どんなときにどんなメソッドを使えばいいのでしょうか?
詳しい方、よろしくお願いいたします。

Aベストアンサー

たびたびすみません。
調べてたらこんなのがでてきました。
http://www.nurs.or.jp/~ppoy/access/access/acF007.html

参考URL:http://www.nurs.or.jp/~ppoy/access/access/acF007.html

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

QAccessにインポートしたら並び順が変わっちゃった

Access2000 を使っています。
ExcelデータをAccessテーブルにしようとしましたが、全項目をテキスト型で入れたいのに、自動的に各項目の型が変わってしまうので、一旦CSVに落としてAccessにインポートしました。
ところが、インポートされたテーブルではレコード順が違っちゃっているんです。
総数は変わっていませんから、全て入ってはいるんでしょうが。
いったい何がいけないんでしょうか。多量のデータをインポートするのに不安でいけません。誰か教えて下さい。

Aベストアンサー

>インポート等においてAccessが順番をあえて変えてしまうことに言及していただけていませんが、Accessは常にそういう余計な動作をするものなのですか。

データベースでは、処理的に一番早く追加できる領域に追加をしていきます。

そのため、最適化されていないテーブル(以前は値が入っていたが、現在はレコードの削除があったなど)では、以前のレコードの情報が残っていたりすると、テーブルの一番最後に追加するのが高速であったり、以前レコードが存在していた領域を上書きするのが高速だったりという感じでどこに追加されるかわからない状態になります。

なので、最適化をしてからデータをインポートすれば、その順番に入るかもしれません。
ただし、たしかAccessでは、主キーが張ってあるテーブルはインポートのレコード順に関係なく主キーの値の昇順になったような???

QSQL文について(片方のテーブルに存在しないレコード抽出)

以下のような2つのテーブルがあったとして、
2つともに存在する「店コード」を抽出するのはSQLは分かるのですが、
片方に存在しない「店コード」(以下の例の場合、「2」)を抽出するSQLを
一文で書くにはどうすればいいのでしょうか?

<店テーブル>
店コード住所・・・(その他、基本情報)
1aaa
2bbb
3ccc

<販売テーブル>
店コード販売品目・・・(その他、販売数など)
1xxx
3zzz

Aベストアンサー

オプティマイザ次第だけど、NOT-INは、あまりお勧めでない。
外部結合も索引があっても有効に使われないので、お勧めでない。

select * from A where not exists(select 1 from B where A.店コード=B.店コード);

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

QAccessでテーブル名やクエリ名一覧の抜き出し

Accessでテーブルやクエリを沢山(100個以上?)使っております。

そこで、テーブル名やクエリ名の管理をしたいので、テーブル名(クエリ名)の一覧を抜き出したいのですが、どうすればいいのでしょうか?
または、そういうことは無理なのでしょうか??(;O;)

1個づつコピーペーストでテールブル名をエクセルに貼り付けて行こうかな?と思ったのですが、さすがに数が多すぎるので困っております。

できるだけ簡単な方法がいいのですが、もしなければVBAでもいいです。

おわかりの方がいらっしゃいましたら、よろしくお願いします。

Aベストアンサー

・クエリを新規作成
・以下SQLを貼り付け
SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.Flags
FROM MSysObjects
ORDER BY MSysObjects.Type, MSysObjects.Name;
・デザインビューで表示
あとは、TypeとFlagsの条件を変えてあげれば一覧できます。

QOfficeアクセス テーブル「テキスト型」⇒「日付型」への変換について

テーブルにデータが保管されており、そのフィールドの一つに「テキスト型」で”20080301”のようなデータを持っています。これを「日付/時刻型」”2008/03/01”のように変換したいのですが、どのようにすればよいのでしょうか。

※ちなみにこのオリジナルデータは毎日、自動的にテーブルにダウンロードされており、このテーブル自体の型を予め「日付/時刻型」に変換すると、日々のデータ取得時にエラーとなってしまいます。ですので、「日付/時刻型」のフィールドを持った新規のテーブルに、この”20080301”のデータを変換し格納したいのですがどのようにすればよろしいでしょうか。

テーブル⇒デザインより、該当のフィールドの型を「テキスト型」⇒「日付/時刻型」へ直接変換してしまうと、「データの変換中にエラーが発生しました。○○件のレコードのデータが失われました。」と表示されます。素人のため、なるべく追加クエリや更新クエリや簡単な関数のみでの方法を教えていただけたら幸いです。

Aベストアンサー

テーブルに日付時刻型フィールドを追加して、
更新クエリを使って、#1さんが提案されている
数式を使えばよいのでは?

Qテーブルからのselectにおいてデータの有無により結果をわけたい

id | point
----+-------
1 | 10
2 | 9
3 | 5
....
というテーブルがあるとします.
idを指定してpointを得たいのですが、そのidがこのテーブルに存在しない場合は空の結果ではなく0を返したいのです.
plpgsqlなどを使いif文で場合分けすればできることはわかっているのですがSQL文だけで(それもできれば1文で)これを実現する方法はあるでしょうか?
よろしくお願い致します。

Aベストアンサー

変則的ですが、これでよければidがユニークでなくても大丈夫ですし、集合関数を使わなくてもOKです。

select dm.id,case when ex1.point is null then 0 else ex1.point end from
(select ? as id) as dm left join ex1 on dm.id = ex1.id;

?を適当に変えてください。
chukenkenkouさんの発想はこれですよね。

Q【SQLServer】IS NULLのパフォーマンス

お世話になっております。SQLSERVER初心者です。

NULLを含む列COL1を検索条件に入れる場合、
パフォーマンスの観点から

WHERE COL1 IS NULL とするのではなく
WHERE ISNULL(COL1,'') = '' とするよう有識者から言われました。

そこで質問なのですが、

(1)IS NULL は基本的に上記のように変換したほうが早くなるのですか?
(2)COL1にもしインデックスが設定されていたとしても、上記の場合だとどちらも効かないですよね?

詳しい方おしえてください。
よろしくおねがいします。

Aベストアンサー

試しにこちらの適当なテーブルに索引をつけて検索してみました。

(1)WHERE COL1 IS NULL
INDEX SEEKになりました

(2)WHERE ISNULL(COL1,'') = ''
INDEX SCANになりました

INDEX SEEKなので(1)の方が効率がよさそうです。
ManagementStudioでSQL実行時に「実際の実行プランを含める」のオプションつきで実行してみてください。

でも、(2)は長さ0の空文字も対象にするから(1)と(2)は結果が変りますよね。このことを考慮してますか?

※長さ0の文字列とNULLを区別するかどうかはDBによって異なります。


人気Q&Aランキング

おすすめ情報