データベースとの接続 Loop内で毎回接続と解除を繰り返し行うべきかどうか

Question

エクセルVBAで、アクセスファイルとデータをやりとりするシステムを作成して使用しています。
このシステムで、使用開始1年ほどでエラーが出るようになり、何となく解決したのですがこれでよかったのか、エラーの理由はなんだったと考えられるのか、本来行うべきだった解決法は他にあるのか、等を質問させてください。

以下のように、アクセスファイルとの接続部分だけ別プロシージャとして作成しています。

Public gCON As ADODB.Connection
'アクセスファイルとの接続
Public Function DbOpen() As Long
On Error GoTo DbOpen_e
Dim sConStr As String
    sConStr = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source = C:\データ.accdb"
    If gCON Is Nothing Then
        Set gCON = New ADODB.Connection
        gCON.Open sConStr
    End If    
    Exit Function
DbOpen_e:
    DbOpen = ERR.Number
    MsgBox "[" & ERR.Number & "]" & ERR.Description
    Call DbDispose
End Function
'接続解除
Public Sub DbClose()
    If Not gCON Is Nothing Then
        On Error Resume Next
        gCON.Close
    End If
    Call DbDispose
End Sub
Public Sub DbDispose()
    If Not gCON Is Nothing Then
        Set gCON = Nothing
    End If
End Sub

実際にデータファイルと接続してデータを取得したり編集する場面は以下のようなものです。

Public Sub DataEdit()
On Error GoTo ERR
Dim rsC As New ADODB.Recordset, strQ As String
Dim ws As Worksheet,RowCnt As Long, tgtId As Long

    Set ws = ThisWorkbook.Worksheets("リスト")    
    'Accessデータベースに接続
    call DbOpen
    
    RowCnt = 2
    Do
        tgtId = ws.Cells(RowCnt, 1)

        'RSCと接続
        strQ = "SELECT * FROM tblBasic WHERE 顧客ID = " & tgtId
        'テーブルを取得
        rsC.Open strQ, gCON, adOpenKeyset, adLockOptimistic
        
        If Not ((rsC.EOF) And (rsC.BOF)) Then
            ws.Cells(RowCnt, 3) = rsC.Fields("名前")
        End If
        Set rsC = Nothing
        RowCnt = RowCnt + 1
        If RowCnt > 1000 Then Exit Do
    Loop
    
    If Not (rsC Is Nothing) Then
        Set rsC = Nothing
    End If
    
    Call DbClose    
Exit Sub
ERR:
    If Not (rsC Is Nothing) Then
        Set rsC = Nothing
    End If    
    Call DbClose
    Application.Cursor = xlDefault
    MsgBox ERR.Description
End Sub

このようなコードなのですが、使用開始後1年くらいの頃、急にエラーがでるようになりました。
決まって206行目でテーブルの取得（rsC.Open）ができなくなるんです。
SQLの内容（tgtIDの番号）が何番であろうと変わらずループ206番目でだめになります。
そこで、いろいろ試すうちに、何の根拠もなくただの勘で、データセットを取得するたびにその前にdbOpenを実行し、データセットをNothingにするたびにその直後にDbCloseを実行するようにしたところ、問題が発生しなくなりました。

教えていただきたいのですが、そもそも、これは解決法として妥当だったのでしょうか？
こういう場合、ループ内で毎回データファイルとの接続と解除を行うべきなのでしょうか？
接続はそのままでデータセットの取得と放棄だけを繰り返すのは何か問題なのでしょうか？

追記
iruyas様、enii様から、adLockOptimisticの使い方に問題があるのではないか、というご指摘がありました。
たしかに私は、この、レコードセットの取得方法について何も考えずにコピペしたコードですませていたのですが、今回のエラーの原因がこの取得タイプの間違いによるものであるのかどうか、まだ理解しきれていません。

私が作ったコードのながれは、

• (1)データベースファイルと接続する
• (2)接続したデータベースから、SQLを発行してレコードセットを取得する（adLockOptimistic指定）
• (3)取得したレコードセットからフィールドの値を取り出してセルに書き出す
• (4)レコードセットを手放す（set RS = nothing)
• (5)データベースファイルとの接続を切断する

という流れのなかで、(2)から(4)をループさせていました。
これがうまくいかなくなって(1)から(5)までをループさせたらうまくいったという状態です。

「一度に持てるデータの量は決まっている。無駄にデータを持たせないように、ReadOnlyでいいのかLockしかないのか考えるべき」というご指摘でしたが、
私としては、レコードセットを1ループごとに取得してはすぐ解放してるのだから、1度に持っているデータは1レコードセット分だけなのに、と考えてしまいます。
取得したレコードセットからひとつフィールドの値をもらったらすぐにそのレコードセットを手放してるつもりなのですが、そうなってはない＝つまり(4)でレコードセットを手放しているつもりでも、実は何かが手元に残っているということでしょうか？元のデータベースとの接続じたいを一旦切ってしまうか、あるいはReadOnlyで取得したレコードセットならちゃんと手から離れてくれる、ということなのでしょうか？

Comments

[退会済みユーザー]

adLockOptimistic
これの意味とこれを変える検討はしましたか？

[kujiraSE]

まったく考えたことがありませんでした。
その部分については、別のシステムで前任者が書いたコードを意味も考えずコピペしたものです。
そこに何か原因があるのでしょうか？

[退会済みユーザー]

ご自身では調べないのですか？

[enii]

SQLに詳しくなさそうなので補足すると、
SELECT（参照）はReadOnly、UPDATE（更新）はLockで発行する
というのが一般的な使い方です。

１つの手で掴めるものは１つなので、人間は最大２つまでしか物を掴めません。
プログラムも同じで、掴んでいられる量はPCごとに違っていても、必ず最大値があります。
で、ループでLockしながらOpenし続けているということは、お使いのPCでは最大値が206回目辺りなんでしょう。
iruyasさんがおっしゃっているのはそういう概念を踏まえた話です。

[kujiraSE]

相手をしてくださりありがとうございます。

＞SELECT（参照）はReadOnly、UPDATE（更新）はLockで発行する

↑この部分ですが、レコードを参照するだけでいい時はもらいっぱなしでいいんだからReadOnlyで、
取得したレコードを編集して保存しなおすなら、他の接続者と衝突しないためにLockで、というように理解しました。

ただ、そのご指摘が私の失敗例にどれほど関係するのか、それがまだしっくりきません。
私が作ったコードのながれは、
(1)データベースファイルと接続する
(2)接続したデータベースから、SQLを発行してレコードセットを取得する（adLockOptimistic指定）
(3)取得したレコードセットからフィールドの値を取り出してセルに書き出す
(4)レコードセットを手放す（set RS = nothing)
(5)データベースファイルとの接続を切断する
という流れのなかで、(2)から(4)をループさせていました。これがうまくいかなくなって(1)から(5)までをループさせたらうまくいったという状態です。

この対処の適否に関して「一度に持てる量が決まっているからどういうタイプで取得するのかどうかに気を使うべき」というのがいまいちピンときません。
1ループごとに取得してはすぐ解放してるのに、どういう取得の仕方であるかがなぜ重要なのか、ということです。
取得したレコードセットからは、ひとつフィールドの値をもらったらすぐに手放してるつもりなのですが、そうなってはない＝つまり(4)でレコードセットを手放しているつもりでも、実は何かが手元に残っているということでしょうか？元のデータベースとの接続じたいを一旦切ってしまうか、あるいはReadOnlyで取得したレコードセットならちゃんと手から離れてくれる、ということなのでしょうか？

[enii]

うまい説明ができないですが、DBアクセスの仕組みをどこかのお店で食事することと同じように考えてみてください。

着席がOpen、退席がCloseです。席数は決まっているので退席がなければ新たに座れなくなるのは当然です。
SQL発行などは「料理を注文する」などの行為に当たり、座席に対しては何の影響もありません。
その中でも「set RS = Nothing」というのはDB側ではなくExcel側の仕組みなので、当然ながらこれをやっても埋まった席が空くことはありません。
では最後に開放されるのはどうして？　といえば、単純にExcel処理が終われば「もう閉店ですよ」となって全員追い出されるだけのことです。

ReadOnlyとLockは席ではなく店員に対する話で、Lockはつまり「各店員は最初に注文を取った人相手にしか動かない」と固定されている状態です。すべての店員が誰かに固定されて動ける店員がいなくなれば、たとえ席に座っていても料理は出てきません。
店員が解放されるのは客が退店した時なので、結局はCloseしなければどうしようもなくなります。

[kujiraSE]

ありがとうございました！！
やっと理解できました。
＞「set RS = Nothing」というのはDB側ではなくExcel側の仕組み
この一句、目から鱗でした。
ほんとうにありがとうございました。

Answer 1

追記･修正依頼欄でのenii様のご回答で納得できました。
回答欄ではなかったため、自己解決という形になりますが、すべてenii様のおかげです。
自身で試みた「レコードセットの取得のたびにいちいちデータとの接続切断をする」という方法もあながち邪道ではなかったようです。レコードセットの取得時のロック方法についてももっと意識的であるべきでした。