How to disconnect recordset obtained from local table in MS Access?

Question

I'm looking for a way to create an in-memory datasheet form. And I like the idea of using a disconnected recordset. But I ran into the problem of the app crashing after reassigning disconnected recordset to the form. On stackoverflow, I was prompted by a post by Mr. HK1, where he wrote the following:

If you obtain the recordset from a table (even if it's an empty table) and then disconnect you can work around this problem.

But how to do it? In the case of DAO.Recordset I get the error: Operation is not supported for this type of object. I tried all types of recordset but it didn't help:

Sub testDynaset()
    Dim rs As dao.Recordset
    Set rs = CurrentDb.OpenRecordset("MyLocalTable", dbOpenDynaset)
    rs.Connection.Close ' << error: Operation is not supported for this type of object
End Sub

Sub testForwardOnly()
    Dim rs As dao.Recordset
    Set rs = CurrentDb.OpenRecordset("MyLocalTable", dbOpenForwardOnly)
    rs.Connection.Close ' << error: Operation is not supported for this type of object
End Sub

Sub testOpenSnapshot()
    Dim rs As dao.Recordset
    Set rs = CurrentDb.OpenRecordset("MyLocalTable", dbOpenSnapshot)
    rs.Connection.Close ' << error: Operation is not supported for this type of object
End Sub

Sub testOpenTable()
    Dim rs As dao.Recordset
    Set rs = CurrentDb.OpenRecordset("MyLocalTable", dbOpenTable)
    rs.Connection.Close ' << error: Operation is not supported for this type of object
End Sub

In the case of ADODB.Recordset, I don't know how to get it from a native local or linked MS ACCESS table. Please, help!

As noted, Access does not really support disconnected DAO recordsets, but you can use ADO ones. In most cases, I don't recommend doing this. In most cases, you can just shove a SQL string into the forms reocrdsource. While disconnected reocrdsets are of value, they are not the best choice when working with Access. — Albert D. Kallal
– Albert D. Kallal, Commented Jan 26, 2024 at 18:04

Erik A · Accepted Answer · 2024-01-26 12:05:36Z

You can use CurrentProject.Connection which is an ADO connection to the current database. It supports both local and linked tables.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM MyLocalTable", CurrentProject.Connection, adOpenStatic, adLockReadOnly
Set rs.ActiveConnection = Nothing

Depending on what you want to do exactly, you might need to tune the recordset and lock type. I'm not entirely sure why you want to use an in-memory recordset, a normal static recordset already contains a copy of the records in memory and won't refetch them.

The shared sentiment between most Access developers regarding binding forms to them is "don't", they generally have poor support for filters/sorts/refresh/requery, and even if you disable them, Access may crash (also see your linked post, the author remarks he switched to a temporary table).

Collectives™ on Stack Overflow

How to disconnect recordset obtained from local table in MS Access?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related