• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 889
  • Last Modified:

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
0
rgroves
Asked:
rgroves
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now