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

  • Thread starter Thread starter gjbankos
  • Start date Start date
G

gjbankos

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.
 
Change

..Execute

to

..Execute dbSeeChanges

In fact, you'd probably be better off using

..Execute dbSeeChanges + dbFailOnError


wrote in message

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.
 
Back
Top