Archive Records in Access from one DB to another DB

Lee R Liddick Jr
Lee R Liddick Jr used Ask the Experts™
on
I have a database in Access (DB1) with a table (TABLE1) that holds data that continuously gets added upon.  There is an issue when opening TABLE1 that the length of time is getting longer and longer (yes I already know if this is the problem, move it out of Access and into DB2 or SQL or something, etc. but I do not have that option at this time).

I have created a form in DB1 that basically has two inputs, one for a beginning date and one for an ending date that the end user can choose in order to move older data out of TABLE1 into DB2 (the archived database) into TABLE2 (holding just the old archived data from the main database.  The user then clicks a button and I need to move the records (so basically copy the records) within the date range the end user entered to TABLE2 in DB2 and then delete these old records in TABLE1 of DB1.

My problme is that I am not sure how to write the macro or whatever to make this happen.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
create a  query like this

INSERT INTO Table2X ( DateField ) IN 'C:\FolderName\DB2.mdb'
SELECT Table1.*, Table1.DateField
FROM Table1
WHERE (((Table1.DateField)>=[Forms]![NameOfForm]![txtStartDate]) AND ((Table1.DateField)<=[Forms]![NameOfForm]![txtEndDate]));
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can use SELECT INTO with the IN clause to move data into an external database:

SELECT * INTO YourExternalTable IN [Full path to the external db] FROM YourLocalTable WHERE DateField<#05/22/2012#

You could then DELETE the records in YourLocalTable

DELETE * FROM YourLocalTable WHERE YourDateField<#05/22/2012#

Obviously this is a permanent, non-reversible operation, so it would be prudent to make backups before doing this operation.

More info on SELECT INTO and the IN keyword here:

http://www.w3schools.com/sql/sql_select_into.asp
http://blogs.office.com/b/microsoft-access/archive/2009/03/27/accessing-external-data-using-the-in-clause.aspx
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Note that mine (SELECT INTO) will create the table, and therefore that table cannot already exist in the destination db. cap's moves data into an existing table. Each has it's own merits, of course, just wanted you to be aware of the basic difference.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Lee R Liddick JrReporting Analyst

Author

Commented:
Cap...I'm getting a Duplicate output destination 'EntryDate' popup error when I try and run the query.

INSERT INTO mytable ( EntryDate ) IN 'C:\MYDATA\MYARCHIVE.accdb'
SELECT mytable.*, Cred.EntryDate
FROM mytable WHERE (((mytable.EntryDate)>=[Forms]![Archive]![txtStartDate]) And ((mytable.EntryDate)<=[Forms]![Archive]![txtEndDate]));

Not sure what I am missing here.

LSM...I'm wanting to export the archived data into a specific table already established in the archived database.
Top Expert 2016
Commented:
oops sorry

test this one

INSERT INTO  [NameOfDestinationtable] IN 'C:\MYDATA\MYARCHIVE.accdb'
SELECT mytable.*
FROM mytable WHERE (((mytable.EntryDate)>=[Forms]![Archive]![txtStartDate]) And ((mytable.EntryDate)<=[Forms]![Archive]![txtEndDate]));


change   [NameOfDestinationtable] with actual name of table
Lee R Liddick JrReporting Analyst

Author

Commented:
Okay CAP, that worked perfectly.  Now I just create a delete query and add it to my button function?  So I remove the recently moved data out of the main database table?

Also, is there a way to make sure the DELETE doesn't run if there was something wrong with the Archive?
Lee R Liddick JrReporting Analyst

Author

Commented:
I believe I got the rest of this figured out...thanks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial