Solved

Move the records from one table to another

Posted on 2011-02-14
4
307 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now