How can disk space be reclaimed following the deletion of tables and forms

[JAH]

I developed an Access DB to keep track of activity at a local animal shelter. It imports spreadsheet data on the dogs coming into our shelter as well as the applicants who adopt them. Unfortunately, after about 4 years the database exceeded the 2GB limit. My first step was to use the existing tools to compress the file which did nothing to reduce the size. I then deleted several tables, forms, and reports, opened a new database and imports the remaining components, and compressed again; still no reduction in disk space utilization. My question is, how can I get the database down to a useable size?

[CJ_London]

1 you should split your database - tables in the backend (BE) and forms, queries, reports and modules in the front end (FE) - see the 'Access Database' splitter on the database tools tab

2 next you need to decompile the new front end - see this link on how to do this -

http://www.access-programmers.co.uk/...ight=decompile

post #4 is probably the easiest. The front end will now be as small as it can be and should not change in size (unless you have temporary tables - see below)

3. now compact the back end - again it will be as small as it can be

4. if it is still around the 2gb limit, you need to divide the BE into two or more databases. Base which tables go to which database on common relationships (for example customers, addresses, contacts in one table, invoice header and invoice detail in another).

All of the above does not require any code, just rearranging what you already have.

For regularly incrementing data (such as invoices) you may need to consider creating an archiving process which will no doubt require some code - google 'access archiving' or similar to find a solution to meet your needs

If you use temporary tables, these are better in a separate temporary database. You may need some code to create and delete the database as required - google 'access vba create database' to find suggested code

finally you should compact and repair the BE on a regular basis - frequency depends on how often the BE is updated but would suggest a minimum of once a month.

I note you are using Access 2007 - if a db reaches 2gb and fails, compact will work for a .accdb but not for a .mdb. Personally I would upgrade a .mdb to a .accdb, but if it has already failed, you will need to create a new db of either flavour, then import the tables

Finally if all your data totals 2gb of space and you don't want to have multiple backends and don't want to archive, consider migrating the back end to sql server express which is free and has a 10gb limit (on your current growth that should give you about another 16 years). this may well require changes to code but in the first instance you can use the SQL Server upsizing wizard on the database tools tab. Again, google 'upsizing access to sql server' or similar to find out what issues you may face and suggested solutions/workarounds. Note that multivalue, calculated and attachment type fields cannot be upsized.

[June7]

Deleting objects and data then running Compact & Repair should result in some reduction in file size - but maybe there just wasn't enough to be noticeable.

I put temp tables in frontend - the tables are actually permanent, their data is temporary.

[JAH]

Ajax,

Thank you for both your input and speed of response. I have to go out of town for a week but will try your suggestions as soon as I get back and let you know how it works out.

Jim

[JAH]

Ajax,

Thank you very much. Your recommendation worked like a charm and the split database is working as originally intended. One thing I had to do was delete all of the temporary tables before the split function could work since those tables dad no keys.

Thanks again.

Jim