Accessでのインポート時アップデート

質問

Access 2003を所持しています。ExcelデータをAccessのテーブルにインポートしたいと思います。

インポートウィザードでインポートをすることはできました。

そしてプライマリーキーを設定します。

その後、同じExcelをインポートする時に、既にインポートされているものは、UPDATE、プライマリーキーがExcelにないものはInsertする。

といったような事が実現できないかなと思っています。できないようであれあば、Access 2010など最新のものではそのような事が可能になっているでしょうか？

もしできないようであれば、それが実現できるソフトウェアが欲しいと思っています。

つまり、欲しいソフトウェアはDBMS。Excelをインポートできる。条件によってinsert,updateができる。

といったものです。よろしくお願い致します。

回答No.4

piroin654 · ベストアンサー

#2です。
あるいはまったく視点を変えて、Excelから直接Accessの
データを更新、追加することではどうでしょうか。これならば
いちいちAccessを開いて更新、追加をすることはありません。
一応、クエリは
http://oshiete1.watch.impress.co.jp/qa6393593.html
で作成したクエリとします。

AccessからExcelのシートをリンクテーブルとして
リンクしている場合とします。

Accessの標準モジュールに、以下を
設定します。

Sub cmdUp()
DoCmd.OpenQuery "更新クエリ"
End Sub

Sub cmdInsert()
DoCmd.OpenQuery "追加クエリ"
End Sub

つづいて、Excelの標準モジュールに以下を
設定します。

Sub cmdTest()
Dim appAccess As Access.Application
Dim strPath As String

Set appAccess = CreateObject("Access.Application")
strPath = "D:\sample.mdb"

' appAccess.AutomationSecurity = msoAutomationSecurityLow
appAccess.OpenCurrentDatabase (strPath)
appAccess.Run ("cmdUp")
appAccess.Run ("cmdInsert")
' appAccess.AutomationSecurity = msoAutomationSecurityUI
appAccess.Quit
Set appAccess = Nothing

End Sub

Access2003の環境が無いので、AutomationSecurityプロパティの設定
が必要ならば、コメントアウトした部分のコメントアウトを解除して
ください。
参照設定でMicrosoft Access xx ObjectLibraryのチェックを
確認します。

このマクロをシート上でにボタンを設置しマクロを
呼び出すようにするか、あるいはマクロの実行から
マクロを指定して実行します。

お礼

おぉ！！コードまで書いていただいて頭がさがります。ほんとに勉強になります、ありがとうございます！！

回答No.3

DexMachina

mshr1962さんも言われているように、Accessのインポートウィザードでは
「Insert」のみで、「Update」には対応していないかと思います。
(手持ちの中では最新になるAccess2007では、Access2003とほぼ同様：
　インポート作業そのものに名前をつけて保存するオプションの追加はあり)

あいにく、Access以外のデータベースについてはわかりませんが(汗)、
先日の以下のご質問の際にご紹介したように、Excelファイルへのリンクを
作成した上で、
http://okwave.jp/qa/q6392978.html
以下のような手順を踏めば、Accessでも実質的にご希望に適った処理が
可能かと思います。

1)「インポート先となるAccessテーブル」(Aとします)と、「インポート元となる
　Excelへのリンクテーブル」(Bとします)から、『Aテーブルから、両者の間で
　プライマリーキーが一致するレコードをDeleteする【削除クエリ】』を実行
2)『Bテーブルの全レコードをAテーブルに追加する【追加クエリ】』を実行
　(または、インポートウィザードを実行してもOK)

つまり、Updateの対象となる既存のレコードを予め削除してしまうことで、
Excel側の全レコードをInsert(またはImport)できるようにしてしまう、という
ことです。

Deleteは件数が多くても実行速度は速いのと、「Update＆Insert」では
それぞれ対象レコードの限定が掛かることなどから、「Delete＆Insert
(又はImport)」の方が迅速に処理されるものと思います。

お礼

こういったやりかたがあるんですね！！勉強になります！ありがとうございます！！

回答No.2

piroin654

http://oshiete1.watch.impress.co.jp/qa6393593.html
以前私が回答した続きならば、以下のようにします。

Private Sub ボタン_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery ("更新クエリ")
DoCmd.OpenQuery ("追加クエリ")
DoCmd.SetWarnings True
End Sub

他のAccess関連のQ&Aサイトでも似たような
返答になると思います。VBAで一つ一つの
レコードを比較しながらやっていてはレコード数が
大量になれば当然ながら膨大な時間がかかるのは
当たり前です。なお、Excelのシートをリンクして
テーブルとして接続するというのはおわかり
だと思いますが。

回答後、補足なしに締め切られたのでその後の
経過がまったくわかりません。もし、データ数が
多く、更新追加に時間がかかるということであれば
SQL Serverを導入し、データ管理をそちらで行っては
どうでしょうか。

しかし、どちらにせよSQL文を同様にExcelからSQL Server
に実行することには変わりはありません。
もう一つは、Web上にいくつかの有償ツールがありますが、
性能のほどはわかりません。中でやっていることは
似たようなものだと思いますが。

お礼

なるほど、勉強になります、ありがとうございました！！

回答No.1

mshr1962

出来ないはずです。現行で出来るのは、「プライマリーキーがExcelにないものはInsertする。」だけですね。

お望みの事をしたいのなら、インポート用のテーブルと、実行用のテーブルを用意
インポート用のテーブルから実行用のテーブルへ更新クエリと追加クエリで可能になります。
上記の更新クエリ、追加クエリ後にインポート用のテーブルのデータを削除クエリでクリア
という手順をマクロに登録すればいいと思いますが．．．

お礼

なるほど、そういうやりかたがあるのですね。ありがとうございます！