Delete in multi tables by code
| Author |
Message |
|
Kevin M Carma
#1 / 8
|
 Delete in multi tables by code I was wondering if it is possible to delete a record from multi tables at once by code having a common criteria. Senario:
A volunteer passes away (ID # 9899) this volunteer has
been here for 20 years and has had many jobs and many
evaluations.
Since they are deceased we know no one is going to ask if
they have volunteered here as a reference check anymore
so is it possible to delete all records from the Main
Table (1) the Jobs Table (possibility of more than 1)
Evaluations (Possibility of More than 1)Breaks in Service
(Ditto) etc by searching from a special deletit form (All
forms currently do not allow for deletions to prevent
accidental deletion) all refernces to the ID # 9899 from
all tables bearing 1 or more records with that number?
I know someone will ask why, so here goes: we are just
starting testing on this dbase and hope it will be used
long into the future as the usage goes up we do not want
a list of all test data, mistypes, duplicate entries, etc
from 75 years ago (if still in use you understand) so we
are going to create a special delete form that when used
will retrieve the Main record to verify the info before
allowing the delete, but once deeted need to delet all
the relavant records (if any at all) from the other 8 or
9 tables that we use. (for some reason ref integ can not
be enforced on some tables [don't understand why though])
If this can be done could someone provide the code and
also I need the reverse (when a record is added to one
form I need to duplicate the info in another form with a
date 90 days from the date of initial entry) code for
another piece of the project.
Any help is profoundly appreciated
Kevin M carman
|
| Fri, 15 Jul 2005 04:01:00 GMT |
|
 |
|
Van T. Din
#2 / 8
|
Delete in multi tables by code Referential Integrity can only be enforced if the relevant Tables are actually in the SAME mdb / mde file. If you can enforced R.I. with Cascade Delete, you can simply delete the
Volunteer Record and all related Child Records (Breaks-In-Service, Jobs,
...)
will be deleted by Jet automatically.
Otherwise, you need to delete the Child Records and then the Parent Record
by Delete Query/SQL, VBA code. Delete from one Table at a time.
Adding Records: Check Access Help on the Append Query.
(I may be wrong here) However, it sounds like you store (nearly) the same
data in 2 different Tables??? If this is the case, you need to look at the
Table Structure again as data should not be duplicated in the database.
--
HTH
Van T. Dinh
MVP (Access)
Quote: > I was wondering if it is possible to delete a record from > multi tables at once by code having a common criteria. > Senario:
> A volunteer passes away (ID # 9899) this volunteer has
> been here for 20 years and has had many jobs and many
> evaluations.
> Since they are deceased we know no one is going to ask if
> they have volunteered here as a reference check anymore
> so is it possible to delete all records from the Main
> Table (1) the Jobs Table (possibility of more than 1)
> Evaluations (Possibility of More than 1)Breaks in Service
> (Ditto) etc by searching from a special deletit form (All
> forms currently do not allow for deletions to prevent
> accidental deletion) all refernces to the ID # 9899 from
> all tables bearing 1 or more records with that number?
> I know someone will ask why, so here goes: we are just
> starting testing on this dbase and hope it will be used
> long into the future as the usage goes up we do not want
> a list of all test data, mistypes, duplicate entries, etc
> from 75 years ago (if still in use you understand) so we
> are going to create a special delete form that when used
> will retrieve the Main record to verify the info before
> allowing the delete, but once deeted need to delet all
> the relavant records (if any at all) from the other 8 or
> 9 tables that we use. (for some reason ref integ can not
> be enforced on some tables [don't understand why though])
> If this can be done could someone provide the code and
> also I need the reverse (when a record is added to one
> form I need to duplicate the info in another form with a
> date 90 days from the date of initial entry) code for
> another piece of the project.
> Any help is profoundly appreciated
> Kevin M carman
|
| Fri, 15 Jul 2005 04:58:50 GMT |
|
|
|
Kevin M. Carma
#3 / 8
|
Delete in multi tables by code Van: The tables ARE in the same MDB but when trying to enforce RI A97(anyway) tells me that it can not enforce because some tables right now have no records. I do not want a cascade delete for fear of accidental deletion (that is why I asked in Modulesvba (Code) group instead of elsewhere. I want to make absolutely positively unconditionally that the record displayed is the one they want deleted and then and only then do a deete from the 8 or 9 tables (1 at a time but by code based on the ID # as some tables could have 0 or 100+ recoirds for the 1 VID). An append query would not work here as it has in the past replicated all
records a multiple of Records times (802 recors added 802 times for a total
of 643204 records) I have tried this to try to combine the two tables in
question and it doesn't work right (I am not getting paid so I do not want
to spend Hours on end to figure out why) this is why I want to create some
kind of function to add the record to the second table by code so the append
query does not have to be used. as it has never worked for me before. so as
per my original post is there code or isnt there.
Multi responses / suggestions are welcome
Kevin
Quote: > Referential Integrity can only be enforced if the relevant Tables are > actually in the SAME mdb / mde file. > If you can enforced R.I. with Cascade Delete, you can simply delete the
> Volunteer Record and all related Child Records (Breaks-In-Service, Jobs,
> ...)
> will be deleted by Jet automatically.
> Otherwise, you need to delete the Child Records and then the Parent Record
> by Delete Query/SQL, VBA code. Delete from one Table at a time.
> Adding Records: Check Access Help on the Append Query.
> (I may be wrong here) However, it sounds like you store (nearly) the same
> data in 2 different Tables??? If this is the case, you need to look at
the
> Table Structure again as data should not be duplicated in the database.
> --
> HTH
> Van T. Dinh
> MVP (Access)
> > I was wondering if it is possible to delete a record from
> > multi tables at once by code having a common criteria.
> > Senario:
> > A volunteer passes away (ID # 9899) this volunteer has
> > been here for 20 years and has had many jobs and many
> > evaluations.
> > Since they are deceased we know no one is going to ask if
> > they have volunteered here as a reference check anymore
> > so is it possible to delete all records from the Main
> > Table (1) the Jobs Table (possibility of more than 1)
> > Evaluations (Possibility of More than 1)Breaks in Service
> > (Ditto) etc by searching from a special deletit form (All
> > forms currently do not allow for deletions to prevent
> > accidental deletion) all refernces to the ID # 9899 from
> > all tables bearing 1 or more records with that number?
> > I know someone will ask why, so here goes: we are just
> > starting testing on this dbase and hope it will be used
> > long into the future as the usage goes up we do not want
> > a list of all test data, mistypes, duplicate entries, etc
> > from 75 years ago (if still in use you understand) so we
> > are going to create a special delete form that when used
> > will retrieve the Main record to verify the info before
> > allowing the delete, but once deeted need to delet all
> > the relavant records (if any at all) from the other 8 or
> > 9 tables that we use. (for some reason ref integ can not
> > be enforced on some tables [don't understand why though])
> > If this can be done could someone provide the code and
> > also I need the reverse (when a record is added to one
> > form I need to duplicate the info in another form with a
> > date 90 days from the date of initial entry) code for
> > another piece of the project.
> > Any help is profoundly appreciated
> > Kevin M carman
|
| Fri, 15 Jul 2005 05:59:42 GMT |
|
|
|
John Vinso
#4 / 8
|
Delete in multi tables by code On Sun, 26 Jan 2003 12:01:00 -0800, "Kevin M Carman" Quote:
>we
>are going to create a special delete form that when used
>will retrieve the Main record to verify the info before
>allowing the delete, but once deeted need to delet all
>the relavant records (if any at all) from the other 8 or
>9 tables that we use. (for some reason ref integ can not
>be enforced on some tables [don't understand why though])
Well, you've got a MUCH worse problem than deleting then. A database without relational integrity has no integrity, and no guarantee whatsoever that you don't have garbage data entered. FIX IT, and set Cascade Deletes to True for these relationships. What error are you getting when you try to enforce RI?
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Fri, 15 Jul 2005 11:44:36 GMT |
|
|
|
Kevin M. Carma
#5 / 8
|
Delete in multi tables by code I can not create the screenshot of the message but is says microsoft Access can not create the RI because 1 table (evals) violates RI rules -- this is because right now the tale has nothing in it. this is why again I ask for CODE to do this programmatically as RI in some cases as it stands right now will not work. How come people can not answer my original question without saying the problems I have are elsewhere. I am not a programmer, I am an average Joe who taught himself Access 97 and never used modules till now. All I want is the code for my original posts. Appends never work for me properly and the relationships are there but enforce RI will not work in all cases Also by doing cascade deletes this defeats the purpose of making absolutely sure the record being displayed is in fact the one to be deleted. Is there code for my original question or isn't there? If you can't help then don't tell me why it is a bad Idea, because it probably is but if it gets the job done then what do I care Thank but no help here on this reply
Kevin
Quote: > On Sun, 26 Jan 2003 12:01:00 -0800, "Kevin M Carman"
> >we
> >are going to create a special delete form that when used
> >will retrieve the Main record to verify the info before
> >allowing the delete, but once deeted need to delet all
> >the relavant records (if any at all) from the other 8 or
> >9 tables that we use. (for some reason ref integ can not
> >be enforced on some tables [don't understand why though])
> Well, you've got a MUCH worse problem than deleting then. A database
> without relational integrity has no integrity, and no guarantee
> whatsoever that you don't have garbage data entered. FIX IT, and set
> Cascade Deletes to True for these relationships.
> What error are you getting when you try to enforce RI?
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Fri, 15 Jul 2005 12:55:21 GMT |
|
|
|
John Vinso
#6 / 8
|
Delete in multi tables by code On Mon, 27 Jan 2003 04:55:21 GMT, "Kevin M. Carman" Quote:
>I can not create the screenshot of the message but is says microsoft Access >can not create the RI because 1 table (evals) violates RI rules -- this is >because right now the tale has nothing in it. this is why again I ask for >CODE to do this programmatically as RI in some cases as it stands right now >will not work. How come people can not answer my original question without >saying the problems I have are elsewhere. I am not a programmer, I am an >average Joe who taught himself Access 97 and never used modules till now. >All I want is the code for my original posts. Appends never work for me >properly and the relationships are there but enforce RI will not work in all >cases Also by doing cascade deletes this defeats the purpose of making >absolutely sure the record being displayed is in fact the one to be deleted. >Is there code for my original question or isn't there? If you can't help >then don't tell me why it is a bad Idea, because it probably is but if it >gets the job done then what do I care >Thank but no help here on this reply
I'm sorry you're so offended, Kevin. I believe that my advice was sound. If you want to maintain a database without relational integrity, I think you're exposing yourself to serious data security problems; and that it should be possible to establish RI in your database (an empty table will NOT block RI, there's another problem). But if that's the way you want it, I wish you good luck. To delete records programatically from multiple tables, I would
suggest creating the SQL for as many Delete queries as you need
dynamically in code. Your "delete master record" button should start a
Transaction, run the delete queries for the related tables, and then
delete the record from the master table; and finally commit the
transaction. See the online help in the VBA editor for BeginTrans for
an overview.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Sat, 16 Jul 2005 00:59:46 GMT |
|
|
|
Kevin M. Carma
#7 / 8
|
Delete in multi tables by code John, I am sorry for how that came out, I am not offended just frustrated that my questions don't get answered the way I need them and people just tell me its bad design or that the way I want to have something done is not the best way, I probably don't build efficiently but it does work for my purposes. I have attached a word document that shows the snap of the error message and
the Tools-> analyze -> Documenter (106 pages (I gave you every table in my
BE file and the relationships go with it. Some of the tables do show Cascade
update but from the way I understand it the CU would only update the fields
that are linked. The Vol Database table does (I realize have a lot of fields
but I can not find a way to cascade add into the 6 tables that were combined
to make it so I put all one to one relationships in the 1 table) I know...
not very efficient but it works. Hopefully you can take a look and tell me
why it doesn't work.
The volunteer database table would have the main record and the evals and
possibly others that have no ERI on them come up with the same error.
Again I a Sorry
Kevin
I have tried 7 times to send this to group with a file to document and
display my message and it does not appear to work so I 'll write it off as a
bad Idea.
I undertand what you said to do but I do not know the code to do it so that
is why I keep asking to post the code so I may copy/Paste into my db.
TIA
Quote: > On Mon, 27 Jan 2003 04:55:21 GMT, "Kevin M. Carman"
> >I can not create the screenshot of the message but is says microsoft
Access
> >can not create the RI because 1 table (evals) violates RI rules -- this
is
> >because right now the tale has nothing in it. this is why again I ask
for
> >CODE to do this programmatically as RI in some cases as it stands right
now
> >will not work. How come people can not answer my original question
without
> >saying the problems I have are elsewhere. I am not a programmer, I am an
> >average Joe who taught himself Access 97 and never used modules till now.
> >All I want is the code for my original posts. Appends never work for me
> >properly and the relationships are there but enforce RI will not work in
all
> >cases Also by doing cascade deletes this defeats the purpose of making
> >absolutely sure the record being displayed is in fact the one to be
deleted.
> >Is there code for my original question or isn't there? If you can't help
> >then don't tell me why it is a bad Idea, because it probably is but if it
> >gets the job done then what do I care
> >Thank but no help here on this reply
> I'm sorry you're so offended, Kevin. I believe that my advice was
> sound. If you want to maintain a database without relational
> integrity, I think you're exposing yourself to serious data security
> problems; and that it should be possible to establish RI in your
> database (an empty table will NOT block RI, there's another problem).
> But if that's the way you want it, I wish you good luck.
> To delete records programatically from multiple tables, I would
> suggest creating the SQL for as many Delete queries as you need
> dynamically in code. Your "delete master record" button should start a
> Transaction, run the delete queries for the related tables, and then
> delete the record from the master table; and finally commit the
> transaction. See the online help in the VBA editor for BeginTrans for
> an overview.
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Sat, 16 Jul 2005 21:54:44 GMT |
|
|
|
John Vinso
#8 / 8
|
Delete in multi tables by code On Tue, 28 Jan 2003 13:54:44 GMT, "Kevin M. Carman" Quote:
>John, >I am sorry for how that came out, I am not offended just frustrated that my >questions don't get answered the way I need them and people just tell me its >bad design or that the way I want to have something done is not the best >way, I probably don't build efficiently but it does work for my purposes. >I have attached a word document that shows the snap of the error message and
>the Tools-> analyze -> Documenter (106 pages (I gave you every table in my
>BE file and the relationships go with it. Some of the tables do show Cascade
>update but from the way I understand it the CU would only update the fields
>that are linked. The Vol Database table does (I realize have a lot of fields
>but I can not find a way to cascade add into the 6 tables that were combined
>to make it so I put all one to one relationships in the 1 table) I know...
>not very efficient but it works. Hopefully you can take a look and tell me
>why it doesn't work.
I'm sorry; but not only do I not generally download attachments, even if you had been able to post it, but reading through 106 pages of documentation goes way beyond what's reasonable to expect of an unpaid volunteer. I do have clients who are paying me to do less. Here's some sample transaction code.
Dim ws As Workspace
Dim bInTrans As Boolean
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb
fInTrans = False
... <code to prepare for your Delete queries>
ws.BeginTrans ' start the transaction
bInTrans = True ' flag that a transaction is active
Set qd = db.Querydefs("qryDelA")
qd.Execute dbFailOnError
Set qd = db.QueryDefs("qryDelB")
qd.Execute dbFailOnError
<etc. etc.>
ws.Commit ' if all worked, commit the transaction
... <more code>
Proc_Exit: Exit Sub
Proc_Error: ' error handler
If bInTrans Then
' If an error occured during the delete queries, roll back
' the entire transaction and undo all the deletes
ws.Rollback
End If
<other error handling code>
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Sun, 17 Jul 2005 05:07:50 GMT |
|
| |
|
