【Access】VBA内のSQL記述でDlookUp関数でのエラー回避方法

https://pctips.jp/pc-soft/access-query-transaction2020/

その為に、複数クエリーを使っている処理をSQLコードに記述変更しているのですが、Dlookup関数の部分でどうしてもエラーがでてしまうので調べてみました。

AccessのクエリーからSQL変換

クエリーをデザインモードで開くと上の図のようになりますが、左上の部分から、SQLビューに変えると、SQLに変換することができます。

この

がでます。

なんで、Accessというソフトがそのような仕様なのかは理解できませんが、VBAで使用する為には加工が必要です。

VBAようにSQLコードを加工して使用

UPDATE dbo_accounts SET dbo_accounts.前月末残高 = [前月末残高]-([現金]+[小切手]+[振込]+[手数料]+[相殺]+[手形]+[電債])+Int([当月金額])+Int((Int([当月金額])*DLookUp("[TAX]","dbo_tax","[JNO]=1")/100))+[調整金], dbo_accounts.当月金額 = 0, dbo_accounts.調整金 = 0, dbo_accounts.入出金日 = Null, dbo_accounts.現金 = 0, dbo_accounts.小切手 = 0, dbo_accounts.振込 = 0, dbo_accounts.手数料 = 0, dbo_accounts.相殺 = 0, dbo_accounts.手形 = 0, dbo_accounts.電債 = 0;

上のコードが、AccessのクエリーをSQLビューに変換したそのままの状態ですが、カッコやカンマなどの違いからエラーになる事はフツーにあります。

なんだか、無駄にカッコが多いので削除してもエラーになります。

これが、そのままVBA内でも使えれば本当に便利なのですけどね。もしくは、VBA内で複数クエリーでトランザクション処理がまともに動くなら便利なんですけどね。

愚痴を言っていても進まないので黙々とエラー回避の作業を３時間ほど費やしました。

DlookUp関数でエラーが出ている！

デバッグを進めていくと、DlookUp関数でエラーが出ていることが分かりました。

いったい何がダメなの？

いろいろとダブルコーテーションやカンマや、シングルコーテーションを付けたり、外したりして無駄な時間を費やしてたどり着いたのが下記のようになりました。

これが、VBA内で動かすと、最終的に更新されたテーブルの値を計算して確認しないと検証ができません。

結局下記のようにしたらちゃんと動いたようです。

最終的なVBAコード

Sub getsuji()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim SQL As String

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn

On Error GoTo ErrorHandler

cn.BeginTrans

              
                SQL = "UPDATE dbo_accounts "                                      'go_月末処理１"
                SQL = SQL & "SET dbo_accounts.前月末残高 = [前月末残高]-([現金]+[小切手]+[振込]+[手数料]+[相殺]+[手形]+[電債])+Int([当月金額])+Int((Int([当月金額])*DLookUp('TAX','dbo_tax','JNO=1')/100))+[調整金], dbo_accounts.当月金額 = 0, dbo_accounts.調整金 = 0, dbo_accounts.入出金日 = Null, dbo_accounts.現金 = 0, dbo_accounts.小切手 = 0, dbo_accounts.振込 = 0, dbo_accounts.手数料 = 0, dbo_accounts.相殺 = 0, dbo_accounts.手形 = 0, dbo_accounts.電債 = 0"
                cmd.CommandText = SQL
                cmd.Execute



'        DoCmd.OpenQuery "go_月末処理２"
'        DoCmd.OpenQuery "go_月末処理30"
'        DoCmd.OpenQuery "go_月末処理31"
'        DoCmd.OpenQuery "go_月末処理32"
'        DoCmd.OpenQuery "go_月末処理33"
'        DoCmd.OpenQuery "go_月末処理４"
'        DoCmd.OpenQuery "go_月末処理５"
'                DoCmd.OpenQuery "go_月末処理xxxxxx"
        

cn.CommitTrans
    
    Set cmd = Nothing
    cn.Close: Set cn = Nothing



ExitErrorHandler:
    Exit Sub

ErrorHandler:
    cn.RollbackTrans
    Set cmd = Nothing
    cn.Close: Set cn = Nothing
    DoCmd.Close acForm, "job_wait"
    MsgBox "エラーが発生しました。処理せずに戻ります。"
    End

End Sub

コメントアウトしている部分のクエリーをSQLに変換している途中のコードになります。

Accessは色んな癖が強いので本当に苦労します。