Link to home
Start Free TrialLog in
Avatar of Lee R Liddick Jr
Lee R Liddick JrFlag for United States of America

asked on

Archive Records in Access from one DB to another DB

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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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]));
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Lee R Liddick Jr

ASKER

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
I believe I got the rest of this figured out...thanks for your help.