Simple Database over 500Mb in size, why?

[meadt]

Hi Guys,

The Problem: I have a database that I’ve been working on for the last few months that I have realised this morning has grown to a file size of 556Mb! It’s a database that allows a user to import txt files (of about an average of 6k records) into it and then run several vba functions on it. As a result tables are constantly created and deleted, especially through testing it. Is it possible that these tables haven’t really been deleted and still exist in some form, even though they don’t appear on the objects window? Or could it be something else?

Thanks in advance for any help,

 

[gemma-the-husky]

a) try compact and repair - see what happens

b) embeded images produce enormous bloat - could that be it?

 

[meadt]

Hi,

Thanks for your suggestions. The compact and repair freed up 10Mb but that still only brings it down to 545Mb. There are no embedded images in the database.

 

[c_smithwick]

It is not a good idea to constantly create and delete tables - it creates a great deal of bloat. Try a different approach - perhaps restructuring just one table (through VBA) based on field types and names you need for your text import (hence reuse just one table).

 

[ghudson]

Try importing all of your db objects into a new database.

I prefer to use one table per import, I delete the table records first, then import the new data. Then I append those records to their destination table to add new records, update the records if they already exist to add any new changes if neccesssary. Modify to meet your needs.

 

[gemma-the-husky]

but if compact and repair is not freeing space - then maybe you really ARE using the space. How big are your tables in terms of record numbers.

out of interest, a large DBS I support has several very large tables - the largest two are very wide (lots of columns), both with about 200K records each, and is about 300Mb now

 

[meadt]

Hi guys,
Thanks for your suggestions, I'll try them out. Ive moved all the objects over to a new database and it is only using 400k. My largest table is no more than 10k records with only 5 fields!

Is there an SQL command to delete all records in a table (i've only been using access for a few months, sorry if this is a simple question)

 

[vbaInet]

When records are entered do you trim before saving? It could be an to write a function that will trim all the data. Tables are also created by Access for import and export errors.

 

[Galaxiom]

Importing text to tables should not be done by creating new tables. Eventually the numbering runs out and the objects have to be exported to a new database to continue.

Better to substitute the input text files to a fixed location linked to the table in Access. Don't create and delete any temporary tables used for processing. Clear the existing records and append the new records.

Also do the temporary tables in a separate local database so the constant changes in the records don't cause bloat in the front end. The Side End database can be recreated as required by the Front End.

 

[ghudson]

Hi guys,

Is there an SQL command to delete all records in a table (i've only been using access for a few months, sorry if this is a simple question)

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

or

CurrentDb().Execute "DELETE * FROM [Your Table Name Here]"