Export and purge data to an archive database

[Gryphoune]

I have a 2010 database that is approaching 1.5 Gb with approximately 2.7 million records and I need to reduce it's size for network performance reasons. I have would like to export all the data for 2014 to a separate database with all relationships, queries, etc. I have been able to find anything in help and other searches for a way to do this. should I create a new database and do an import for all the objects and then delete all 2015 data? Or is there a way to do an import query for all the 2014 data in the original and delete the 2014 data in the current database? I'm trying to do this in the most efficient way while still maintaining data integrity and usability in both.

Any help would be greatly appreciated.



Thanks,

Jim

[Perceptus]

I have a 2010 database that is approaching 1.5 Gb with approximately 2.7 million records and I need to reduce it's size for network performance reasons.

Does compact and repair help at all? Is it mostly text or are you storing images?

Have you thought about moving the backend to a my/ms/sql solution?

[Gryphoune]

Compact & Repair only reduces it by 100 Mb or so as we're importing additional data every day. I don't know much about SQL or it's variations so I wouldn't have a clue on how to migrate the backend. Also, this is run on a corporate network for just our department so I don't know if we would be able to set up a separate SQL server.

[orange]

Is the data you plan to archive and purge NOT required by your business?
You could put the data for previous years in separate backends (may not be an option)

You could use the free Sql Server Express. Or MySQL as has been suggested.

Linked tables performance

You really need to tell readers the business story for the data. It's origin, use on a typical day, and requirements for access to older data/previous years.

[Gryphoune]

Our company is a timeshare/hotel corporation and we audit all the rental reservations for 47 properties currently. We currently run reports to Excel from our property management system and then format and import them into Access. These reports are two different sets of data that are linked by reservation number, one is guest and reservation information and the other is folio transaction information. We then run queries in Access to produce Excel reports of errors in payments versus calculated stay totals. After correcting errors in the folios we then have to log the errors into a separate table in Access so we produce reports regarding error types and amounts recovered versus amounts lost for the different properties and to visualize trends over different time periods.

We do not have IT permissions and our IT department has stopped supporting Access even though they know we have it and are actively using it. So I would need a solution that doesn't require admin permissions to execute.