パススルークエリをテーブル作成するには？

質問

以下のコードについてお教えいただければと思います。

フォーム上のテキストボックスに入力された6ケタの年月に基づき、
パススルークエリでデータ抽出を行うつもりです。
この後に、新たにテーブル作成をしようと思っています。

しかし、以下のコードでは
(1)　書き込むTestテーブルを準備し、保存しておく必要があるかと思います。
（現在は、別に作ったクエリオブジェクトの形式を流用しています）
(2)　繰り返し数が多く、相当な時間を使います。
(3)　Loopの代わりにと、SQLでINTO～を挿入したらエラーになってしまいます。

Testテーブルを都度削除し、一回で全件を追加する方法はないでしょうか。

Private Sub PassThru()
Dim qd As QueryDef
Dim rsSub As DAO.Recordset, rsMain As DAO.Recordset
Dim str月初 As String, str月末 As String
Dim i As Long
Const strConnect As String = "ODBC;****"

Set dbs = CurrentDb
Set qd = dbs.CreateQueryDef("")
qd.Connect = strConnect
qd.ReturnsRecords = True

str月初 = Format(DateSerial(Left(Me.txt年月, 4), Right(Me.txt年月, 2), 1), "yyyymmdd"))
str月末 = Format(DateSerial(Left(Me.txt年月, 4), Right(Me.txt年月, 2) + 1, 0), "yyyymmdd")

qd.SQL = "SELECT * FROM MAINDATA WHERE((DATE>=" & str月初 & ") And (DATE<=" & str月末 & "))"
Set rsSub = qd.OpenRecordset()

　　'****テーブルへの書き込み。フィールド数(20)×レコード数(約1500)回まわる
DoCmd.RunSQL "delete * from Test"
　　Set rsMain = dbs.OpenRecordset(Name:="Test", Type:=dbOpenTable)
Do Until rsSub.EOF
For i = 0 To rsSub.Fields.Count - 1
rsMain.AddNew
rsMain.Fields(i)=rsSub.Fields(i)
　　 Next
Loop

End Sub

回答No.1

IDii24 · ベストアンサー

これはですね。パススルークエリーではなくふつうのクエリーですよね。
この方式ですとデーターセットが使えないと一回でまとめて更新は無理です。つまりAccessではなくC#でSQLサーバーとAccsessを両方使うとかなら出来ます。
データーセットに読み込めばソースの形式は何でも構わないので。メモリ上に展開してメモリでリレーションを掛け更新もできます。

もしどうしてもAccessを使いたい場合は本当にパススルークエリをプロフラムでその都度作るしかありません。そのパススルークエリーを元にさらにふつうのテーブル作成クエリを作成しDoCmdなどで実行するしかありません。

まずパススルークエリを作成する前に、同じクエリは削除します。

　　Dim CAT As ADOX.Catalog
On Error GoTo errc
Set CAT = New ADOX.Catalog
Set CMD = New ADODB.Command

Deleteobj = True
CAT.ActiveConnection = CurrentProject.Connection

If CAT.Procedures.Count > 0 Then
For Each i In CAT.Procedures
If i.Name = クエリの名前 Then
CAT.Procedures.Delete (i.Name)

End If
Next
Exit Function
End If

そのあとに新しいSQLでパススルークエリのオブジェクトを作成します。

　　 qd.SQL = "SELECT * FROM MAINDATA WHERE((DATE>=" & str月初 & ") And (DATE<=" & str月末 & "))"

Set CMD.ActiveConnection = CAT.ActiveConnection

CMD.CommandText = qd.SQL
CMD.ActiveConnection.Properties("Jet OLEDB:ODBC Command Time Out") = 0
CMD.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
CMD.Properties _
("Jet OLEDB:Pass Through Query Connect String") = _
"ODBC;DRIVER=SQL Server;SERVER=" & server ";DATABASE=" & database

CAT.Procedures.Append クエリの名前, CMD
CAT.Procedures.Refresh

これでAccessにクエリオブジェクトが作成されています。
テーブル作成クエリは予め作成しておいても構いません。名前は同じなので。

この後にそのテーブル作成クエリを実行します。

お礼

ご回答いただき、ありがとうございました。
私、C#やSQLサーバーはおろかADOの使い方もろくに分からず、DAOばかりで
ご示唆いただいた内容をヒントとして頂戴するのみとなりました。大変申し訳ありません。

上記の質問で、ふつうのクエリをパススルークエリと呼んでいたのは、
クエリオブジェクトの作成とほぼ似た作業をしてくれるためでした。

結果、パススルークエリ、作成クエリをオブジェクトとして予め保存しておいて
プロシージャ内で以下の作業を行うことで、一応の決着としました。
(1)DoCmd.DeleteObject でテーブルを削除
(2)実際に作ったパススルークエリのSQL、Connectを変更
(3)実際に作ったテーブル作成クエリをDoCmdで実行

そもそも引っ掛かりの原因は、クエリオブジェクトを残さずコードだけで始末するのが
最上だと思い込んでいたところにあるような気がします。

不勉強な範囲については、また出直してきたいと思います。
このたびはありがとうございました。また機会あれば、お教えいただけると幸いです。