delete query

[Chris]

Hi all, the query listed below inserts the rows returned by a query into
table tblTeamTask.

How can I change this insert query into a delete query?

strSQLInsert = "INSERT INTO tblTeamTask ( TeamObjectiveID, ObjTaskID,
TeamTask )" & _
"SELECT DISTINCTROW
qryCorrectTeamObjTasks.TeamObjectivesID, qryCorrectTeamObjTasks.ObjTaskID,
qryCorrectTeamObjTasks.Task" & _
" FROM
qryCorrectTeamObjTasks" & ";"
DoCmd.RunSQL strSQLInsert

 

[Jeff Boyce]

Chris

What are you trying to delete? Do you have a BACKUP?!

You could create a new query in design mode, add the table from which you
wish to delete rows, create the criteria you wish to use (e.g., all
OrderDates more than a year old), and run this as a select query first, to
make sure you're getting the records you want.

Then you can change the query type to Delete.

If you want to see the SQL statement, you can change the view after setting
up the query in design mode.

 

[Chris]

I'm trying to delete all the records from a select query. The insert query
will select all these records and insert them into the new table. My
concern is if someone makes a mistake they will have to delete the records.

 

[Gary Walter]

Hi all, the query listed below inserts the rows returned by a query into
table tblTeamTask.

How can I change this insert query into a delete query?

strSQLInsert = "INSERT INTO tblTeamTask ( TeamObjectiveID, ObjTaskID,
TeamTask )" & _
"SELECT DISTINCTROW
qryCorrectTeamObjTasks.TeamObjectivesID, qryCorrectTeamObjTasks.ObjTaskID,
qryCorrectTeamObjTasks.Task" & _
" FROM
qryCorrectTeamObjTasks" & ";"
DoCmd.RunSQL strSQLInsert

Hi Chris,

PMFBI

you might try this on a copy of your db:

DELETE DISTINCTROW t.*
FROM tblTeamTask As t
INNER JOIN
qryCorrectTeamObjTasks As q
WHERE
t.TeamObjectiveID = q.TeamObjectivesID
AND
t.ObjTaskID = q.ObjTaskID
AND
t.TeamTask = q.Task

Whether this could possibly delete *more*
records than what you just added
would depend on structure/indexes
of tblTeamTask. My guess is that you
would not be trying to add what did
not exist previously in tblTeamTask,
so caution is irrelevant, but had to
mention just in case.

Apologies again for butting in.

good luck,

gary