【Access】SqlServerに直接接続して更新しても接続スピードは変わらない話

完全に自分の備忘録です。

結論は、これと言った解決策が見つかりませんでした。

段々と更新が遅延

データを更新するVBAは、下記のようになります。

Private Sub regist_only_btn_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

    If MsgBox("登録しますか？ yes/no", vbYesNo, "データ登録") = vbYes Then
    
            On Error GoTo ErrRtn
            
                       'アクションクエリ非表示設定
                        DoCmd.SetWarnings False

                Set cn = CurrentProject.Connection
                rs.CursorLocation = adUseClient
                rs.Open "orderdata", cn, adOpenKeyset, adLockOptimistic
                rs.Filter = "NO = " & edit_value
                   

                    ' トランザクションの開始
                    cn.BeginTrans

                    rs!date_no = Me!date_no_input
                    rs!approved = Me!approved_input
                    rs!bp_no = Me!bp_no_input
                    rs!inquiry_no = Me!inquiry_no_input
                    rs!office_remarks = Me!office_remarks_input
                    
                    rs.Update

                    ' トランザクションの保存
                    cn.CommitTrans

                rs.Close: Set rs = Nothing
                cn.Close: Set cn = Nothing
                
                MsgBox ("登録しました。")
            
            Else
            
                MsgBox ("登録しないで戻りました。")
                Exit Sub
    
    End If

ExitErrRtn:
                    Exit Sub

ErrRtn:
    MsgBox "エラー： " & Err.Description
    cn.RollbackTrans
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

SqlServerからリンクテーブルしている「orderdata」をレコードセットで開いて、「NO」でフィルターしてから更新するVBAですが、レコード数が増えると、異常に接続が遅くなり、２万レコードを超えるころには、ODBC接続エラーになってしまいました。

ストアドで更新するのがベストなのだが・・・

https://pctips.jp/server/sqlserver-stored-parameters2020/

Sqlserverを使うなら、ストアドを使用するのがベストだと思うのですが、自分の実力では、どうも挙動がおかしい。更に、デバッグの方法がよくわかってないので手に負えない。

そこで、直接SqlServerに接続して、テーブルをレコードセットにしたら何とかなるんじゃないかと実験して見る事にしました。

SqlServerのテーブルをレコードセット

下記のようにVBAを変えました。

Private Sub regist_only_btn_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

    If MsgBox("登録しますか？ yes/no", vbYesNo, "データ登録") = vbYes Then

      'データベースに接続します。
      Set cn = New ADODB.Connection
      ConString = "Driver={SQL Server};SERVER=192.168.0.×××\SQLEXPRESS;UID=User;PWD=Password;DATABASE=DB_NANE"
      cn.Open ConString

            On Error GoTo ErrRtn
            
                       'アクションクエリ非表示設定
                        DoCmd.SetWarnings False
                        
       
'                                strSQL = "SELECT * FROM orderdata WHERE NO = " & edit_value ※これダメ

                                strSQL = "SELECT * FROM [dbo].[order] WHERE [dbo].[order].[NO] = " & edit_value

                rs.CursorLocation = adUseClient
                rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
                
                      ' トランザクションの開始
                    cn.BeginTrans
        
                
        If rs.EOF Then
    
                        MsgBox "データありません"
    
        Else
                        rs!date_no = Me!date_no_input
                        rs!approved = Me!approved_input
                        rs!bp_no = Me!bp_no_input
                        rs!inquiry_no = Me!inquiry_no_input
                        rs!office_remarks = Me!office_remarks_input
    
                        rs.Update
                    
       End If
                    ' トランザクションの保存
                    cn.CommitTrans

                rs.Close: Set rs = Nothing
                cn.Close: Set cn = Nothing
                
    Else
            
                MsgBox ("登録しないで戻りました。")
                Exit Sub
    
    End If

ExitErrRtn:
                    Exit Sub

ErrRtn:
    MsgBox "エラー： " & Err.description
    cn.RollbackTrans
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

「cn」でSqlServerに接続して、「rs.open」にて、strSQLをレコードセットで開きました。

丸１日いろいろやって記述がダメだったことがわかりました。

この[dbo].[テーブル名]のカッコが重要でした。

オブジェクトがどうの？とか、演算子が見つからない？とか訳の分からないエラーが出たり、出なくても期待した更新ができない状態が続きました。結局は、このカッコが重要だったようです。

知らないよー

結局は原因不明だけど・・・

VBAを色々と修正して実験を繰り返しやって見ましたが、更新遅延は改善しませんでした。

Sqlserverをクラウドに置きVPNで接続しているので、どこかでボトルネックを起こしているという事も考えらるのですが、普通にテーブルデータを見るだけなら接続遅延を感じません。

では、SqlServerが無料ライセンスのExpressだから？なのかも怪しい部分でもあります。

有償ライセンスを導入してまでやるような内容じゃないし・・・モヤモヤします。

※Developer版で後ほど試して見たいと思います。

でも、３０００件じゃ、データベースの役割を果たしているとは言えません。

最後までモヤモヤするSqlServerとACCESSとの組み合わせです。