Steps to remove certain date range of records in Access

[RAC1978]

I have a database with about 10 years of records.  We only need two years of history in the current database. How can I move the older data but keep in case we need to review?

Roberta

[#2]

How many records are we talking about?
If less than 100K, I would not bother. Access is fast enough. Just make sure you have good (duplicate) indexes to assist with looking up data.
Note that you cannot "remove" and "keep" the data at the same time 🙂
If you insist, what you can do is archive the current version of the FE and BE in a folder on the server, just in case you need it in future, and then delete the old data. I insist on archiving the FE as well, as it may evolve over time and become incompatible with today's BE.
To delete data, use a Delete query.

[#3]

There are a couple of ways.

1) you can create a duplicate archive table and move data to the archive table. This makes query fast on the current tables.
This has served perfectly if the database is not too big. Similarly, same can be achieved creating another database and appending data on archive database.

[#4]

Around 56k. The database opens slowly and the tables open slowly.

[#5]

@RAC1978 

Can you quantify "slowly"? Seconds? Dozens of seconds?

What operations, specifically, are slow? Simply opening the accdb and waiting for the first form to settle?

What is the design pattern for opening forms? I.e. are forms bound to unfiltered queries, or even whole tables?

What database engine is used for the back end, accdb? SharePoint lists? SQL Server? Something else?

It's not uncommon for certain design patterns that work okay initially, on small recordsets, to be become increasingly sluggish as the number of records grows. Unfortunate design choices begin to make their impact known over time.

Trust me, I am well aware of that problem. This isn't on the same development platform, but I recently found myself relearning this important principle, even though I've been doing database work for more than 20 years. 

The more details you can share about the design of the database application, the more likely someone will have direct suggestions to share. At the end of the process, you may still want to archive older data, but that should be a last resort.

[#6]

The database was created many years ago by a former team member who really did not know a lot about relationships or design choices. I could rebuild it, but time is not something I have to do so.

The database takes about 20 seconds to open and land on the Menu. When I click on Relationships Design I see 3 tables, one looks blank because it is link to an Excel spreadsheet for its data.

This database allows our Phone QA person to enter scores when observing phone calls for our Customer Service teams. I believe there are too many tables, queries, reports, etc. that does not mean much, little or improper relationship. She should enter call score data, ensure calculation is returned correctly (this took weeks for me to figure out when scores weren't correctly being outputted).

I consider myself a Novice still after all these years. I need to learn how to create formulas and maybe slowing recreate this tool for her.

Thank you for your insight.

[#7]

@RAC1978 

Okay, thanks. 

20 seconds is SLLOOOOOWWWW. Time to refactor/redesign.

Archiving might be an interim, delaying tactic to put that off while you work on redesign.

My first target would be that linked Excel spreadsheet. Probably that is not the most efficient part of the application. How many records in it? How is it created? How is it used? Can it be replaced by an Access table by importing the data from it?

When you open the relationships window, try clicking on "All Relationships". Does that show any additional tables beyond the first three you see?

Can you manually add the other tables? Then take a screenshot for us. That might help in offering ideas on ways to improve it. Might not, but it could help a lot.

[#8]

When you work with Relationships window, also have Navigation Pane open, and drag and drop additional tables that may not show up at first (because they are not related to any others).
A good relationships window should look like this one for Northwind template:
https://support.content.office.net/en-us/media/559a04f2-11b2-44b8-ae4a-92284d1576bd.png

[#9]

I dragged the other tables in the Relationship window; however, there are no relationships.
I hate that I cannot attach screen shot.

[#10]

@RAC1978 

The lack of designated relationships is predictable, given your description earlier. We can work on that.

You should be able to add screenshots in a couple of ways, but you have to elect to reply this way on the first screen.

..

[#11]

@Skandel590 I will look into the steps of getting this done.  I don't want to mess anything up..

I think you are saying to archive the table in the current database, then delete the unwanted records from the current database?

[#12]

What does FE and BE stands for?

[#13]

@RAC1978,

Unless I know the database structure I can not suggest much.

The solution I was suggesting was If your Database resides on server and your end users are MS access app with Linked table, below are option.

1) Create a duplicate table with different name, Run an append query to append data from the production table to the newly created archive table. Once data appended, you can delete the old records from the production table. However if the tables are relationships are not designed properly, that may not help. but you can try without messing.

2) Instead of keeping Database on Access, you can migrate to MSSQL (Of course express version which does not cost and free upto 10 GB Database) link the tables in Ms Access which can improve the performance.

If you can PM me the database relationship, How many tables, How many queries run on main form I may be able to assist.

[#14]

The Front End and the Back End of a split database.