Execute Delete Query against SQL Table 1

[Spenney]

I have inherited an Access database and been tasked with 'upsizing' the tables into a SQL Server database. I am now in the process of testing the functionality of the 'front-end'. The following code (which deletes the current record) worked with the Access tables...

Private Sub cmdDelete_Click()

Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim fBegun As Boolean

    On Error GoTo cmdDelete_Click_Err
    
    Set wrk = DBEngine.Workspaces(0)
    Set db = wrk.Databases(0)
    Set qdf = db.QueryDefs("qdelBusinessAppServer")
    Set prm = qdf.Parameters("BusinessAppServerID")
    prm.Value = Me.lngBusinessAppServerID
    wrk.BeginTrans
    fBegun = True
    qdf.Execute dbFailOnError
    wrk.CommitTrans
    fBegun = False
    Me.Requery
cmdDelete_Click_Exit:
    Set prm = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub
cmdDelete_Click_Err:
    Select Case Err.Number
        Case 3218
            'Cause retry until the lock is cleared and it succeeds
            Resume
        Case Else
            If fBegun Then
                wrk.Rollback
            End If
            MsgBox Err.Number & " " & Err.Description, vbCritical, _
                    "Form_sfrmBusinessApplications: cmdDelete_Click"
    End Select
    Resume cmdDelete_Click_Exit
End Sub

The query named "qdelBusinessAppServer" in the code is a parameter query, and works correctly if executed manually with a supplied parameter. SQL...

DELETE tblBusinessAppServer.lngBusinessAppServerID
FROM tblBusinessAppServer
WHERE (((tblBusinessAppServer.lngBusinessAppServerID)=[BusinessAppServerID]));

When the code is executed with the SQL tables, I received the following error...
"3622 You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column"

This I addressed by amending the execute line to

qdf.Execute ([dbSeeChanges dbFailOnError])

I'm not sure if that is entirely correct, but it doesn't throw the 3622 error any longer.

Now I have a different error (still caused by the qdf.Execute line)...
"2465 [Database] can't find the field '|' referred to in your expression"
I understand that '|' is a placeholder for an object name rather than a specific object, so this isn't much help.

Does anybody have any idea what may be causing this ?

Thank you.

 

[PHV]

And what about this ?
qdf.Execute dbFailOnError + dbSeeChanges

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Spenney]

PHV

Thank you very much.

Spot on once again...

Stuart