Update Query!

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

On Wed, 10 Mar 2010 11:44:17 +1300, "Bob Vance" 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]

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

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@uwe wrote in message
news:a4c56d1f35877@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

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/For...ccess/201003/1

On Wed, 10 Mar 2010 17:20:37 +1300, "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

There's an easier way!

Create a new empty database, and use File... Get External Data... Import to
import all 25 tables. For the ones you want empty, check the box "design view
only"; for the rest, check "structure and data".

--

John W. Vinson [MVP]

"Bob Vance" wrote in message
...

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

yj;:!§

"Bob Vance" a écrit dans le message de groupe de
discussion : ...

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

ykkklmmmmùùùùù**




"Bob Vance" a écrit dans le message de groupe de
discussion : ...

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

"Bob Vance" ha scritto nel messaggio
...

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