• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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
0
marian68
Asked:
marian68
  • 2
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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"
0
 
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
0
 
Jeffrey CoachmanCommented:
<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
 
Jeffrey CoachmanCommented:
Note: There is no real "Move" in SQL.
You copy first, then delete the source...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now