We help IT Professionals succeed at work.

Delete button VBA error, must use dbSeeChanges with OpenRecordset

Member_2_1316035
Member_2_1316035 asked
on
I have a button on a form that deletes a record. This code worked fine when the backend tables were Access tables.  But when the backend was moved to SQL server I am now getting the following error:
"You must use the dbSeeChanges option with OpenRecordset when assessing a SQL Server table that has an IDENTITY column."

I am guessing I need to change the syntax of the dsql string (remove the *?) and perhaps change how I am handling the recordset, but I've tried a variety of things with no luck.


Private Sub btnDelete_Click()

Dim dSql As String, usrResponse
usrResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Record")

If usrResponse = vbYes Then
dSql = "delete * from Admits where [Admit Key]=" & Me.[txtAdmitKey]
CurrentDb.Execute (dSql)
Me.Requery
Forms!frm_Student.sfrm_adm_browse.Form.Requery
DoCmd.Close acForm, "frm_adm_detail"
DoCmd.Close acForm, "frm_adm_edit"

End If

End Sub


Thanks so much for your help.
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Like so ...?

CurrentDb.Execute (dSql , dbSeeChanges)

Author

Commented:
That was the first thing I tried. But then I get a Compile Error:  Expected: =



DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
no parens

CurrentDb.Execute dSql , dbSeeChanges
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Also

dSql = "delete * from Admits where [Admit Key]=" & Me.txtAdmitKey ' no brackets

OR ...if AdmitKey is text

dSql = "delete * from Admits where [Admit Key]=" & Chr(34) & Me.txtAdmitKey  & Chr(34)

mx

Author

Commented:
Still get same error.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok ... post the exact code you have at this point ...

mx

Author

Commented:
Sorry, I posted that last comment before I saw your suggestion to change the dSql.  Let me try the change in SQL you suggest and I'll let you know.  I figured it was something witht that.  AdmitKey is an autonumber.
Most Valuable Expert 2012
Top Expert 2013

Commented:
With a SQL back end, you may need to do this:


dSql = "delete * from Admits where [Admit Key]=" & Me.[txtAdmitKey]
docmd.RunSQL sSQL

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Maybe

dSql = "delete Admits.* from Admits where [Admit Key]=" & Me.txtAdmitKey ' no brackets

OR ...if AdmitKey is text

dSql = "delete Admits.* from Admits where [Admit Key]=" & Chr(34) & Me.txtAdmitKey  & Chr(34)
Most Valuable Expert 2012
Top Expert 2013
Commented:
Sorry - typo:


dSql = "delete * from Admits where [Admit Key]=" & Me.[txtAdmitKey]
docmd.RunSQL dSQL 

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
What is wrong with Execute ?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"I get a Compile Error:  Expected: ="

That is a syntax error ...

mx

Author

Commented:
THANKS!!

Author

Commented:
DatabaseMX:  When my db was Access, the Execute worked just fine.  It was only when I moved to SQL Server that it stopped working.  I figured it was a syntax error in my dSql that wasn't working on the SQL db but couldn't get it to ever execute without compile error.

mbizup: Thanks for the solution.

Thank you for both of your efforts.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So, you are saying the Execute method does not work on a SQL Server backend ?

mx

Author

Commented:
I do not know enough about the Execute method on the SQL Server backend to say either way.  I just couldn't get my dSql syntax (that worked in Access) to work with the Execute method on the SQL Server backend.  If I changed my access syntax to the correct SQL server syntax would have it worked?  Everytime I tried to fix the syntax with what I thought was correct SQL Server syntax, I still kept getting the dbSeeChanges error.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, not sure about the syntax, but pretty sure the Execute method works against SQL Server. I don't seen anything in the Help file indicating to the contrary.

PLUS ... it has the added benefit of the dbFailOnError argument, which will generate a trappable error - that you may want to know about.

CurrentDb.Execute dSql, dbSeeChanges + dbFailOnError

mx

Author

Commented:
DatabaseMX: Your comment made me curious so I did a quick google search and found this post on another forum (I hope it's not illegal to post an outside link) that contains some info that might be relevant to this issue.

http://www.pcreview.co.uk/forums/acc2000-docmd-runsql-vs-currentdb-execute-t2503687.html


In the post it states:  

"What you can't do with the Execute method is use queries that refer to
controls on forms -- *unless* you fill those values in as parameters
before executing. I use a QueryDef object and its Parameters collection
to accomplish this, when I have to."

Since my query was looking to a control on the form for it's value, is that where my problem was?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"*unless* you fill those values in as parameters
before executing."
OR .... wrap the Forms reference with the Eval() statement.

But there is no Forms reference here per se
dSql = "delete Admits.* from Admits where [Admit Key]=" & Me.txtAdmitKey

That would be like this:
dSql = "delete Admits.* from Admits where [Admit Key]=" & Forms!YourFormName!txtAdmitKey

and yes, you get the Parameter issue .. and you can deal with it by using Parameters ... OR

dSql = "delete Admits.* from Admits where [Admit Key]=" & Eval("Forms!YourFormName!txtAdmitKey")

And it's not just the Execute method that has this issue.  Same if you use OpenRecordset  OR DoCmd.RunSQL

mx
Most Valuable Expert 2012
Top Expert 2013

Commented:
I ran across similar posts when we were upsizing our databases to SQL Server.

It's possible that using parameters would do the trick with the Execute statement, but with the time frame involved in our upgrade and the amount of code that needed to be changed, doing a find and replace with DoCmd.RunSQL was the simplest solution.  It involved no other change to the code or to SQL statements we had embedded in VBA.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
What I'm saying is ... glad you got a solution, but I see no reason why Execute is not working, other than a syntax issue.

mx

Author

Commented:

MX:  Out of curiosity I will try to change my syntax as you suggest and see if the execute method does work.
mbizup: I agree, the RunSQL command is a very easy and simple solution.  Although without the benefits of the FailOnError as MX points out.

Thank you both for the additional comments.  I always learn so much from you experts when I post on here.