must use dbSeeChanges option with Recordset

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

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
 
hi Dale,

Dale said:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError + dbSeeChanges)
Click to expand...
Hm, have you tried:

.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)


mfG
--> stefan <--
 
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 =.
 
Thanks, guys.

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

Dale
 
Back
Top