CurrentDB.Execute Issue

Question

FE Access 2007, BE SQL Server 2005.

I have some code to delete a rather complex structure. Basically, Projects beget Budgets, Work Orders, Bills of Material, etc. My client has begun using my system to create "pro-forma" bills of material, etc. for budgetting purposes on projects that have not been approved. This lets them prepare detailed proposals and provides for quick executions once they have approval. But a large percentage never get approved, and they wanted a simple way to delete the entire structure for a given project.

Expenses (Purchase Orders, Timesheets, Parts Usage, etc.) are charged against Work Orders, and in some cases (either correctly or erroneously) expenses have been charged to Work Orders that are slated to be deleted in this process. Obviously, those deletes will fail due to "key violations" because my system is fully normalized with all Foreign Keys protected by Referential Integrity. There are just too many categories of spending to capture them all in a SQL statement, it would be very long and complex.

In ACCESS, if I use the command "DoCmd.RunSQL Delete...", ACCESS will detect the key violations and show a messagebox to the user asking if he/she wants to continue. If I set "DoCmd.SetWarnings False", Access will continue without the Message Box, deleting only the items with no key violations. When I use "CurrentDB.Execute", however, I get a non-recoverable ODBC error instead. Here's a code snippet:

        CurrentDb.Execute "DELETE FROM tblCPJE WHERE tblCPJE.JEID in (SELECT DISTINCT CPJEID from tblCPTransaction WHERE CPProjectID=" & Me.cbProjectID & ")", dbFailOnError + dbSeeChanges
        CurrentDb.Execute "DELETE FROM tblCPTrans WHERE CPProjectID=" & Me.cbProjectID, dbFailOnError + dbSeeChanges

How do I get this to execute, deleting only what it can without violating Referential Integrity?

---

Jim

Answer 1

Jim

.Execute is not as forgiving as .RunSQL. There is no way to do what you want if there are child records. It might work (although I haven't tested) if you have cascading deletions on those relationships, but that could end up biting you.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

I curious as to how referential integrity protects incorrectly posted expenses from being deleted.  The only way referential integrity protects against a record being deleted is if it is on the one side of a one to many relationship.  In such a case the record is the "parent" and the existence of "child" records prevents its deletion.  If you post an expense item for Parts Usage what is the corresponding "child" record that protects it from being deleted?

---

http://www.saberman.com

Answer 3

I probably just wasn't clear. Top that off with some hair-brained design (I must have been drunk that day....)

Anyway, the CPTransaction table is protected against the deletion of its parent Journal Entry by a foreign key enforcement of referential integrity (the JE ID is stored as a foreign key in each CPTransaction.) I'm forced to delete the CPTransaction, then the JE, which is no problem (and opposite of how I have the above statements ordered...) except that I have to go through the CPTransaction to identify the project(s), but once I delete the CPTransaction, I have no way of getting back to the JE ID (using standard set-based SQL, as opposed to procedural methods, SQL or otherwise.)

That, of course, has nothing to do with the question, which Bill answered for me. Thank you, Bill.

I should have set up the JE with cascading deletes of the CPTransactions, and that would have worked perfectly. By setting it up backwards, I have made life difficult for myself, and I'm still recovering.

And, even as I type this, I still struggle with the best approach. I did store the debit and credit CPTransaction ID in the JE, so I'll probably just use them to accomplish the task. No referential integrity on them, since they can be null (I know, one-sided JE's are a no-no...)

---

Jim

Answer 4

That, of course, has nothing to do with the question, which Bill answered for me. Thank you, Bill.

Jim

You're welcome, Jim.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals