Avatar of Lee R Liddick Jr
Lee R Liddick Jr
Flag 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.
Microsoft AccessVisual Basic.NET

Avatar of undefined
Last Comment
Lee R Liddick Jr

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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 )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott McDaniel (EE MVE )

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.
Lee R Liddick Jr

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lee R Liddick Jr

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 Jr

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