Update Query!
in [General]
|
From: Bob Vance on 9 Mar 2010 17:44 Can I have an UpDate query to delete all content of all fields in tblHorses tblOwners tblInvoices -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3
From: John W. Vinson on 9 Mar 2010 18:02 On Wed, 10 Mar 2010 11:44:17 +1300, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: > >Can I have an UpDate query to delete all content of all fields in >tblHorses >tblOwners >tblInvoices You probably need three queries, one for each table. They can be run in sequence from a Macro or from VBA code. If you have cascading deletes set up on the relationships between your table - e.g. tblHorses is related one to many to tblOwners, with the Cascade Deletes checkbox set, and similarly for tblInvoices - then deleting a record from tblOwners will delete all the records for that owner from the other two tables. It won't "delete the content of the fields" - it will remove the entire record leaving no trace that it was there. (Be sure you have a backup!!!!! Deletion is a one-way street!) -- John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 9 Mar 2010 18:02 Bob: If by 'delete all contents' you mean leave the rows in place but with Nulls in all columns (which is what your reference to an UPDATE query would suggest) , the answer is No, or more accurately it is if the table definitions are legitimate, as no primary key or part thereof can be Null. You could delete all rows from all three tables with a single DELETE query if cascade deletes were enforced in the relationships from owners to horses and owners to invoices (assuming only one owner per horse), but I think that would be rather dangerous. If you need to empty all three tables I'd be inclined to enforce referential integrity and then execute three DELETE queries, horses and invoices first, then owners. Ken Sheridan Stafford, England Bob Vance wrote: >Can I have an UpDate query to delete all content of all fields in >tblHorses >tblOwners >tblInvoices -- Message posted via http://www.accessmonster.com
From: Bob Vance on 9 Mar 2010 23:20 Well actually I have 25 tables and want to delete the contents of 20 of them to form a empty database that my friends can use without my data in it, so i should still look at cascade delete...Thanks bob "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a4c56d1f35877(a)uwe... > Bob: > > If by 'delete all contents' you mean leave the rows in place but with > Nulls > in all columns (which is what your reference to an UPDATE query would > suggest) > , the answer is No, or more accurately it is if the table definitions are > legitimate, as no primary key or part thereof can be Null. > > You could delete all rows from all three tables with a single DELETE query > if > cascade deletes were enforced in the relationships from owners to horses > and > owners to invoices (assuming only one owner per horse), but I think that > would be rather dangerous. If you need to empty all three tables I'd be > inclined to enforce referential integrity and then execute three DELETE > queries, horses and invoices first, then owners. > > Ken Sheridan > Stafford, England > > Bob Vance wrote: >>Can I have an UpDate query to delete all content of all fields in >>tblHorses >>tblOwners >>tblInvoices > > -- > Message posted via http://www.accessmonster.com >
From: PieterLinden via AccessMonster.com on 9 Mar 2010 23:54
Bob Vance wrote: >Well actually I have 25 tables and want to delete the contents of 20 of them >to form a empty database that my friends can use without my data in it, so i >should still look at cascade delete...Thanks bob > >> Bob: >> >[quoted text clipped - 21 lines] >>>tblOwners >>>tblInvoices Being disgustingly lazy, I would probably do something like: 1. create a query to get the list of tables from the database. 2. loop through the records returned and run some dynamic SQL to delete the contents of the table. something like... Sub DeleteContentsOfTables() dim rsT as dao.recordset dim strTables as string ' define the list of tables ' the NOT IN clause is to skip over the tables I want to keep... strTables = "SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like 'MSys*') AND ((MSysObjects.Type)=1) AND MSysObjects.Name NOT IN ('Keep This One', 'Keep That One'));" ' Open a recordset of tables to delete so they can be processed one at a time set rsT = DBEngine(0)(0).OpenRecordset strTables, dbOpenForwardOnly Do until rsT.EOF ' delete the contents of the table. DBEngine(0)(0).Execute "DELETE * FROM [" & rsT.Name & "]", dbFailOnError rsT.MoveNext Loop rsT.close set rsT = nothing -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 |