must use dbSeeChanges option with Recordset

[Dale Fye]

I've got an application that I'm migrating from an Access 2k3 be to SQL
Server. I've changed most of my action and select queries so that they
include the dbSeeChanges option, but the following one is, for some reason,
continuing to generate a runtime error #3622, with the description: You must
use the dbSeeChanges option with OpenRecordset when accessing a SQL Server
table that has an identity column.

When I copy the SQL string, and paste it in a query, it runs just fine.
The offending line of code (below) is the one where I try to define the
recordset.

Dim rs As DAO.Recordset
Dim strSQL As String, rsData As DAO.Recordset

On Error GoTo BuildSlideError

'Open the slides recordset. If no records display a message and exit
strSQL = "SELECT * FROM tbl_Nodes " _
& "INNER JOIN tbl_local_Nodes " _
& "ON tbl_Nodes.ID = tbl_local_Nodes.Node_ID " _
& "WHERE tbl_local_Nodes.IsSelected = True " _
& " ORDER BY tbl_Nodes.[Node_Num2]"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError + dbSeeChanges)


Dale

 

[Stefan Hoffmann]

hi Dale,

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError + dbSeeChanges)

Hm, have you tried:

.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)


mfG
--> stefan <--

 

[Klatuu]

It doesn't have to be dbOpenSnapshot. Any valid recordset type is acceptable.

 

[Dale Fye]

That worked.

Thanks for the recommendation, Steve. So much for optional parameters

 

[Douglas J. Steele]

In many cases, while a parameter may be labelled as Optional, Access stops
looking once it finds a missing parameter, so subsequent parameters are
ignored.

You can get around it as Stefan indicated, or you can use a named parameter:

Set rs = CurrentDb.OpenRecordset(strSQL, Options:= dbFailOnError +
dbSeeChanges)

Note that I now have only 1 comma, and I'm using :=, not =.

 

[Dale Fye]

Thanks, guys.

This foray into SQL Server is teaching me all sorts of stuff about Access
that I never knew.

Dale