Delete Query with SQL Back-end error

[T.J. Bernard]

I have a simple delete query, that I am building and executing through code
(when a button is clicked) to delete the current record (long story, but I am
doing it with a delete query). Anyway, once the button is clicked, the
current record is supposed to delete out of the database.

Here is my code

Dim I as Integer
Dim DB as database
Dim qry as querydef
Dim ID as string

txtID.setfocus
ID = txtID.Text

I = MsgBox("Do you want to delete the record?", vbYesNo, "Delete Record")

If I = 6 Then
set db = currentdb

docmd.deleteobject acQuery, "qryDelete"
set qry = db.createquerydef("qryDelete")

with qry
..SQL = "Delete ID, Name, Title From tblBilling Where ID = " & ID
..Execute
end with
End If

Nice and simple. But I get an error due to having a SQL back-end on the
Execute statement:

"You must use the dbSeeChanges option with OpenRecordset when accessing a
SQL Server table that has an IDENTITY Column" [OK]

And then the process stops.

Does anyone know a fix for this, or a work around? I have not run into this
problem before, so I was not sure exactly where to start.

Thank you,

TJ Bernard

 

[Brian]

I have a simple delete query, that I am building and executing through code
(when a button is clicked) to delete the current record (long story, but I am
doing it with a delete query). Anyway, once the button is clicked, the
current record is supposed to delete out of the database.

Here is my code

Dim I as Integer
Dim DB as database
Dim qry as querydef
Dim ID as string

txtID.setfocus
ID = txtID.Text

I = MsgBox("Do you want to delete the record?", vbYesNo, "Delete Record")

If I = 6 Then
set db = currentdb

docmd.deleteobject acQuery, "qryDelete"
set qry = db.createquerydef("qryDelete")

with qry
.SQL = "Delete ID, Name, Title From tblBilling Where ID = " & ID
.Execute
end with
End If

Nice and simple. But I get an error due to having a SQL back-end on the
Execute statement:

"You must use the dbSeeChanges option with OpenRecordset when accessing a
SQL Server table that has an IDENTITY Column" [OK]

And then the process stops.

Does anyone know a fix for this, or a work around? I have not run into this
problem before, so I was not sure exactly where to start.

Thank you,

TJ Bernard

You need to do exactly what it says on the tin i.e. use the dbSeeChanges
option, thus:

qry.Execute dbSeeChanges

Incidentally, you don't need to go through the rigmarole of creating and
later deleting a persistent querydef object. Something like this will work
just as well:

CurrentDb.Execute "Delete ID, Name, Title From tblBilling Where ID = " &
ID, dbSeeChanges

Or, for maximum efficiency, you could create the query as a parameter query,
with ID as a parameter.