Solved

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

Posted on 2014-07-23
6
202 Views
Last Modified: 2014-07-24
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.
0
Comment
Question by:ca1358
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40215417
What records are being compared?
0
 

Author Comment

by:ca1358
ID: 40215429
If the field [CMTID] Matches then export out and then del record in Table1.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 40215725
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:ca1358
ID: 40217247
Having trouble with the Delete Statement, Error Syntax error in Delete Statement.  I including a example file.
0
 

Author Closing Comment

by:ca1358
ID: 40217380
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40218277
Welcome!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question