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

質問

いつもお世話になっております。
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();

よろしくお願いします。

回答No.4

venzou · ベストアンサー

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

はい、そういう事ですね。

＞というようにして実装しました。

２）の後半部分がよく分かりませんでした。（＾＾；
しかし、問題が解決したのであれば、それで良いと思います。

答えは１つではありませんので、私の回答は単なる１例と捉えて下さい。
問題を解決するための参考になれば、それで良いのです。

実際の実装は、ご自由に行って頂ければ良いと思います。

お礼

>２）の後半部分がよく分かりませんでした。（＾＾；

すみません・・・見返してみて、
自身でもかなりわかりにくいと感じました・・・

>答えは１つではありませんので、私の回答は単なる１例と捉えて下さい。
>問題を解決するための参考になれば、それで良いのです。

とても参考になりました！
理解しながら先に進めていきます。
またなにかございましたらよろしくお願いします。

回答No.3

noname#140971

[イミディエイト]
? 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

やり方は、それぞれの好み、趣味の問題で・・・。

お礼

ありがとうございます。
今回は、クエリのSQLで実装してみます。
この先また、同様の箇所があると思いますので
その際に様子をみて、CnnExecute 関数を利用してみたいと
思います。

ご親切なご回答ありがとうございました。

回答No.2

venzou

＞関数ではなく、コードの中の変数を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

補足

上記の方法で解決しました！
以下、もし見解違いでしたらご指摘いただければ幸いです。

なるほど、変数を取得する関数をつくってしまえばよいのですね。
１）パブリック変数宣言、パブリック関数をつくる
２）SyainId(i)の処理()の中で、SyainId(i)をパブリック変数宣言にわたして、Call パブリック関数を行う
３）パブリック関数をSqlへわたす。
というようにして実装しました。

回答No.1

noname#140971

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関数方式でＯＫであれば、関数も紹介します。

補足

すみません・・・
CnnExecute関数、調べましたがよくわかりませんでした。
教えてください。

上記のようにコードの中に、SQLを組み込むことも考慮したのですが
実際は、INSERTする項目が多い為、
DoCmd.OpenQuery "実行クエリ"という形で取りたいと思っています。

なるべく一番簡単なものを希望しています。
お手数おかけしますが、今一度、ご教授ください。