Access adpのVBAでTransferTextを使いCSVファイルをインポートするとエラーになる場合がある。

タイトルのように、Access adpファイルでVBAに

DoCmd.TransferText acImportDelim　

を使いCSVファイルをインポートしようとするとエラーになる場合がある。

実は、Access2003くらいからずっとこの問題に悩まされていて、インポート中に以下のようなメッセージが出てエラーになることがある。

「バリアント型でない変数にNull値を代入しようとしました」

読み込むCSVのファイルは、毎週１回更新用に提供されるもので、週によって上記のエラーが出たり出なかったりする。

よくわからないのは、エラーとなっても、手動でインポートをやるとエラーにならず上手くインポートできてしまうということ。

読み込むテーブルの各列の　NULLを許容　の属性にチェックを入れてもエラーになる時はなる。

何行目のデータでエラーが起きているのかの情報もないのでどのデータが悪いのかもよくわからない。

そんなわけで、エラーが出た時は、手動でインポートして対応していました。

ここで、インポート定義を使えばいいのではと思われる方もいるとは思いますが、apd では仕様上インポート定義が使えないようです。

それで、ここからは私の推測なんですが、apd　の環境で、DoCmd.TransferText acImportDelim　実行する場合は、コマンド実行に先立って、インポートするCSVのファイルをダミーで読んで各列のデータ型を決めているのでは・・・・。それで実際に読んだ時にダミーの読んだ時の型と不一致なデータが出現した場合にエラーとなるのではないか・・・・。

という推測です。間違っていたらご指摘ください。

では、どう解決すればいいのかと、いろいろ調べてみると、やはり、地道にVBAの標準のI/Oを使って読み込むのが確実なようです。

以下に、サンプルコードを抜粋しておきますので、参考にしてください。

Dim txtData As String, FNo As Long, arrData, i As Integer

　　Dim StrFilNm As String ' パス付きファイル名の格納用変数
　　Dim conn As New ADODB.Connection
Dim Rec As New ADODB.Recordset

　　'カレントデータベースに接続
Set conn = Application.CurrentProject.Connection

'　読み込み用テーブルをクリア
conn.Execute "DELETE [tbl読込先]"

' 読込み先　Open
Rec.Open "tbl読込先", conn, adOpenDynamic, adLockOptimistic

　　' CSVファイルの場所を設定
　　StrFilNm = "C:\sample\test.csv" '場所とファイル名を設定

FNo = FreeFile
Open StrFilNm For Input As #FNo 'CSVファイルオープン

On Error GoTo エラー処理

'　トランザクション開始
conn.BeginTrans

      ' 指定されたCSVファイルからtxtDataに1行読込、
      ' Split関数でカンマ毎に切り離して、配列arrDataにセット
      ' テーブルの各項目にセットする（この例は項目数15）

　
      Do While Not EOF(FNo)
         Line Input #FNo, txtData
         arrData = Split(txtData, ",")
            Rec.AddNew
                  Rec("F1") = arrData(0)
                  Rec("F2") = arrData(1)
                  Rec("F3") = arrData(2)
                  Rec("F4") = arrData(3)
                  Rec("F5") = arrData(4)
                  Rec("F6") = arrData(5)
                  Rec("F7") = arrData(6)
                  Rec("F8") = arrData(7)
                  Rec("F9") = arrData(8)
                  Rec("F10") = arrData(9)
                  Rec("F11") = arrData(10)
                  Rec("F12") = arrData(11)
                  Rec("F13") = arrData(12)
                  Rec("F14") = arrData(13)
                  Rec("F15") = arrData(14)
                  Rec.Update
      Loop

conn.CommitTrans　'トランザクション　コミット

Close #FNo　'　CSVファイルクローズ

Rec.Close ' 読み込み先テーブルクローズ

DoCmd.TransferText acImportDelim　コマンドを　行数は多くなりましたが、
上記のようなコードに変更した結果、いまのところ、

「バリアント型でない変数にNull値を代入しようとしました」

が出ることはなくなりました。

KinsakuGblog1

このブログを検索