Optimize an Access Application size

[Nizar]

Hello everyone,

I've just finished my access application, but its size is 4 Mb. I expect that it will be much bigger once users begin to add records.

What is the best way in Access to keep the size of the file as small as possible?

Many thanks in advance,
Regards,

 

[jdraw]

Nizar,

Open the database and do a Compact and Repair. This will get rid of most of the "bloat".
Check the size then. See if it's still 4Mb.
Good luck.

 

[Nizar]

Thanks Jdraw for your help,

I've check it and it really reduces the size of the db. But is it sufficient to garantee the good work of the db when it will have the size of more then 1Gb for example?

Best Regards,

 

[jdraw]

No it won't guarantee good performance automatically. A lot of "bloat" occurs when developing -- trying something multiple times, removing some feature, deleting tables etc. . But, since Access manages its own space, you must do a compact now and then to remove these unnecessary "space users".

If your data base will only be used by others to add records, then you can probably run for a long time with good performance. Again it depends on how many records of what size are added.

You can force the database to do a compact and repair every time it closes -- BUT very few people would recommend this. It can be a slow process.

jdraw

 

[padlocked17]

Note: Access stores only the characters that are entered in the field. Access does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered in the field, set the Field Size property to the value that you want.

Numeric fields are not the same however, from what I understand.

 

[padlocked17]

You can also refer to field storage size here:

http://support.microsoft.com/kb/824263

 

[ChrisO]

G’day Nizar.

The way you are referring to the database indicates it is not split.

Best to split it…do a search of this site for splitting a database.

Regards,
Chris.

 

[Nizar]

Good day for all of you,

Thanks every one for his precisous help. I think that repairing and compacting the data base is helpful. However to ensure typical performance for the Access application there is another idea: auto-backup. It garanties security and size optimization. I am searching some solutions with Vba if they exist.

Many thanks again and HAPPY NEW YEAR FOR ALL OF YOU!

Best Regards,

 

[boblarson]

If you are going to have multiple users on a network using the database you MUST have it split, as Chris suggested, or you run a huge risk of corrupting and losing your database.

The split database should be a backend (tables only) on a file server and the frontend should be a copy on EACH user's computer. If you don't want to have a hassle over updating each user's copy of the frontend then look into using my (or anyone else's) autoupdater.

You can find mine here:
http://www.btabdevelopment.com/main/AccessTools/tabid/78/Default.aspx