moving record between two table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a temp table that update an other table1 if a
condition is true

after updating table one i want the record withe the
condition true in temp table to be deleted
strSQL = "INSERT INTO [table1] (..
Select [temp table].*
where condition true

strSQL = "SELECT [temp table].* INTO [new work table]
FROM [temp table] WHERE condition=False));
strSQL = "SELECT [new work table]* INTO [temp table)]
DoCmd.DeleteObject acTable, "new work table"

my question is if there is any easier way to do it
because whene i run the code i get an error message "temp
table already exist" and i can not delete it

thak you
 
are you saying that you want to select a specific record in one table,
append (insert) that record into another table, and then delete the record
from the first table?
if so, just use the same criteria and do an Insert, and then a Delete,
essentially. examples:

INSERT INTO Table2 ( FieldA, FieldB )
SELECT FieldA, FieldB
FROM Table1
WHERE Table1.FieldA="something";

DELETE *
FROM Table1
WHERE Table1.FieldA="something";

or do you want to insert a record from the first table into the second
table, and then delete the first table entirely? if so, then use the first
SQL statement to do the record insert, then use the following command to
delete the first table, as

DoCmd.DeleteObject acTable, "MyTable"

hth
 
Back
Top