dbSeeChanges

[spinkung]

Hi

I'm getting an error : A new error has occured. Run-time error 3622

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

I've tried adding dbSeeChanges (in red below), but i then get an Invalid Argument Error.

Can anyone tell me where i'm going wrong. Thanks.

Dim rs As ADODB.Recordset

Lot = """" & Me.Lot.Value & """"

SQL = "Select * from dbo_WipDevStatus where Lot = " & Lot & ""

Set rs = CurrentDb.OpenRecordset(SQL, [COLOR="Red"]dbSeeChanges[/COLOR])

 

[DJkarl]

Well dbSeeChanges is an Option

"Set recordset = object.OpenRecordset (source, type, options, lockedits)"

So try...

OpenRecordset(SQL,,dbSeeChanges)

 

[Never Hide]

Hey guys,sorry to post in a old thread, but I'm getting the same error so I thought I'd use this post instead of starting a new one.

I use the openrecordset the way DJkarl suggested

Set rsA = db.OpenRecordset(searchTable, , dbSeeChanges)

but I'm still getting the same error.

And here is the wierd part. It used to work without that "dbSeeChanges" ( to be honest now is the 1st time i encounder this)...the only thing I changed and I got this error is that I changed the "searchTable" to a different query that produces the same result with the old one ,it just has better performance.

P.S. i'm using DAO instead of ADODB-don't know if that makes any difference,I've never workded with ADODB

 

[Never Hide]

UPDATE: I just found out that if I use my old query as "searchTable" everything goes back to working as intended.....

The difference between the 2 queries is the new query uses the linked tables for souce, and the old query uses other queries as source (based on the same linked tables of course)

Any thougts?

 

[boblarson]

UPDATE: I just found out that if I use my old query as "searchTable" everything goes back to working as intended.....

The difference between the 2 queries is the new query uses the linked tables for souce, and the old query uses other queries as source (based on the same linked tables of course)

Any thougts?

Post the whole code. Can't tell anything from what you've posted.

 

[Never Hide]

Hey Bob, thanks for your reply.

On Error GoTo Error_Handler

'dhlwsh metavlhtwn
Dim i As Long
Dim db As dao.Database
Dim rs, rsb As dao.Recordset
Dim qdf As dao.QueryDef
Dim pType1 As String
Dim pType2 As String
Dim pType3 As String
Dim pType4 As String
Dim X As Integer
Dim searchTable As String

'orismos metavlhtwn
searchTable = "qInVisio_Diamenontes"
Set db = CurrentDb()
Set rs = db.OpenRecordset(searchTable, dbOpenSnapshot, dbSeeChanges)
Set rsb = db.OpenRecordset("tmpCleaning")

pType1 = "petsetes"
pType2 = "sentonia"
pType3 = "elkatharimsos"
pType4 = "plkatharismos"

'ekatharish tou prosorinou pinaka "tmpCleaning", ston opoio apothikeuontai oi plhrofories
'gia to an ta dwmatia pou einai kathleimmena xreiazontai katharisma
Set qdf = CurrentDb.QueryDefs("clrCleaning")
qdf.Execute

rs.MoveFirst
For i = 1 To rs.RecordCount
rsb.AddNew
rsb!FOLIOID = rs!FOLIOID
'elegxos gia allagh petsetwn
rsb!TOWELS = Cleaning(rs!CHKIDATE, rs!CHKODATE, Me.tbHotelDate.Value, Plano(searchTable, rsb!FOLIOID, pType1))
'elegxos gia allagh sentoniwn
rsb!SHEETS = Cleaning(rs!CHKIDATE, rs!CHKODATE, Me.tbHotelDate.Value, Plano(searchTable, rsb!FOLIOID, pType2))
'elegxos gia elafru katharismo
rsb!ELAFRYS_KATH = Cleaning(rs!CHKIDATE, rs!CHKODATE, Me.tbHotelDate.Value, Plano(searchTable, rsb!FOLIOID, pType3))
'elegxos gia plhrh katharismo
rsb!PLHRHS_KATH = Cleaning(rs!CHKIDATE, rs!CHKODATE, Me.tbHotelDate.Value, Plano(searchTable, rsb!FOLIOID, pType4))
rsb.Update
rs.MoveNext
Next i
rs.Close
rsb.Close

'an einai mono to checkbox gia ta kathleimmena dwmatia epilegmeno tote anoigei report mono gia ta
'kathleimmena dwmatia
If Me.chbOC <> 0 And Me.chbFree = 0 Then
Call DoCmd.OpenReport("rptRoomsAll", acViewPreview, , "[Kind] = 201")
Call DoCmd.Maximize
Call DoCmd.RunCommand(acCmdZoom100)
'an kai ta 2 checkboxes einai epilegmena tote anoigei to report gia ola ta dwmatia
ElseIf Me.chbOC <> 0 And Me.chbFree <> 0 Then
Call DoCmd.OpenReport("rptRoomsAll", acViewPreview)
Call DoCmd.Maximize
Call DoCmd.RunCommand(acCmdZoom100)
End If

Exit_Procedure:
'Exit Code here
Set rsb = Nothing
Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox "ÄçìéïõñãÞèçêå êÜðïéï óöÜëìá ìå ôçí åöáñìïãÞ. " & vbCrLf _
& "ÅðéêïéíùíÞóôå ìå ôçí INTERMEDIA A.E.T.E. " & vbCrLf _
& "ÇñÜêëåéï: 2810.261440, Ìïßñåò: 28920.24117" & vbCrLf _
& "email: support@intermedia.com.gr" & vbCrLf _
& "áíáöÝñïíôáò ôï ðáñáêÜôù ìÞíõìá ëÜèïõò:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & vbCrLf _
& Err.Description, _
Buttons:=vbCritical
Resume Exit_Procedure

ignore the comments, they are written in greeklish(greek with latin characters ) and also those unreadable characters in the Error_Handler are just greek letters that don't copy correct."Cleaning" and "Plano" are custom functions I created


The line in red is Highlighted by the debugger. I've tried with different or mo [Type] argument,and without the dbSeeChanges [Option] argument and I always get the same result. And if I use the old query it goes back to working as intended. Can't figure out what's going on

let me know if you want me to post those 2 queries as well,
thank you for your time

 

[jdraw]

Just a comment, and not specific to your underlying issue:

This Dim rs, rsb As dao.Recordset does not so what you think.
It does not Dim rs as a DAO.Recordset. rs will be Dimmed as a Variant.

You must explicitly DIM variables.
You can use
Dim rsb As dao.Recordset
Dim rs As dao.Recordset
OR
Dim rsb As dao.Recordset,rs as DAO.Recordset

See post #13 at
http://www.accessforums.net/access/can-any-one-tell-me-how-do-24457.html

 

[boblarson]

What happens if you don't use the dbOpenSnapshot part?

 

[DrallocD]

Change For i = 1 To rs.RecordCount / Endif to Do While not rs.EOF / Enddo. rs.Recordcount will most likely return 1 no matter how many rows there are.

 

[Never Hide]

Hey guys, thank you for your replies,
sorry it took me so long to reply but I've had some problems that needed to be solved and I was away for a while.
I've changed this

Set rs = db.OpenRecordset(searchTable, dbOpenSnapshot, dbSeeChanges)

to this

Set rs = db.OpenRecordset(searchTable, dbOpenDynaset, dbSeeChanges)

and now it works. I don't know how or why though since I've tried that before getting that same error

 

[boblarson]

Hey guys, thank you for your replies,
sorry it took me so long to reply but I've had some problems that needed to be solved and I was away for a while.
I've changed this

Set rs = db.OpenRecordset(searchTable, dbOpenSnapshot, dbSeeChanges)

to this

Set rs = db.OpenRecordset(searchTable, dbOpenDynaset, dbSeeChanges)

and now it works. I don't know how or why though since I've tried that before getting that same error

You probably had somethng slightly off when trying it before. But the reason the dbOpenDynaset works and dbOpenSnapshot doesn't is that dbOpenSnapshot, in my estimation (and I could be wrong) isn't for use with ODBC connected data sources. Because a snapshot is a moment in time thing, the data in a SQL Server database would not be able to be captured and, even if it could be - why would the dbSeeChanges be able to be used with it if it didn't matter if changes were able to be reflected if it was a snapshot.

If you want to have something that is quick to run through one time, use the dbForwardOnly selection instead.