AccessでExcelデータをインポート

質問

お世話になります。

　Access2003/2010
　Excel2003/2010

10年程前より、Access2003で作成したツール(Excel2003形式のファイルを
インポート)を利用しております。

Excelファイルは毎日追加/更新しているのですが、先日、とうとうExcel2003
の行の上限である65,536行を超えてしまいました。
今後はExcel2010形式のファイル(行の上限は1,048,546行)とし、かつ、
2010形式のファイルをインポートできるようツールの修正をしなければならなく
なりました。

ツールの修正というよりは、今までAccess2003上で使用していたので、そのまま
Access2010上で使用したいだけなので、修正が必要がどうかも分からない
状況です。

そこで2点質問ですが、

　1.現状インポートする際は、
　　DoCmd.TransferSpreadsheet acImport, 8, "temp", strFile, True
　　としておりますが、「8」の箇所は2010形式のExcelをインポートする場合
　　どのように修正すればよいでしょうか。
　　ヘルプを見ても8(Excel2000形式)までの記載しかありません。

　　とりあえず8のままExce2010形式で、かつ2003の行の上限を超えている
　　ファイルを(66000行)インポートしてみましたが、行は削れることなく
　　インポートできました。

　2.インポート時の文字数制限について

　　インポートするExcelファイルの「備考」フィールドは文字数が多いのですが、
　　Access2003上で使用すると全ての文字列(750文字程度)をインポート
　　できておりました。
　　しかしAccess2010上で使用すると、文字列が途中で切れる(255文字)
　　不具合が発生しました

　　インポートする際は、あらかじめテーブルを作成しておき、そこに対して
　　Excelのデータをインポートしております。なお、「備考」のデータ型は
　　メモ型です。

　　(255文字で切れることから)恐らくAccessがインポートする時点で「備考は
　　テキスト型」と判断しているから255で切れてしまうのではないか、で、切れた
　　文字列をメモ型に入れてるだけのように見受けられるのですが。。
　　2003から仕様が変わってしまったのでしょうか。

　　上記のことから、インポートするExcelの先頭行の「備考」に、適当な文字列
　　(750文字)を入力し、それをインポートすると途切れることなくインポート
　　できました。
　　※Accessがメモ型と認識したから？

※いずれもツールをAccess2010上で使用した状況です。

ご教示の程、宜しくお願い致します。

回答No.1

piroin654

8→10　　　　だと。

以前も似たような質問がありましたが、
http://okwave.jp/qa/q8413669.html
上記は、
varStr = Split(buf, ",", , vbTextCompare)
のところを、
varStr = Split(buf, ",", , vbBinaryCompare)
と、引数を変更する必要がありましたが。

たとえば、以下のように、メモ型のデータがある場合、配列に
入れ込んでAccessでテーブルに入れるという方法
ぐらいだと思いますが。

例えば、Excelで

番号　　名前　　　　住所　　　　　質問
1　　　　徳川　　　　東京　　　　秋刀魚は、・・・・・
2　　　　織田　　　　名古屋　　　外郎の・・・・・・・
3　　　　豊臣　　　　大阪　　　　タコヤキの・・・・・

のような、データがあり、Accessに同じ構造の
テーブルがあるとして、

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim App As Object
Dim obj As Object
Dim sheet As Object
Dim buf As Variant
Dim varData As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("T質問", dbOpenDynaset)
Set App = CreateObject("Excel.Application")
App.Visible = True
App.Workbooks.Open (CurrentProject.Path & "\mydata1.xls")
'obj.Visible = True

i = App.Worksheets("Sheet1").Range("A1").End(xlDown).Row
For j = 2 To i
　　For k = 1 To 4
　　　　If k = 1 Then
　　　　　　buf = buf & App.Sheets("sheet1").Cells(j, k)
　　　　Else
　　　　　　buf = buf & "," & App.Sheets("sheet1").Cells(j, k)
　　　　End If
　　Next k
　　varData = Split(buf, ",", , vbBinaryCompare)

　　rs.AddNew
　　　　For m = 0 To UBound(varData)
　　　　　　rs.Fields(m) = varData(m)
　　　　Next m
　　rs.Update
　　buf = ""
Next j

App.Quit
Set App = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing

のような、感じですかね。

あるいは、大量のデータを処理するならば、
一旦、そのまま
DoCmd.TransferSpreadsheet acImport
で、入れてその後、メモ型のフィールド
のみを配列に入れて上記の一部を以下のようにして、
メモ型のフィールドを書き変えるかでしょうか。

i = App.Worksheets("Sheet1").Range("A1").End(xlDown).Row
For j = 2 To i
　　If j = 2 Then
　　　　buf = buf & App.Sheets("sheet1").Cells(j, 4)
　　Else
　　　　buf = buf & "," & App.Sheets("sheet1").Cells(j, 4)
　　End If
Next j
　　varData = Split(buf, ",", , vbBinaryCompare)
　　m = 0
　　rs.MoveFirst
　　Do Until rs.EOF
　　rs.Edit
　　rs.Fields(3) = varData(m)
　　　　　　'あるいは、
　　　　　　'rs!質問 = varData(m)
　　rs.Update
　　m = m + 1
rs.MoveNext
　　Loop

この場合は一度にデータを配列に入れますから、
メモリの余裕がなければフリーズ、ということに
なるかもしれません。
余裕がなければ、ExcelとAccessを行ったり来たり
といことになります。

上記の二つを一つのプロシージャの中に、どちらかの変数(i、j、m　など)
を違うものして一度にやってしまうか、あるいは、別個にして実行
するかのどちらかだろうと思いますが。

お礼

いつもご回答ありがとうございます。

>8→10　　　　だと。

これってどこかに情報載ってますでしょうか。色々探してはみたのですが。。
とりあえず、10に変更して問題なくインポート（エクスポートも）できることを
確認しました。

元々インポートしているExcelファイルに限界があり、運用自体も変えたい
ところではあるのですが、なかなか手を付けられずにいます。
Excelファイルは質問にも書きましたように、既に65000行を超えており
かつ、100列ほどあります。

なので教えて頂いた上記処理に耐えられるかどうか。。

なお、レジストリの修正については先日行ってみたのですが、
状況かわらずでした。
TypeGuessRowsを「0」にして、ImportMixedTypesを
「MajorityType」に変えてみたのですが、何か間違ってますでしょうか。
なお、「MajorityType」にするとTransferSpreadsheetの箇所で
エラーになったのでTextに戻しました。