PC Review


Reply
Thread Tools Rate Thread

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

 
 
FA
Guest
Posts: n/a
 
      1st Mar 2006
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

 
Reply With Quote
 
 
 
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      1st Mar 2006
Why don't you try to add this option for the .Execute statement as suggested
by Access?

db.Execute strFlagSQL, dbFailOnError or dbSeeChanges

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"FA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
FA
Guest
Posts: n/a
 
      1st Mar 2006
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

 
Reply With Quote
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      1st Mar 2006
On which line this error occurs?

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

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"FA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
FA
Guest
Posts: n/a
 
      1st Mar 2006
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

 
Reply With Quote
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      1st Mar 2006
I don't know anything about Outlook, you should start a new thread on a
newgroup devoted to it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"FA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has an IDENTITY column gjbankos@gmail.com Microsoft Access Form Coding 1 12th Jul 2012 11:37 AM
You must use the dbSeeChanges option with OpenRecordSet whenaccessing a SQL Server table that has an IDENTITY column iccsi Microsoft Access Form Coding 1 13th Oct 2011 10:06 PM
must use dbSeeChanges option with Recordset Dale Fye Microsoft Access Form Coding 5 18th Mar 2008 12:24 AM
dbSeeChanges specified in OpenRecordset yet Access still raises error 3622 Douglas J. Steele Microsoft Access 1 13th May 2004 08:39 PM
My audit routine routine fails. Run-time error '3622' "You must use the dbSeeChanges..." Adam Microsoft Access Form Coding 1 23rd Oct 2003 12:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:45 AM.