Copy data from one access mdb to another

[JoshuaL]

First, let me start by saying any replies are greatly appreciated.

Okay...Here's the situation. I have two .mdb files that are identical in structure and i'm looking for the best way to programmatically copy, or move, some, or all, of the data from one .mdb to the other. What I mean by "some" of the data is that I want to allow the user to choose which records are moved based on the date they were created. Records older than a certain date will be moved and newer ones will stay.


Thanks again for any help,
Josh

 

[hymn]

Why two db's?
have you thought about just adding a table/tables to your existing db that just store the old data that at present appears not to be required.
ihave a button that opens an identical form attached to the added tableand use the code below

Private Sub Command39_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 1, , acMenuVer70
DoCmd.OpenForm "frmDeletedPerson", , , acNew
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "frmDeletedPerson"
End Sub

Hope this helps
Hymn

 

[JoshuaL]

Thanks for the reply.
Creating new tables wont really do the trick. This is to be used as an archiving tool. Moving the records to another db will allow the user to reduce the size of the db and provide an easy way to store the data in another location.

 

[jsolutions]

Joshua - since this will be interactive anyway (meaning the user will input the date), you could simply use two queries : an append query and a delete query. Please note that yes, there are better ways to do this using SQL directly, but I am thinking you probably want the path of least resistence.

First create a paramenter query that asks the user to enter a date - make sure that it is pulling the data correctly. Then from the Query menu, change this to an append query. The append query will ask you where you want the results to go. You can select a table in another database (as you were requesting). This will then "copy" the data from the original table to the target table.

You data still remains in the original table. Therefore, you need a delete query. This will be the same structure as the Append query, the difference is that it will now delete the selected records from the original table.

To make this solution a little more eloquent, you will probably want to create a form with a text field to allow the user to enter the date. Then they will not have to reenter it for the second query. Have each of the queries read the date parameter from the form instead of prompting the user for input.

Finally, you will want to create a button that when clicked will run both queries. You can do this with DoCmd.Openquery.

Final note - you might want to specifically tie this ability to a user (in other words, test to see who the user is before executing the code) since this will essentially delete information from the table.

Hope this steers you in the right direction.

Jay