Appending/Deleting Records to Access DB using SQL in VB App

[segy]

I need to append a records based on a date from one Access table to another and then delete the same records from the origin table. I've got the query worked out but apparently am not making the correct reference to the data control. I'd appreciate an example if someone's got one available.

[tumblingdown]

more info required...

in what way are you not making the correct reference to the data control, e.g. how do you know your not (what error message do you get)?

[segy]

I tried setting the data control recordsource to "query statements" followed by a refresh. That gives an invalid operation. Is that the correct reference to preceed the query statements?

[tumblingdown]

"query statements" ?

i presume by this you are setting the recordsource to a sql statement. Are you sure the statement is correct - can you set the recordsource to a tablename you know exists?

[BruceG]

I believe you can only use SELECT-type queries with the data control (which I hate to use), not "action" queries, such as INSERT or DELETE. I believe you want to do something like this (this example assumes that "NewTable" and "OldTable" have the same structure):

Dim MyDB As Database

MyDB.Execute "INSERT INTO NewTable " _
           & "SELECT * FROM  OldTable " _
           & "WHERE OldTable.DateField = #1/1/2000#"
MyDB.Execute "DELETE FROM  OldTable " _
           & "WHERE OldTable.DateField = #1/1/2000#"