Move the records from one table to another

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
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Note: There is no real "Move" in SQL.
You copy first, then delete the source...
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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"
marian68Author Commented:
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
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<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" 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...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.