You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a

Discussion in 'Access Forms Coding' started by gjbankos@gmail.com, Jul 11, 2012.

  1. Guest

    Please accept my apologies for my ignorance in advance. I am learning and I appreciate any feedback on this issue that I may receive. I inherited an access database that I migrated to SQL 2008 and I am getting the above error in this code:
    Private Sub Restore_Click()
    Dim n As Integer
    Dim lngID As Long
    Dim frm As Form_frmFrontEnd

    On Error GoTo RestoreError:

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    With SelectedData(Me.lstInactive)
    ' alert the user when no records were selected
    If .Count = 0 Then
    MsgBox "Please select a record before " & _
    "attempting to restore it.", vbInformation
    GoTo ExitRestore:
    End If

    ' get confirmation from user to restore record(s)
    msg = MsgBox("Are you sure you want to restore these record(s)?", vbQuestion + vbYesNo)
    If msg = vbNo Then
    GoTo ExitRestore:
    End If

    ' initials progress meter
    SysCmd acSysCmdInitMeter, "Restoring Records Please Wait...", .Count

    For n = 1 To .Count
    ' retrieve the ID number for later use
    lngID = .Item(n)

    ' execute query to restore records
    With CurrentDb.QueryDefs(RESTORE_BIDS_QUERY)
    .Parameters![ID] = lngID
    .Execute

    ' alert user when a record could not be restore
    If .RecordsAffected = 0 Then
    MsgBox "Could not reactive Record #" & lngID, vbExclamation
    End If
    End With

    ' update progress meter
    SysCmd acSysCmdUpdateMeter, n
    Next n

    ' refresh the list of records in the listbox
    Me.lstInactive.Requery
    End With

    ' refresh the front end form
    If CurrentProject.AllForms(FRONT_END_FORM).IsLoaded = True Then
    Set frm = Form_frmFrontEnd
    frm.Requery
    End If
    ExitRestore:
    Set frm = Nothing
    SysCmd acSysCmdClearStatus
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

    RestoreError:
    MsgBox Err.Description, vbCritical
    Resume ExitRestore:
    End Sub



    I've seen other threads that discuss this error, but the code example isn't the same as what I have and I'm not sure how to incorporate the dbSeeChanges command within this code.

    Any assistance would be greatly appreciated and thank you in advance.
     
    , Jul 11, 2012
    #1
    1. Advertisements

  2. Re: You must use the dbSeeChanges option with Open Recordset when accessing a SQL Server table that has an IDENTITY column

    Change

    ..Execute

    to

    ..Execute dbSeeChanges

    In fact, you'd probably be better off using

    ..Execute dbSeeChanges + dbFailOnError


    wrote in message
    news:...

    Please accept my apologies for my ignorance in advance. I am learning and I
    appreciate any feedback on this issue that I may receive. I inherited an
    access database that I migrated to SQL 2008 and I am getting the above error
    in this code:
    Private Sub Restore_Click()
    Dim n As Integer
    Dim lngID As Long
    Dim frm As Form_frmFrontEnd

    On Error GoTo RestoreError:

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

    With SelectedData(Me.lstInactive)
    ' alert the user when no records were selected
    If .Count = 0 Then
    MsgBox "Please select a record before " & _
    "attempting to restore it.", vbInformation
    GoTo ExitRestore:
    End If

    ' get confirmation from user to restore record(s)
    msg = MsgBox("Are you sure you want to restore these record(s)?",
    vbQuestion + vbYesNo)
    If msg = vbNo Then
    GoTo ExitRestore:
    End If

    ' initials progress meter
    SysCmd acSysCmdInitMeter, "Restoring Records Please Wait...", .Count

    For n = 1 To .Count
    ' retrieve the ID number for later use
    lngID = .Item(n)

    ' execute query to restore records
    With CurrentDb.QueryDefs(RESTORE_BIDS_QUERY)
    .Parameters![ID] = lngID
    .Execute

    ' alert user when a record could not be restore
    If .RecordsAffected = 0 Then
    MsgBox "Could not reactive Record #" & lngID,
    vbExclamation
    End If
    End With

    ' update progress meter
    SysCmd acSysCmdUpdateMeter, n
    Next n

    ' refresh the list of records in the listbox
    Me.lstInactive.Requery
    End With

    ' refresh the front end form
    If CurrentProject.AllForms(FRONT_END_FORM).IsLoaded = True Then
    Set frm = Form_frmFrontEnd
    frm.Requery
    End If
    ExitRestore:
    Set frm = Nothing
    SysCmd acSysCmdClearStatus
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

    RestoreError:
    MsgBox Err.Description, vbCritical
    Resume ExitRestore:
    End Sub



    I've seen other threads that discuss this error, but the code example isn't
    the same as what I have and I'm not sure how to incorporate the dbSeeChanges
    command within this code.

    Any assistance would be greatly appreciated and thank you in advance.
     
    Douglas J Steele, Jul 12, 2012
    #2
    1. Advertisements

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Adam
    Replies:
    1
    Views:
    296
    Rob Waibel
    Oct 23, 2003
  2. SlowGirl

    dbSeeChanges & Identity Column error

    SlowGirl, May 5, 2006, in forum: Access Forms Coding
    Replies:
    6
    Views:
    267
    Holly
    May 23, 2006
  3. rmcompute
    Replies:
    3
    Views:
    1,253
  4. J. Keggerlord
    Replies:
    3
    Views:
    168
    Klatuu
    May 4, 2007
  5. sandal
    Replies:
    3
    Views:
    120
    Pat Hartman
    Jan 10, 2008
  6. JR
    Replies:
    3
    Views:
    238
  7. gmazza via AccessMonster.com

    dbSeeChanges error with IDENTITY column

    gmazza via AccessMonster.com, Dec 29, 2009, in forum: Access Forms Coding
    Replies:
    2
    Views:
    194
    gmazza via AccessMonster.com
    Dec 29, 2009
  8. iccsi
    Replies:
    1
    Views:
    1,234
    John W. Vinson
    Oct 13, 2011
Loading...