Prev: juhgf
Next: Combo Box
From: Bob Vance on

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

 |  Next  |  Last
Pages: 1 2
Prev: juhgf
Next: Combo Box