We help IT Professionals succeed at work.

Microsoft Access MDE database - Purging data

holemania
holemania asked
on
We have an old access database that is an MDE file.  The company that created that for us went out of business so we can't contact to get support.  The access database has grown to a size that is causing performance issue and we need to purge some data out.  What is the best way to handle this?  We can go and delete old records one at a time, but that's a lot of manual work.  Wondering if anyone has any input on how to best handle this or a method to purge old data out.
Comment
Watch Question

1. Delete data by sorting: Sory by a date range (eg: defined by old) and then delete it.

2. Compact & Repair: http://office.microsoft.com/en-us/access-help/compact-and-repair-an-access-file-HP005187449.aspx

This deletes junk from your database (eg: when you delete a record, its marked for delete but never completely deleted. This bloats the Access file size. Only way to remove this is by doing a compact and repair)
First of all, have you tried a compact and repair?  When you delete a record in Access, they aren't really gone until you perform this step.  If the database has been around for a while, this first step may reduce the size quite a bit and you may see some improvements.

http://office.microsoft.com/en-us/access-help/compact-and-repair-an-access-file-HP005187449.aspx
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:

  You can use that DB or another DB and link to the table) and execute queries to delete the data you no longer required.

  Be aware of two things however:

1. If they established relationships, they may have turned on cascading deletes.  This will delete records in related tables if you delete records in a parent table.  So you can end up getting rid of more data then you expect this way.  You can see this in the relationships window.  Do a show all relationships, then look for any lines between the tables

2. If relationships are not enforced, then you need to make sure you fully understand the database design if you delete data outside of the functions offered in the program.

  Once you've deleted things, then do a compact and repair to reclaim the delete space as the others have said.

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 And I'd do a C&R first.  You may find performance back to an acceptable level without deleting data.  C&R not only reclaims deleted space, but also updates table statistics and tells every query to re-cost it's execution plan based on those new statistics.

Jim.