Solved

Move the records from one table to another

Posted on 2011-02-14
4
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

729 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