When we look at commonly used RDBMS solutions in the market, we find the likes of Oracle and SQL Server at one end of the spectrum that are designed for large and medium sized businesses. At the other end we have likes of Ms Access which are primarily suited for small businesses and personal applications. However the Ms Access database application has a lot of juice in it and has over the years been used by many to handle line of business applications and intricate multiuser programs. Now if you are using Access in your company, certain best practices can go a long way in keeping the database in shape.
Perform Regular Compact and Repair Actions
As opposed to enterprise focused RDBMS solutions, Ms Access is not designed to free up space on its own. So you need to periodically perform a compact and repair action to limit the size of the database. This action can go a long way in eliminating missing VBA code or unused forms that can lead to errors. It also allows you to get back the disk space which was left unused when you deleted an Access Recordset or table
Constraints related to Database Size and Simultaneous Access needs to be factored
The Ms Access database can support up to 2GB of data in most cases however from a stability perspective, anything over 1GB would be a cause of concern. Similarly it is claimed that it can support 256 concurrent connections and yet you should never have more than hundred simultaneous users. Now if you are looking to save disk space and not cross the database size limit you should avoid using OLE objects. When you store an OLE object like a word file in Access it ends up taking an inordinate amount of space. Instead always consider using the hyperlink column type to link external data.
When you go about building an Index in Access you need to consider the larger goals. There is no point creating an index for just about every column as they end up consuming extra space and have to be rebuilt over and over again whenever a record is added. More importantly you should clearly avoid creating compound indexes as they are bulky and not suited at all for caching.
Last but not the least invest in a powerful recovery tool
While the best practices listed above can considerably reduce the chances of a database crash and improve overall performance, there is no guarantee that database would always remain in shape. Hence investing in an access repair utility like DataNumen Access Repair can help you remain prepared for contingencies. In case the MDB file, that Access uses to store all data, goes kaput you can still get back your records with the help of this proficient tool. Moreover with its capacity to negotiate files stored in zip archives, flash disks or optical media, you can be assured of getting back your data under nearly every circumstance.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access repair and sql recovery software products. For more information visit www.datanumen.com