Access database size limits?

[chris990]

Hey guys,
Was wondering, what is the recommended maximum size for Access databases? I've heard that for very large databases that oracle is preferred. Would Access be appropriate for something on the scope of 5000 records of roughly 6 text and 2 number fields? Do people ever notice performance effects on databases of that size?

Also are there any ways to help reduce the size of the database? Do you recommend change the default field sizes where appropriate and would this have a noticeable effect on a database of this size? Any other suggestions to help keep the database manageable, and functioning?

I'm basically trying to figure out if there's much to worry about here, before I start importing my data. Thanks.

 

[freakazeud]

Hi,
the max file size of an mdb file is currently 2GB. It is smaller in earlier versions, so it depends what your version is.
5000 records is not much at all and access can handle this easily. It all depends on what you do with the records though. Do NOT store images or other documents in a db as this caused massive bloat. The images should be stored outside the db and then you can store the filepath of the images/documents in the table as a string. Then you can use the string at runtime to display what you want.
To keep everything organized and the file size down you can compact + repair every ones in a while. You find the option under tools--database utilities-compact and repair...! You can also set to compact on close, which will compact the db every time it is closed. You find the option under tools--options--general tab.
Additionally it also depends on how well the data is normalized. The better the structure the better the performance and reliability.
You will be able to store million of records if you do it correct. Furthermore the number of users and network speed are factors as well. Access can handle up to 255 cuncurrent users, but the performance goes way down. You might want to look in alternatives like SQL Server or MySQL if you need more.
HTH
Good luck

 

[chris990]

Cool....My database is at 3MB already and I hadn't even put any data into it, other than some test data, so I was starting to get a little concerned. I'm not anticipating multi-users, but the stockpiling of data could add up, though it sounds like it should be ok.

Thanks for the tip on the images, I'll give that a try as I was planning to integrate some company logos.

Another thing I just thought about, would there be any limit on lookup menus? I've put a bunch of them in, so that data is entered correctly.....will they be able to accomodate 300-400 products if neccessary?

Thanks,
Chris

 

[freakazeud]

Hi,
do you mean comboboxes in forms? I hope you don't mean lookup fields in the table level. You should avoid these. Instead you should create a lookup table (holding all values) and then create a combobox on your form which is based on that table. YES they can hold up to 64k records. It might get a little complicated for the user at some point since they need to scroll through so many records, but you can find a solution for that here: http://allenbrowne.com/ser-32.html
To learn more about the picture issue you can take a look here:
http://www.databasedev.co.uk/bound_image_form.html
and
http://www.databasedev.co.uk/bound_image_report.html
HTH
Good luck