- ✅ Introduction
- ■ Issue: Freezes at Recordset.Open Despite Correct SQL
- ■ Cause: Missing CursorLocation Setting
- ■ Solution: Explicitly Set CursorLocation = adUseClient
- ⚠ Pitfalls When Migrating from OO4O to ADO
- ■ Summary: One Line of CursorLocation Can Make or Break It
- Bonus: Safe Recordset Template
- ■ 関連記事
- ■ Related Articles
✅ Introduction
When migrating from OO4O (Oracle Objects for OLE) to ADO (ActiveX Data Objects) in Access VBA, you may encounter a strange issue:
“The SQL statement is exactly the same, but it freezes.”
Surprisingly, the cause is neither the SQL itself nor the connection, but a single missing line of configuration.
This article explains the root cause of the freeze and how to avoid it.
■ Issue: Freezes at Recordset.Open Despite Correct SQL
In the OO4O era, the following code ran without issues:
Set OraDyn = g_OraDatabase.CreateDynaset(mySQL, ORADYN_READONLY)
After rewriting it for ADO, it resulted in a complete freeze—no progress, no response, not crashing, but doing nothing:
Set rs = New ADODB.Recordset
rs.Open mySQL, g_con, adOpenStatic, adLockReadOnly■ Cause: Missing CursorLocation Setting
In ADO, specifying the cursor location when opening a Recordset is crucial.
By default, adUseServer (server-side cursor) is used, but this can be incompatible with certain providers, especially Oracle.
Because the cursor location was omitted, the process got stuck trying to fetch a server-side cursor.
■ Solution: Explicitly Set CursorLocation = adUseClient
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient ' ←Lack of this causes it to stop!
rs.Open mySQL, g_con, adOpenStatic, adLockReadOnly✔ Why is adUseClient necessary?
- Client-side cursors make EOF/BOF and other navigation more stable
- Server-side cursors may not function properly with some providers (like Oracle)
- Client-side cursors work in-memory, making them suitable for read-only SELECT queries
⚠ Pitfalls When Migrating from OO4O to ADO
| Item | OO4O（old） | ADO（new） |
|---|---|---|
| Cursor Setting | Implicit optimization | Must be explicitly defined |
| Connection Compatibility | Oracle-specific | Requires attention to provider compatibility |
| Error Messages | More descriptive | May silently freeze |
■ Summary: One Line of CursorLocation Can Make or Break It
If your code freezes after migrating to ADO—even when using the same SQL—check your cursor setting first.
■ Quick Checklist:
- Did you remember rs.CursorLocation = adUseClient?
- Did you verify the cursor type (client vs. server) during SQL execution?
- Are you using a provider (e.g., Oracle) that might behave differently with ADO?
Just one missing line can mean the difference between working and freezing—that’s both the challenge and depth of working with ADO.
Bonus: Safe Recordset Template
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open mySQL, g_con, adOpenStatic, adLockReadOnly
If Not rs.EOF Then
' Processed here
End If
rs.Close Set
rs = Nothing■ 関連記事
【Access VBA】OO4OからADOに移行後、SQLは同じなのにフリーズ？原因はカーソル設定
【Access VBA】「Object」と「ADODB.Connection」の違いとは？使い分けの解説
OO4O→ADOに移行する際のフォーム関数の呼び出し方の違いと注意点
【保存版】Oracle OO4O（Oracle Objects for OLE）とは？サポート終了とADOへの移行ガイド
■ Related Articles
- Access VBA ADO freeze
- Recordset.Open hangs
- CursorLocation adUseClient necessity
- ADO freezes without SQL changes
- ADO adUseServer vs adUseClient
- OO4O to ADO migration issues
