dbOpenDynaset DAO

Feia temps que no veia unes explicacions tan clarificadores:

Sobre dbOpenDynaset

http://allenbrowne.com/ser-29.html

Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Last updated April 2010.

---

Contents:

1. DAO versus ADO

2. Recordset types

3. Move with no records

4. RecordCount without MoveLast

5. MoveNext without testing EOF

6. Loop without MoveNext

7. Find without NoMatch

8. AddNew without LastModified

9. Failing to close recordsets

10. Nested recordsets

VBA Traps: Working with Recordsets

This article highlights ten common traps with DAO recordsets in VBA code.

Most of the traps yield no syntax error; they are bugs lying dormant in your code until particular conditions are met. Your program then fails, or returns inaccurate results.

1. DAO versus ADO

The DAO and ADO libraries both have a Recordset object, but with different methods, properties, and options.

DAO is the native Access library (what Access itself uses), whereas ADO is a more generic library (now superseded by the vastly different ADO.NET library.)

Different versions of Access default to different libraries. See Solving Problems with Library References for details.

This article assumes DAO recordsets.

Solution:

To ensure your code works reliably:

1. Set your references to use just the library you want.
2. If you must use both, list your main one first.
3. Disambiguate by specifying which library's recordset you want. Use:
       Dim rs As DAO.Recordset
   not:
       Dim rs As Recordset

2. Recordset types

There are different types of DAO recordset, with different methods.

When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.)

The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.

Solution:

Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:

    Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Sobre dbSeeChanges

https://www.microsoftaccessexpert.com/Microsoft-Access-Code-dbSeeChanges.aspx

While working with a Microsoft Access database using linked Microsoft SQL Server tables, you may receive the following error message:




You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column


Solution: Set rst = CurrentDb.OpenRecordset("SELECT * From tblName", dbOpenDynaset, dbSeeChanges)