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

Discussion in 'Microsoft Access Forms' started by FA, Feb 28, 2006.

  1. FA

    FA Guest

    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
     
    FA, Feb 28, 2006
    #1
    1. Advertisements

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

    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" <> wrote in message
    news:...
    > 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, Feb 28, 2006
    #2
    1. Advertisements

  3. FA

    FA Guest

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

    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
     
    FA, Mar 1, 2006
    #3
  4. Re: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an identity column."

    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" <> wrote in message
    news:...
    > 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, Mar 1, 2006
    #4
  5. FA

    FA Guest

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

    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
     
    FA, Mar 1, 2006
    #5
  6. Re: You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an identity column."

    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" <> wrote in message
    news:...
    >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, Mar 1, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. [MVP] S. Clark

    Re: dbSeeChanges in SQL statement?

    [MVP] S. Clark, Sep 5, 2003, in forum: Microsoft Access Forms
    Replies:
    0
    Views:
    217
    [MVP] S. Clark
    Sep 5, 2003
  2. Jefferson Berlin

    Linked SQL tables and dbSeeChanges?

    Jefferson Berlin, Dec 4, 2003, in forum: Microsoft Access Forms
    Replies:
    2
    Views:
    2,894
    Jefferson Berlin
    Dec 4, 2003
  3. Guest
    Replies:
    11
    Views:
    1,517
    Allen Browne
    Nov 20, 2004
  4. Synergy

    DBSeeCHanges

    Synergy, Feb 7, 2006, in forum: Microsoft Access Forms
    Replies:
    2
    Views:
    411
    Synergy
    Feb 7, 2006
  5. ersouthard

    Using OpenRecordset with a linked table

    ersouthard, Jan 28, 2009, in forum: Microsoft Access Forms
    Replies:
    4
    Views:
    516
Loading...

Share This Page