INSERT INTOを使ってデータの登録をすると、処理時間がかかりすぎるので、.AddNewを利用する場合のトラブルです。
以下のようにすれば、問題なく登録できます。
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
Dim List1 As Variant
Dim List2 As Variant
List1 = Array("fact0", "fact1", "fact2")
List2 = Array("a", "い", "う")
adoRs.AddNew List1, List2
adoRs.Close
しかし、実際にはデータ数が多くなるので、
配列に格納する場合に、変数を使用する必要があります。
"a", "い", "う"は、submtという配列に入っているので
Dim List1(2) As Variant
Dim List2(2) As Variant
For i = 0 To 2
List1(i) = "fact" & i & List1(i)
List2(i) = submt(i)
Next
で、List1にフィールド名、List2にデータを格納してから
adoRs.AddNew List1, List2
を実行すると、
実行時エラー 2147217887「要求された名前、または序数に対応する項目がコレクションに見つかりません」
というエラーが発生します。
力技で、テキストを作って変数に代入してみましたが、ダメでした。
For i = 1 To 2
tex = tex & """" & "fact" & i & """" & ", "
tex2 = tex2 & """" & submt(i) & """" & ", "
Next
tex = """" & ", " & Left(tex, Len(tex) - 2)
tex2 = """" & ", " & Left(tex2, Len(tex2) - 2)
List1 = Array(tex)
List2 = Array(tex2)
どのように、処理したらよいのでしょうか？
後だしで、どんどん情報がかわっていくいつものパターンでしょうか？
ここはAccessに関する質疑応答、技術的な情報交換の場です。
それを踏まえて、他の閲覧者や後から検索で訪問した人にとって、意味のある情報になるように、Accessでどのように解決するかという観点で、質問していますか？
具体的にどのようなコードを書いたのか不明ですが、
追加するデータはどこにどのような形で存在するのでしょうか。
Accessのテーブル、CSVデータ、エクセルシート、あるいは他データベースのテーブルなどなら、INSERT INTOの方がAddNewより高速です。
そうでないなら、どこにどのようなデータがあるのかの説明が必要です。
adoRs.AddNew List1, List2 でそのエラーが発生するということですか。
エラーメッセージから推測するに、List1 に格納されているフィールド名が、追加するテーブルに存在しないという状況だと思います。
2000件くらいのデータを一括で登録する場合、
.AddNewを利用すると、INSERT INTOを利用した時と比べて、1/5くらいの時間で処理できてる感じです。
ですが、2000件分のコードを書くのは、間違っているような気がしまして
フィールド名をList1に、登録するデータをList2に格納するのですが、
List1 = Array("fact1","fact2","fact3")
と2000件書き続けるわけにはいかないので、
変数を利用する必要が出てきます。
しかし、
For i = 1 to 2000
List1(i) = "fact" & i & List1(i)
next
で処理すると、エラーが出てしまうわけです。
List1(0)には、"jan"の文字列が入るので、1からスタートしています。
List1 に格納されているフィールド名が、追加するテーブルに存在しないという状況のようなのですが、
factに連番を振っているだけなので、
List1(i) = "fact" & i & List1(i)
の部分に、なにか致命的な間違いがあるような気がしてますが、原因が分かりません。
ちなみに、主キーのjanを登録してから、updateで残りのデータで更新をかけると
全てsqlで処理しても、コードの簡潔さを考慮すれば、OKレベルではあるのですが、、、、
なんか、負けたようで嫌なのです。
ですが、updateの方が不可が少ないような感じなので、これが正解なのかもしれないのですが、
INSERT INTOの、100件程度の処理速度を測ってみましたが、
１秒かからないので、コードが簡単に作れることを考えると、
200件程度だったら、これでいいかな～という感じでした。
コードは、
For i = 1 To 200
tex = tex & "fact" & i & ","
Next
Tn = "算定基礎届"
Fn = "(" & "jan" & "," & Left(tex, Len(tex) - 1) & ")"
で作ってエラーが出ないので、
List1のフィールド名にも、間違いはなかったはずです。
やはり、根本的な間違いをしているような気がします。
₋ 具体的なテストコードと実行時間の測定方法および測定結果が不明。
主に足りていないのは、次の 3 つについての理解でしょう。
VBA における配列の扱い方
ADODB.Recordset オブジェクトの AddNew メソッドの使い方
Access データベースにおけるテーブル/クエリの仕様
仮に adoRs が参照しているのが Access データベース上のテーブルなのであれば、
1 つのテーブルに定義できるフィールドの数の上限は 255 個ですので、
2000 個ものフィールドを持つレコードを追加することなど絶対に不可能です。
ハンドルネームを入力し忘れました。失礼。
まず、根本的に無理だったのですね。
200程度でも、やはり処理速度には差が出るので、
色々と試してみたのですが、
引数が間違った型、許容範囲外、または競合しています。
というエラーメッセージが出るようになりました。
この原因を見つけ出すのは、かなり困難なので止めます。
脱.Executeは、今後の課題にします。
ありがとうfございました。
結局、いつもの右往左往して自己完結でしたね。そして、貴方の自己完結には脈絡がありません。
もしかして言葉のキャッチボールを知らないのかしら？
質問者さんは自己完結されてるようなので、
後から来た閲覧者のために、
大量のデータをテーブルに新規追加する方法の速度に関する、
一般論を置いておきます。
SQLの INSERT INTO ... VALUES ステートメントで1レコードずつ新規追加を繰り返す方法と、
DAOまたはADOの .AddNewメソッドで1レコードずつ新規追加を繰り返す方法では、
後者の方が高速。
理由
INSERT INTO ... VALUES ステートメントでは、1回実行するたびに、レコードセットの生成、破棄が実行される。例えば200レコード追加する場合は、それが200回繰り返される。
.AddNew の場合は、最初にOpenRecorset でレコードセットを生成、200回追加して(.AddNew)後に、最後にレコードセットを破棄(Close)することが可能。生成、破棄は１回のみですむ。
ただし、追加するデータが、Accessのテーブル、CSVデータ、エクセルシート、他データベースのテーブルなどなら、テーブルとして扱うことが可能なので、 INSERT INTO ... SELECT ステートメントを使えば、複数レコード追加でも、1回の実行で可能なので、高速だし、コードもシンプルになる。