3622: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that

[FA]

Hi Freinds i have the following codes and its giving me the error:
3622: You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an identity column."

The code executes till .Send
and sends email etc etc... but after that there is a flag that i set
which updates the table dbo_SYS_INFO and thats where its gives me the
error. i dont know what i am doing wrong or is there anything else i
can do to avoid this error. please help me out. Thanks

Private Sub Command0_Click()
Dim strTo As String
Dim strSubject As String
Dim varMsg As Variant
Dim varAttachment As Variant
Dim strFlagSQL As String


'Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
'Dim objEml As Outlook.MailItem
Dim I As Integer

Set db = CurrentDb
Set rst = CurrentDb().OpenRecordset("qryContacts", dbOpenSnapshot,
dbSeeChanges)

Set objOutl = CreateObject("Outlook.application")
'Set objEml = objOutl.createItem(olMailitem)

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

For I = 1 To rst.RecordCount
If Len(rst!PRA_CTAC_NME) > 0 Then
strTo = rst!PRA_CTAC_NME
strSubject = rst!SYS_NME & " " & " " & "PRA Results"
varMsg = emailBody

Dim objEml As Outlook.MailItem
Set objEml = objOutl.CreateItem(olMailItem)

With objEml
.To = strTo

.Subject = strSubject

If Not IsNull(varMsg) Then
.Body = varMsg
End If

.Send
strFlagSQL = "UPDATE dbo_SYS_INFO SET
dbo_SYS_INFO.TEST_STAT_ID = 6 WHERE dbo_SYS_INFO.SYS_ID_CODE =" _
& rst.Fields("SYS_ID_CODE").Value & ";"
db.Execute strFlagSQL, dbFailOnError
Set rst = CurrentDb().OpenRecordset(strFlagSQL,
dbOpenSnapshot, dbSeeChanges)
End With
End If
Set objEml = Nothing
rst.MoveNext
Next I

ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing

Exit Sub

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
'rst.Close

End Sub

 

[Sylvain Lafontaine]

Why don't you try to add this option for the .Execute statement as suggested
by Access?

db.Execute strFlagSQL, dbFailOnError or dbSeeChanges

 

[FA]

Thanks Sylvain, your suggestion worked but with some error. My code is
sending email to only one contact person where it suppose to send email
to every contact person in the query, but after sending one email and
updating one record in the table, it gives me error, " 3219: invalid
operation".

what do you think can be a problem?

Please help me out.

Thanks

 

[Sylvain Lafontaine]

On which line this error occurs?

If this problem related to Outlook, Access or SQL-Server?

 

[FA]

i have figured it out Sylvain, the problem was the excessive line Set
rst = CurrentDb().OpenRecordset(strFlagSQL,dbOpenSnapshot,
dbSeeChanges) . I comment this line out and now its working just as i
wanted.
Related to the same code i have one more question, Is there any way i
can create a log for the contacts that did not recieve the email.
Actually sometimes the email does not go through and there is no way of
knowing which email address was cruppted.
So for that purpose i want to create a log which store all the email
address in my case "rst!PRA_CTAC" that were bad or the email did not go
through to them.
Outlook does tell you that the email address is incorrect but i will be
automating this module so that it will run by itself in a timely
manner.

If you know any codes for that please let me know.

Thanks

 

[Sylvain Lafontaine]

I don't know anything about Outlook, you should start a new thread on a
newgroup devoted to it.