Welcome to a brand new year – 2016 which continues with regular blog posts – all about Microsoft Access databases.
So, with the recent release of Microsoft Access 2016, we now have parity and I thought as a recap (as with earlier versions of the past), we may as well start with Access 2016 specifications and get to know your limits (if any)!
This information about the limits of Microsoft Access database files and objects can be found from the official Microsoft support website.
Any database can exceed they limits which can cause performance issues and become corrupt overtime which is why there are tools to help manage such files and objects. However, this is a last resort and some prevention is required by database administrators to ensure longevity and good general housekeeping; forming good habits.
Microsoft Access 2016 Database Specifications
This information will help with the careful examination of your database design and confirm if your database has been well designed from the outset. For example, importing data directly from Microsoft Excel into Access without good normalisation can result in having more and unnecessary fields (columns).
Using the link above and study the specification will also reduce the ‘re-inventing of the wheel‘ syndrome and instead utilise the capacity of the standard Access application.
In some instances, even being within the remit and specification may still be moot and redundant. For example, saving a password for a database is firstly limited to 14 characters but the level of security is very subjective and not perhaps secure enough for your database and nay wish to deploy your own and stronger security. You may wish to look into my very own Login User, Workgroup & Access Rights Utility. Therefore, you can now customise and store more than the standard 14 characters if so desired.
The number of concurrent users always amuses me as it clearly states a maximum of 255! Well, if you ever get half way to this value, please email me – I would love to know how you did this. Microsoft Access was never designed to be truly a client-server database but it does allow certain techniques and tools to provide and emulate multiple users and instances and in the past, and with the right database design getting 20 concurrent users is manageable. Anymore, perhaps a different and stronger application maybe in order.
One of the most important collection of objects as well as learning to save memory and optimise your database properly lies within the fields and their data types. It’s important to modify (where applicable) field sizes and choose the correct data types and save memory as Access will abuse this if left to poor design implementation. A fixed length memo (or long text) field can store approximately 65,000 characters as opposed to a maximum of 255 for a standard (short) text field. If you use VBA code, the longer text field is driven by memory of 1GB as its maximum. Keeping the field count in a table as low as possible will also improve the performance of the database though you can reach 255 for a table or query.
So, get a handle on the specification of Microsoft Access 2016 and know the limits but in reality, if you run out of room, have a rethink on how you are actually using Access in the first place.
Happy New Year to all 🙂