Delete Table before Update

[Anon]

Hello

1. I have been trying to delete the data in a table
before i update the table. To update the table i have a
SQL query. What is the syntax for adding a DELETE before
the update?

2. I have 3 SQL queries that update the same table, but
from various sources. How can i combine these into 1 SQL
query so that i dont have to 2 SQL statements seperately.

Thanks
EC

 

[Nikos Yannacopoulos]

EC,

1. To delete the table records before updating, you would use something
like:

strSQL = "DELETE * FROM TableName"
CurrentDB.Execute strSQL

(changing TableName to the actual table name)

2. In theory, you could make a Union query on the three queries from which
you update your table, save it as a normal Select query, and use that in
your Update query SQL statement. Yet, it would add nothing to your execution
speed (as a matter of fact, it would probably slow it down), and your
project would be less apparent to follow, I would stick with the three
separate SQL queries executed in sequence.

HTH,
Nikos

 

[Jonathan Blitz]

A small note.

If your table has no referential-integrity connections then you can use the
command

Truncate Table TableName

Instead of Delete

It is quicker and cleaner (also resets any Identity column you may have)

--
Jonathan Blitz
AnyKey Limited
Israel

Tel: (972) 8 9790365

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[John Vinson]

A small note.

If your table has no referential-integrity connections then you can use the
command

Truncate Table TableName

Instead of Delete

It is quicker and cleaner (also resets any Identity column you may have)

This is true for SQL/Server; however, Access does not support the
Truncate command, and (unless it's been added in 2003 and I haven't
seen it) doesn't call Autonumbers Identity either.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps