【Access】SQLをVBA内で動かすとエラーになる場合とならない場合の相違

マイクロソフトデータベースアクセス（以下Access）は本当に意味が分からない事が多いです。

今回も、簡単な更新するSQLをVBA内で動かそうとしたらエラーがでるのです。

簡単な更新SQLでハマる

クエリをSQLに変換して、VBA内に直接貼り付けてもエラーが起きることは普通です。

今回のエラーも、カンマやダブルコーテーションなどの違いからエラーが出ているという思い込みから時間が経過していきました。

フォームの値を、変数に変更して見たり、Dlookup関数を直接SQLに埋め込むような事はやめて変数にしてみたりと色々と工夫をしましたが、エラーから抜け出せません。

以下がエラーがでるコードです。

Sub input_login()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim SQL As String
Dim schno As String
Dim logu As String

On Error GoTo ErrRtn

        schno = DLookup("auth_no", "dbo_login_user", "name='" & Me.user & "'")
        logu = Forms![login]![user]
        
        
        Set cn = CurrentProject.Connection
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cn

        cn.BeginTrans


                SQL = "UPDATE login SET user = '" & logu & "',auth_no = '" & schno & "' WHERE ID = 1"
                cmd.CommandText = SQL
                cmd.Execute

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


ExitErrRtn:
                Exit Sub

ErrRtn:
                MsgBox "ログインし直してください。エラー： " & Err.description
                cn.RollbackTrans
                Set cmd = Nothing
                cn.Close: Set cn = Nothing
                End

End Sub

上のSQLの部分でエラーが出ていると思い込んで様々な書き方を試してみましたがダメでした。

DoCmd.RunSQLにしたら問題なし

本当に簡単な更新SQLなのに３日間ほど悶々と時間が過ぎていきました。

そして、今日　DoCmd.RunSQL　に行きつきました。

問題なく動いたコードが下記になります。

Sub input_login()
Dim cn As New ADODB.Connection
Dim SQL As String
Dim schno As String
Dim logu As String

On Error GoTo ErrRtn

        schno = DLookup("auth_no", "dbo_login_user", "name='" & Me.user & "'")
        logu = Forms![login]![user]
        
        
        Set cn = CurrentProject.Connection

        cn.BeginTrans

                DoCmd.RunSQL "UPDATE login SET user = '" & logu & "',auth_no = '" & schno & "' WHERE ID = 1"

        cn.CommitTrans
        
                cn.Close: Set cn = Nothing
        
ExitErrRtn:
                Exit Sub

ErrRtn:
                MsgBox "ログインし直してください。エラー： " & Err.description
                cn.RollbackTrans
                cn.Close: Set cn = Nothing
                End
End Sub

上のように、DoCmd.RunSQLを使ったら問題なくすんなりと動きました。

いつものやり方でダメならDoCmd.RunSQL

SQLをVBA内で動かす為には、定番のやり方しか頭にありませんでした。

https://pctips.jp/pc-soft/access-query-sql-vba20200401/

今回のこの件で非常に無駄な時間を使ったので、定番のやり方でダメな場合は、DoCmd.RunSQLを使ってVBA内でのSQLは動かした方がよいという事がわかりました。

残念な事に、今の自分では、この定番とDoCmd.RunSQLを使ってのやり方は何が違うのかが理解して無いってことです。

いずれは、SqlServerだけで全ての処理ができるようになるかと思うので、この問題も深く突き詰めることは止めておきます。