In Access 2007 Is there a way to Export a record into new table and del it in old table.

Is there a way that In Access 2007 if  Table1[CMTID] = Table2[CMTID] then export to Table3 and Del out Table1. Using VBA or Access Query.
ca1358Asked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Compare with: tables dbo_A, dbo_B, t3. If dbo_A.a=dbo_B.a then write record to t3
Then delete record from dbo_A. Make sure that t3 structure is the same as dbo_A.

Private Sub Command40_Click()
    DoCmd.RunSQL "INSERT INTO t3 SELECT dbo_A.* FROM dbo_A INNER JOIN dbo_B ON dbo_A.a = dbo_B.b;"
    DoCmd.RunSQL "DELETE dbo_A.* FROM dbo_A WHERE dbo_A.a IN (SELECT dbo_B.a FROM dbo_B);"
END Sub

Open in new window

0
 
Helen FeddemaCommented:
What records are being compared?
0
 
ca1358Author Commented:
If the field [CMTID] Matches then export out and then del record in Table1.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ca1358Author Commented:
Having trouble with the Delete Statement, Error Syntax error in Delete Statement.  I including a example file.
0
 
ca1358Author Commented:
Thank you , this is my final code

DoCmd.RunSQL "INSERT INTO Table3 ( CMTID )SELECT Table1.CMTID FROM Table1 INNER JOIN Table2 ON Table1.CMTID = Table2.CMTID;"
DoCmd.RunSQL "Delete Table1.CMTID FROM Table1 WHERE [Table1]![CMTID]IN (SELECT Table2.CMTID FROM Table2);"
0
 
hnasrCommented:
Welcome!
0
All Courses

From novice to tech pro — start learning today.