4

In Access VBA, when opening a recordset as a snapshot, does it make the read-only option redundant? At first this seemed to be true, since a snapshot is essentially read-only already, but there always seems to be caveats.

Example:

Dim acc      as Access.Application
Dim db       as DAO.Database
Dim rs       as DAO.Recordset
Dim sqltext  as String

sqltext = "SELECT * FROM SOMESOURCE"

Set rs = db.OpenRecordset(sqltext, dbOpenSnapshot, dbReadOnly)

'Because the type is dbOpenSnapshot, does dbReadOnly become redundant?
spinjector
  • 2,534
  • 1
  • 19
  • 39

1 Answers1

5

Check the recordset's Updatable property. This one prints False.

sqltext = "SELECT * FROM TABLE_01"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqltext, dbOpenSnapshot)
Debug.Print rs.Updatable

So, yes, dbOpenSnapshot as the recordset Type option gives you a read-only recordset.

HansUp
  • 92,185
  • 11
  • 67
  • 122
  • 3
    The `.Updateable` property is influenced by a huge number of things, such as the content of the query, the presence of a primary key, and if the table is linked. However, dbOpenSnapshot is never updateable according to [MSDN](https://msdn.microsoft.com/en-us/library/office/ff197799.aspx): _Snapshot-type Recordset— a static copy of a set of records that you can use to find data or generate reports. A snapshot-type Recordset object can contain fields from one or more tables in a database but can't be updated. This type corresponds to an ODBC static cursor._ – Erik A Aug 08 '18 at 21:16