【ACCESS Excel連携】Excelから、ACCESSのデータを更新したいときどうする？
ACCESSのデータをExcelから更新したいときがあります。
そのようなシーンには、今までほとんど、遭遇しませんでした。
しかし、最近、そのような事態になりましたので、紹介します。
目次
Excelから、ACCESSのデータを更新したいとき・・・
こんにちは。伊川(@naonaoke)です。
今回は、Excelから、ACCESSのデータを更新する方法を紹介します。
ACCESSの機能は、ACCESSで行うと決めていたのですが、どうしてもExcelから更新しなければならない事態になりました。
キッカケは、いつものように、競馬です。
ご興味のある人は下記の記事を参照してください。
【競馬 データ分析】競馬ブックスピード指数とCG表の関係
Excelは、AVEDEV関数が搭載されています。
AVEDEV関数は、平均偏差を求める関数で、標準偏差の仲間です。
統計学では、標準偏差を多用して、平均偏差は、あまり、利用されません。
しかし、その平均偏差をどうしても利用しなくてはならない場面がきました。
AVEDEV関数は、たぶん、ACCESSには搭載されていないと思います。
このブログはこんな人にお勧め
- ExcelとACCESSの連携を勉強している人
- Excelから、更新クエリを実行したい人
- データ更新のためだけに、テーブルを作成している人
このブログを、読み終わるころには・・・・
今回は、2つのパターンを紹介します。
1つ目は、結果だけを求める方法、2つ目は、更新クエリの方法です。
Excelから、ACCESSのデータを更新
私の実務でもよくありがちなのですが、社内の主幹システムから、
CAVファイルをダウンロードして、ACCESSのデータ更新をしたい時があります。
その時は、CSVファイル貼り付け専用のテーブルを作成しています。
しかし、最初は、便利と思ったのですが、慣れてくると、結構、面倒な作業です。
でも、今回のように、Excelに搭載されているが、ACCESSには、搭載されていない関数の場合は、Excelから更新をするのが一番です。
Excelから、ACCESSのデータを更新 作業手順 その1 データベース確認
今回のやりたいことは、上の図のようなことです。
まず、反則技を紹介します。
カンタンです。
いったん、ACCESSのデータを削除して、Excelのデータを、新に反映させるのです。
しかし、この方法は、ちょっと・・・・・では、ないでしょうか？
前回の周知未読野郎の記事と違って、知識を合わせてシステムを作成したのと、
同じく、知識を合わせたけど、後々、苦労するかもしれません。
今回のデータは、データ数が、14個ですが、もし、5万件のデータを、上記の方法で更新するのは、少し疑問が残ります。
やはり、ここは更新クエリを作成すべきです。
ACCESSは、大量データを扱うのです。
Excelから、ACCESSのデータを更新 作業手順 その2 別のデータベース確認
F列に、平均偏差(AVEDEV関数)があります。
全部レコードを削除しなくても、更新クエリでF列のデータを利用して、ACCESSのデータを更新します。
Excelから、ACCESSのデータを更新 作業手順 その3 Ecxcelで更新クエリを作成
この赤枠の中で、更新クエリを実行します。
但し、下記の書籍では、一括更新をするコードは記載されていません。
余談ですが、ExcelとACCSSの連携を勉強するなら、下記の書籍があれば十分です。
Excelのループでは、For～Nextを利用します。
しかし、ACCESSが絡んでくると、Do ～Loopを利用します。
更新クエリの完成です。
更新クエリに関しては、SQL文の中に、UPDATEが入ります。
ACCCESSのSQLビューで、確認するのもありですが、カンタンなSQLの書籍を購入するのがいいかもしれません。
Excelから、ACCESSのデータを更新 作業手順 その4 今回のコードで一番注意する点
主キーの型を間違えると、上記のようなエラーが出ます。
型が一致しませんというメッセージではありません。
また、オートメーションエラーなんかも出ます。
このようなエラーが、出てくると、嫌になるので、気を付けましょう。
ここがポイント
Excelから、ACCESSのデータ一括更新は、ネットで調べても、中々、HITしませんでした。
たぶん、需要がないのでしょう。
競馬でなければ、ここまでは、調べませんでした。
今回は、更新クエリでしたが、当然に、Excelから、追加クエリ、削除クエリ、選択クエリもできます。
また、恐らくは、テーブル作成クエリもできると思います。
でも、実務で利用するのは、更新クエリくらいでしょうか？
ACCESSに搭載されていない関数のおかげで、また、新たな知識を得ることができました。
まとめ
今回も競馬ネタになってしましたが、Excelから、更新クエリを作成しました。
しかし、ちょっと待って！
本当に解決したい問題は、AVEDEV関数がないから困ったということで、ここを解決しないと、根本の解決にはなりません。
それは、次回紹介します。
今回も最後まで読んでいただき、ありがとうございました。
今回のサンプルファイルは、No241です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
