holemania
asked on
Microsoft Access MDE database - Purging data
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.
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
http://office.microsoft.com/en-us/access-help/compact-and-repair-an-access-file-HP005187449.aspx
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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)