Solved

Move the records from one table to another

Posted on 2011-02-14
4
308 Views
Last Modified: 2013-11-28
Who can give me the VBA code for moving (cut and paste) all the records from one table to another table, the 2 tables being placed in different databases.
Thank you
0
Comment
Question by:marian68
  • 2
4 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 83 total points
ID: 34889554
You can use SELECT INTO, if the destination table doesn't already exist:

Currentproject.Connection.Execute "SELECT * INTO SomeTable IN 'c:\somefolder\somedatabase' FROM YourSourceTable"

If the table does exist, use INSERT INTO:

Currentproject.Connection.Execute "INSERT INTO YourDestinationTable IN 'c:\somefolder\somedatabase' SELECT * INTO FROM YourSourceTable"
0
 

Author Comment

by:marian68
ID: 34896982
Thank you for your reply and sorry for my delay.
The VBA code you gave me copy and paste the records. Please I need the code for moving the records "cut and paste".
Also if I tried to use this code to transfer the records from a database with a password and it didn't work.
Thank you
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 34897723
<Points to LSM if this is what you wanted>
(Just trying to help out...)
;-)


Then delete the records after the copy, ...something like this perhaps...
Currentproject.Connection.Execute "DELETE * FROM SomeTable"

...to be sure, ...the first few times you run this, make sure the records are, in fact, copied first.
The last thing you want is to find out that you deleted the records and the copy failed for some reason....
(Destination DB is moved or renamed, ... for example)
...and perhaps add some error handling...
;-)

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 34897745
Note: There is no real "Move" in SQL.
You copy first, then delete the source...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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