Access Database growth 1

[jenkin]

I have developed an access business application(Quotations/invoices/management reports/VAT reports) which consists of a single table (102 columns), around 10 reports, 5 input forms, 40 macros and 20 queries. There are 4 update queries which run for every transaction requiring a calculation (Cash input, invoice prep. and so forth.

There are only 412 records in the database which has now grown to 2gig and I am getting an "invalid Argument" message when I try to add a new record. Compact and repair makes little or no difference.

When I split the database both the application and the MDB file are around 2gig. Making an MDE gives me the "invalid argument" message.

I have written the application in access using standard functionality only, such as wizards, macros etc. I am not familiar with visual basic and operate only at a user level.

Exporting the Database to Excel results in a spreadsheet size of 34k.

I tried the Analyze wizard to split the database into two tables but I got the "invalid Argument2 message"

I cannot find any corrupt records on the database

1. What is causing this growth and how can I stop it?
2. how can I reduce the database size?

 

[lupins46]

To solve the immediate problem, create a new empty database and import everything into it from your current database.
(File>GetExternalData>Import).

Unless you are storing pictures and/or other non-Access objects in your database (Word docs/Excel workbooks etc)then it is unlikely that you are really occupying that much space. However, the way Access works is that it never re-uses any space it no longer requires. If you are running lots of queries, especially crosstabs, Access uses temporary space but doesn't release it.
Similarly if you are constantly adding and deleteing records all that Access will do is use more and more space for the additions.
To get round this you need to use the compact option on a regular basis. In my own case, if I believe the data file will not grow to more than say 30mb, I will set the 'Compact On Close' option.
For large files this is more problematic because it might take up to an hour to compact a very large file.

But your export to Excel suggests you should only have a small data file so I would be inclined to set the Compact OnClose in this case.

 

[BNPMike]

Treat what you've got as a prototype. Now start again. There's no way one table is a correct data model for your application.