Delete QueryDef Error 3622 Open SQL Server Table with Identity Column-CodePudding

I have a table in SQL Server and a form in Access. I have a query with a parameter to delete a record providing a given ID. In the click event of a button I use a querydef object and try to execute the query and throws this error:

Error 3622 Open SQL Server Table with Identity Column

You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column

The table definition:

CREATE TABLE [Formativa].[SEPE_FAMILIAS_FORMATIVAS](
[ID_FAMILIA_FORMATIVA] [int] IDENTITY(1,1) NOT NULL,
[CODIGO_FAMILIA] [nvarchar](255) NULL,
[NOMBRE_FAMILIA] [nvarchar](255) NULL,
[FECHA_CONTROL] [timestamp] NOT NULL,
CONSTRAINT [SEPE_FAMILIAS_FORMATIVAS_PK] PRIMARY KEY NONCLUSTERED

The querydef:

PARAMETERS [ID_FAMILIA_FORMATIVA] Long;
DELETE SEPE_FAMILIAS_FORMATIVAS.ID_FAMILIA_FORMATIVA, SEPE_FAMILIAS_FORMATIVAS.*
FROM SEPE_FAMILIAS_FORMATIVAS
WHERE (((SEPE_FAMILIAS_FORMATIVAS.ID_FAMILIA_FORMATIVA)=[ID_FAMILIA_FORMATIVA]));

The code:

Dim dbCurrDB As DAO.Database
Dim qryDelFamilia As DAO.QueryDef

Dim lngIdFamiliaFormativa As Long

Set dbCurrDB = CurrentDb
Set qryDelFamilia = dbCurrDB.QueryDefs("SEPE_FAMILIAS_FORMATIVAS_ELIMINAR")

With qryDelFamilia

    .Parameters(0) = lngIdFamiliaFormativa
    .Execute
    
    If .RecordsAffected = 0 Then
        mVntMsg = "Ha sucedido un error al eliminar la familia formativa. No es posible proseguir."
        MsgBox mVntMsg, vbExclamation   vbOKOnly, "ERROR AL ELIMINAR"
        Set dbCurrDB = Nothing
        Exit Sub
    End If
    
End With

¿Any idea of what's wrong? I need to use saved queries instead of SQL strings in code because of mantainability, traceability, etc... Also I need to use parameters or equivalent to be able to delete a particular record.

Edit:

I tried with a pass-through query with a variable but I don't know how to establish the value of the variable when I execute the query in vb.

DECLARE @ID_FAMILIA_FORMATIVA Int
BEGIN
DELETE FROM EDIBON_ERP.Formativa.[SEPE_FAMILIAS_FORMATIVAS]
WHERE EDIBON_ERP.[Formativa]. 
[SEPE_FAMILIAS_FORMATIVAS].ID_FAMILIA_FORMATIVA=@ID_FAMILIA_FORMATIVA
END

Thank you

CodePudding user response：

As the error says, you need to add the dbSeeChanges options when executing the query.

.Execute dbSeeChanges

It's best to add the dbFailOnError option as well, which will rollback changes if the query fails for whatever reason. The option will throw a runtime error though, so you need to have error handing in the method.

On Error GoTo Trap

With qryDelFamilia

    .Parameters(0) = lngIdFamiliaFormativa
    .Execute dbFailOnError   dbSeeChanges 
    
    If .RecordsAffected = 0 Then
        mVntMsg = "Ha sucedido un error al eliminar la familia formativa. No es posible proseguir."
        MsgBox mVntMsg, vbExclamation   vbOKOnly, "ERROR AL ELIMINAR"
        Set dbCurrDB = Nothing
        Exit Sub
    End If
    
End With

Leave:
    Exit Sub

Trap:
    MsgBox Err.Description, vbCritical
    Resume Leave