Excel から Access を操作する形での DoCmd.TransferSpreadsheet でエラーが出ない

Question

Excel VBA で参照設定をした上で Access.Application を作成し、
DoCmd.TransferSpreadsheet を実行して、xlsx を accdb にインポートする処理を書いています。

この際、基本的に正常動作したのですが、
テーブルでプライマリキー重複するデータをテストしたところ、エラー発生せずに処理を通過してしまいました。

Access VBA 上で同等の処理を行った場合「すべてのデータを追加できませんでした」と言うダイアログが表示されますが、
同じようにダイアログ表示したり、あるいは、エラー発生させたりする方法はありますでしょうか。

Comments

[tosi]

質問内容の確認です。以下の認識で宜しいでしょうか。
Access側で作成したVBA(Function)を、Excel側からAccess.Application runで起動を掛けている。
このAccess側のFunctionにDoCmd.TransferSpreadsheet部分があり、エラー発生しているが、現在この内容をExcel側で取得できない。
そのため、Access.Application run実行完了後にその結果を、Excel側で戻り値として取得したい。

[退会済みユーザー]

Application run ではなく excel 側の vba 内で Acceaa.application のインスタンスを作成する形で、そこから DoCmd.TransferSpreadsheet を実行していますしています。

[tosi]

下記の様な処理をされているのではないかと思われます。
おっしゃる通りエラーは取得できませんでした。
Application runであれば戻り値あるでしょうが、上手いアイディア出ましたらフォローさせて頂きます。
Sub test()
Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase "C:\Users\Y***\Data.mdb"
Err.Clear
appAccess.DoCmd.TransferSpreadsheet acImport, , "Table", "C:\Users\***\test.xlsx", True
Debug.Print Err.Number
End Sub

[radames1000]

予め最低限でもコードを記載いただくと認識の齟齬や不要な確認も無くなり、回答がもらいやすくなると思いますよ。

[退会済みユーザー]

ありがとうございます。ソースについてはほぼ仰る通りの内容です。

※個別具体的な話としては、インポート元ファイルの行数とインポート先テーブルのビフォーアフターの行数を検証してエラーにするなどの回避をしているのですが、そもそも不可解な挙動であることと、その他のDoCmdのメソッド（使用箇所複数…）等でも同じような事象が発生するのでは無いかとの懸念もあって、継続してこの事象に関する情報を求めています。

なおこれは余談ですがAccess vba を組み込んで Application.run する方式は業務上の都合（溜息）により不可なのです…

[tosi]

DoCmd.TransferSpreadsheetを諦めて、読み込みExcelを起動させてから、
セルを順次読み込みADO(又はDAO)にてAccessへ書き込みする方法もあります。Accessを純粋なデータべベースとして扱う方向です。
(私はExcelからAccessへのデータ書き込みはこの方法を常に取っています。)

Answer 1

Application.DisplayAlerts プロパティでメッセージを出力しないようになっていませんか？

Access.Application を作成し

という事なら、SetWarningsも確認して下さい。

Comments

[退会済みユーザー]

していません。
また Access側のダイアログの話ですので、ExcelのDisplayAlertsは無関係です。

[退会済みユーザー]

SetWarnings も true です。

[sazi]

ところで、仮に「すべてのデータを追加できませんでした」が出力されたとしてそれでいいのでしょうか？
通常は、無ければ追加あれば更新という処理を行うと思いますので、そのような対応にしたければ、TransferSpreadsheet でワークのテーブルに一旦格納して、それを元に処理する方が何かと都合がいい事が多いですね。

[退会済みユーザー]

> 出力されたとしてそれでいいのでしょうか？

はい。
インポートデータ内でのキー重複やデータの不備（データ長やnull等々）等検知する必要がある為です。

おもいつくままに色々事前にチェックすることも可能ですが、
いま問題にしているのは、Accessで発生したエラーを検知する方法です。

[sazi]

実行時に、Accessは起動していますか？
DocmndはAccessでのコマンドなので、Accessが起動されていないとダイアログは出力されないと思われます。
コードも質問に追記して下さい。

[退会済みユーザー]

当然起動しています。実行自体は正常に行われていてエラーの場合のみ振る舞いが異なります。

Answer 2

理由は不明だがまあ仕様なのだろうと言う結論

Answer 3

Excelから、Accessを実行したとき、警告のメッセージや情報のメッセージが表示されないのは、仕様ですね。
私も、わざとエラーが出るように作っても、何事もなかった如く、終了してしまいました。

VBA で、on error ステートメントを使用して、エラーを回避した時、McroErrorプロパティにエラーの内容が格納されるのですが、それも、格納されませんでした。
（わざと存在しないファイル名を指定したりと、必ずエラーとなるケース等）

Access側で、件数をカウントして、登録された件数と元データの件数を調べるとか、一旦、なんでも格納されるテーブルにインポートしてから、登録して、不一致を検出するとかで、事前にチェックするしかないですね。

なんとなくですが、Access側でデータをチェックして、メッセージボックスを表示させるか、ログファイルを作る手法が良さそうです。
（メッセージボックスは、表示できました。）

Comments

[退会済みユーザー]

ありがとうございます。
回避策についてはまあこれ単体ではどうにでもなるので良いんですが、割とシンプルな問題にも関わらず、関連情報が無いのが気になると言うか残念ですね。

振る舞い的に DoCmd のその他のメソッドでも発生しそうな気がするんですがそのへんの情報もないですし…

[kai_keitai]

何通りか実行しましたが、Accessで実行すると必ずエラーになるケースも、ExcelからAccessを実行すると、何事もなかったように終了してしまいました。
Runメソッドも、Docmdメソッドも、OpenFormメソッドも、ダメでした。
仕様と言うのが正論でしょうね。
多分、ExcelからAccessを操作するニーズが少ないのも原因だと思います。
Microsoft のサポートに直接連絡するのも手段かもしれませんね。