ACCESSのクエリ、SQLに変数を使うのに？？

Question

いつもお世話になっております。
ACCESS初心者です。行き詰ったため質問いたします。
どうか、ご教授お願いします。

アクセスのInsertクエリ実行時に変数を持たせたいです。
VBAコードにて、配列で格納している社員IDを１件ずつループして
SyainCDにいれます。そこでSyainCDに値が入るたび、
SyainCDをWhere条件として、InsertSQLを実行したいです。
■CODE■
SyainCD = SyainId(i)
※コード上ではSyainCDにきちんと値が
入ってきていることは確認済です。
また、SagyoYMD()というユーザ関数を用いて
作業日を取得する関数は正常に取得できます。

関数ではなく、コードの中の変数をSQLに組み込むことは
不可能なのでしょうか？？
実行するとエラーではなく、ダイアログでSyainCD を入力要求
が表示されてしまいます。。。。

■実行クエリ
INSERT INTO wrk_マスタテーブル
SELECT *
FROM
 [SELECT
 qry_ユニオンリンクテーブル.社員CD,
 qry_ユニオンリンクテーブル.作業日,
 Q_部署テーブル.部署名
FROM
 (Q_部署テーブル
 INNER JOIN ユニオンリンクテーブル
 ON Q_部署テーブル.社員CD = qry_ユニオンリンクテーブル.社員CD)
 ]. AS マスタテーブル
WHERE
マスタテーブル.社員CD = SyainCD AND
マスタテーブル.作業日 = SagyoYMD();


よろしくお願いします。

venzou · Accepted Answer

＞なるほど、変数を取得する関数をつくってしまえばよいのですね。

はい、そういう事ですね。


＞というようにして実装しました。

２）の後半部分がよく分かりませんでした。（＾＾；
しかし、問題が解決したのであれば、それで良いと思います。

答えは１つではありませんので、私の回答は単なる１例と捉えて下さい。
問題を解決するための参考になれば、それで良いのです。

実際の実装は、ご自由に行って頂ければ良いと思います。

noname#140971 · Answer

[イミディエイト]
? CnnExecute("INSERT INTO tab1 (ID, Fld_1) VALUES (4, 'DDD');")
True

と、CnnExecute 関数は、ＳＱＬ文を実行する関数です。

Public Sub ErrMessage(ByVal CnnErrors As ADODB.Error, ByVal strSQL As String)
　　 MsgBox "ADOエラーが発生しましたので処理をキャンセルします。" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & CnnErrors.Description & Chr$(13) & _
　　　　　　"・Err.Number=" & CnnErrors.Number & Chr$(13) & _
　　　　　　"・SQL State=" & CnnErrors.SQLState & Chr$(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " ADO関数エラーメッセージ"
End Sub

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　Dim isOK As Boolean
　　Dim cnn　As ADODB.Connection
　　 
　　isOK = True
　　Set cnn = CurrentProject.Connection
　　With cnn
　　　　.Errors.Clear
　　　　.BeginTrans
　　　　.Execute strSQL
　　　　.CommitTrans
　　End With
Exit_CnnExecute:
On Error Resume Next
　　cnn.Close
　　Set cnn = Nothing
　　CnnExecute = isOK
　　Exit Function
Err_CnnExecute:
　　isOK = False
　　If cnn.Errors.Count > 0 Then
　　　　ErrMessage cnn.Errors(0), strSQL
　　　　cnn.RollbackTrans
　　Else
　　　　MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　 vbExclamation, " 関数エラーメッセージ"
　　End If
　　Resume Exit_CnnExecute
End Function

＞実際は、INSERTする項目が多い為・・・

GetSQL関数の類を用意すれば、既存のクエリのＳＱＬ文を取得できます。
既存のクエリの一部を[%xxxxx%] としておけば簡単に置換して CNNExecute関数で実行できます。

[イミディエイト]
? GetSQL("tab1 クエリ")
SELECT tab1.ID, tab1.fld_1
FROM tab1;

Public Function GetSQL(ByVal QueryName As String) As String
　　Dim MyQuery As DAO.QueryDef
　　
　　Set MyQuery = CurrentDb.QueryDefs(QueryName)
　　GetSQL = MyQuery.SQL
End Function

やり方は、それぞれの好み、趣味の問題で・・・。

venzou · Answer

＞関数ではなく、コードの中の変数をSQLに組み込むことは
＞不可能なのでしょうか？？

確かに、関数は認識されますが、変数は認識されません。

SyainCDをパブリック変数として宣言し、
SyainCDを返す関数を作れば、解決するのでは？

Public SyainCD As Variant

Public Function getSyainCD() As Variant
　getSyainCD = SyainCD
End Function


＞マスタテーブル.社員CD = SyainCD AND

マスタテーブル.社員CD = getSyainCD() AND

-----------------------------------------------------------
クエリは使わず、VBA内で、SQL文を作り、実行する方法もあります。
その場合、変数は文字列として、SQL文内に埋め込みます。

Dim strSQL As String
strSQL = "INSERT INTO wrk_マスタテーブル" & _
"SELECT *" & _

　中略

"WHERE" & _
"マスタテーブル.社員CD = " & SyainCD & " AND" & _
"マスタテーブル.作業日 = SagyoYMD();"

CurrentDb.Execute strSQL

noname#140971 · Answer

tab1:

ID__fld_1
1___AAA
2___BBB
3___CCC

次は、tab1に上記の３つのレコードをインサートするサンプルコードです。

Private Sub コマンド0_Click()
　　Dim I　　　　　 As Integer
　　Dim strFld_1(3) As String
　　
　　strFld_1(1) = "AAA"
　　strFld_1(2) = "BBB"
　　strFld_1(3) = "CCC"
　　For I = 1 To 3
　　　　CnnExecute "INSERT INTO tab1 (ID, Fld_1) VALUES (" & I & ",'" & strFld_1(I) & "');"
　　Next I
End Sub

一番簡単なのは、ＣｎｎExecute関数の類を用いて INSERT文を実行することです。
もちろん、クエリのSQL文の変数部にＶＢＡで値を設定することも可能。
しかし、そうまでしてクエリを用いるのかどうかは疑問。
そこで、後者のやり方は割愛します。

なお、CNNExecute関数方式でＯＫであれば、関数も紹介します。

ACCESSのクエリ、SQLに変数を使うのに？？

＞なるほど、変数を取得する関数をつくってしまえばよいのですね。

[イミディエイト]

＞関数ではなく、コードの中の変数をSQLに組み込むことは

この回答への補足

tab1:

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング