Deleting Records in SQL Server back end - DBSeeChanges

[Kiwiman]

Hi All

We have moved an access backend to SQL Server 2000, and use Access 2k as the front end.

When a user tries to delete a line record using the following code

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

we get an error message

The DoMenuItem action was cancelled

I changed the code to

DoCmd.RunCommand acCmdDeleteRecord

but got a message

You must see the DBSeeChanges option with open recordsets when access a SQL Server table that has an identity column

Doing some searching on this forum, I amended my code to be the below. I get no error messages, but the record is not deleted either.

Any ideas?

Private Sub cmdDelRec_Click()
On Error GoTo Err_cmdDelRec_Click

Dim strSQL As String
Dim intLineNo As Integer

intLineNo = Me.ContLineNo


strSQL = "delete from tblContAttribLin where ContLineNo = " & intLineNo

If MsgBox("Delete this record?", vbQuestion + vbYesNo, "Delete?") = vbYes Then

    CurrentDb.Execute strSQL, dbSeeChanges
    Me.Refresh

End If


    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    'DoCmd.RunCommand acCmdDeleteRecord

Exit_cmdDelRec_Click:
    Exit Sub

Err_cmdDelRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelRec_Click
    
End Sub

 

[Kiwiman]

Howzit

This syntax works fine on our Dev box - which has recently been upgraded to SQL Server 2005, so it doesn't look like it is a syntax issue, just something I'm missing??