dbSeeChanges error on SQL DELETE

I get a "Must use dbSeeChanges" error when executing a DELETE statement. But when I insert dbSeeChanges as an option, I get: "Expected =".

Setup: I am using Access as a front end to a SQL Server Database (linked tables).

Please help, thanks.

Code:

Private Sub cmdDelete_Click()
Dim intID As Integer
   
If Me.txtModuleID > 0 Then
Set db = CurrentDb
intID = Me.txtModuleID
       
 If (MsgBox("Delete this module and references to all lessons?",vbYesNo,"Warning") = vbYes)Then
 db.Execute ("DELETE FROM Module WHERE ModuleID = " & intID)
 db.Execute ("DELETE FROM ModuleLesson WHERE ModuleID = " & intID)
 db.Execute ("DELETE FROM CourseModule WHERE ModuleID = " & intID)

Form.Requery
End If
End If

End Sub
rgrovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianWrenCommented:
If (MsgBox("Delete this module and references to all lessons?",vbYesNo,"Warning") = vbYes)Then
 db.Execute ("DELETE * FROM Module WHERE ModuleID = " & intID)
 db.Execute ("DELETE * FROM ModuleLesson WHERE ModuleID = " & intID)
 db.Execute ("DELETE * FROM CourseModule WHERE ModuleID = " & intID)


Brian
0
tkuppinenCommented:
You should probably also rename your table to something other than module.  It is an Object in Access and this table name may cause you problems down the road.

Also if you have referential integrity checked off under relationships you should delete from the child tables first.
0
rgrovesAuthor Commented:
But these aren't the errors I'm encountering..
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rgrovesAuthor Commented:
Edited text of question.
0
rgrovesAuthor Commented:
Forgot to mention that this is communicating with a SQL Server backend--Access is just the interface.
0
BrianWrenCommented:
Did adding the asterisk so that the query had something to work on help?

Brian
0
rgrovesAuthor Commented:
Nope
0
BrianWrenCommented:
Try taking the parens out of the syntax.  None of the examples in Access help use them, and they might be modifying the action of the Execute method.

Then post what happens back here, if you would.

Brian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bknouseCommented:
Try building your SQL statements as string variables.  It seems like the variable is NOT getting appended to the statement in the execute line.

e.g.

Dim Statement as String

Statement = "DELETE * FROM Module WHERE ModuleID = " & intID

db.Execute Statement

Brent

(Just a note: Field names aren't required in a Delete SQL (or the askerisk).  Having field names or an askerisk doesn't mean anything, since we are deleting entire rows.)
0
BrianWrenCommented:
----------------------------------------------------------------------------------

This is probably not it, but it's a thought.

You have:

  Dim intID As Integer

and

 . . . WHERE ModuleID = " & intID)

Most IDs are AutoNumber which is a Long Integer.  Now I realize that the & operator, when concatenating a numeric value to a string converts the number to a string, and so the finer points of nueric types ought to be eliminated.  But there really doesn't seem to be anything actually wrong with your code, so maybe its an anomally...

Have you tried putting the following into the function and analysing what's going on?

Private Sub cmdDelete_Click()

   Dim intID As Integer
     
   If Me.txtModuleID > 0 Then
      Set db = CurrentDb
      intID = Me.txtModuleID
         
      If MsgBox("Delete this mod. & references to all lessons?", vbYesNo, "Warning") = vbYes Then
         Debug.Print "DELETE FROM Module WHERE ModuleID = " & intID
         Stop
         db.Execute ("DELETE FROM Module WHERE ModuleID = " & intID)
          .
          .
          .
      End If
   End If

End Sub

Brian
0
rgrovesAuthor Commented:
Adjusted points to 75
0
rgrovesAuthor Commented:
It worked!!

The parentheses messed up the method when I added the dbSeeChanges method. So my final statement looks like this:

db.Execute "DELETE FROM Course WHERE CourseID = " & intID, dbSeeChanges

Thanks a ton!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.