Microsoft Access Database: 5 Settings To Avoid With Microsoft Access Options Causing You Headaches

Microsoft Access Database: 5 Settings To Avoid With Microsoft Access Options Causing You Headaches

Here’s an article I wrote some time ago but is worth a revisit since the newer generation of users tend not to be always aware of the background tasks Access sometimes insists on performing…

When I first started to use Microsoft Access (back in 1993) which was then version 1.1 and quickly moved to the newer version of 2.0, you had various options available that could be set to change the behaviour of how you viewed and ran the basic objects. It probably took you no more than 15 minutes to discover, read (in the help files) and apply all settings that Microsoft Access databases had to offer – job done, never having to return to this area again!

Microsoft Access Database: 5 Settings To Avoid With Microsoft Access Options Causing You Headaches

Move forward 20 plus years and working with Access version 2013 you now have to either employ an individual to study and constantly refine the permutations of how Microsoft Access will work, behave and look. Okay, I may have exaggerated a little about employing and individual but your get the point! You need to spend a lot more time to understand the individual settings in isolation followed by how they are affected when coupled with other settings to get the best or at least the balance of how Microsoft Access database applications will serve you (and your business).

Microsoft Access Database: 5 Settings To Avoid With Microsoft Access Options Causing You Headaches – continued

I personally do not like all the settings that users can choose as I believe they cause performance problems and some instances causes system failures too which are called bugs though the developers at Microsoft may call them ‘characteristics of the system‘.

However, the following options and settings may need to be understood and at best either left alone or perhaps enforced to help handle unknown and performance problems that this application could challenge you with.

I have listed 5 settings for your perusal – you can make the decision. The following are found (using Microsoft Access 2013) via ‘File‘ backstage tab, under the Options command in the backstage area of your application:

Compact on Close – which can be found in the Current Database section when enabled will automatically compact and repair your database when closing the ACCDB file. Personally, do not set it! You can always compact and repair this manually which is better as it will help increase the application’s performance and more importantly you should only run this when in either ‘Exclusive‘ mode or you are the only person using the database file and have taken back-ups first.

AutoIndex on Import/Create – which can be found in the Object Designers section. It is automatically enabled and populated with four keywords; idkeycode and num. Leave this enabled and even add more generic keywords to it that you feel would serve as a unique name identifier. This feature will automatically assign an index key to it which will speed up your reporting namely those queries.

Confirm – which can be found in Advanced section. Never, never switch all three options off; leave them enabled. There are three settings; Record changesDocument deletions and Action queries which are designed to prompt the user before you delete records, objects and run action queries as once executed cannot be reversed.

No locks – found in the Advanced section too and should be left like this as it will allow appending or importing data into various tables more freely and unless there are a large number of concurrent users on the system then this keeps the system free from preventing changes and restricting record and page conflicts.

Set Trusted locations – which is found in the Trust Center Settings (button) is where you can manage and prevent the horrible pop-up banner users see when starting a database file other than from the default location on your pc. You simply train the system by adding locations (paths) with the option of specifying sub-folders included to avoid the disabling of any macro and module based code that is lurking in the database file.

Of course I could add another 5 options that I believe should be left alone and really only controlled via VBA code with the design intent of users being aware of how they wish to control an Access database.

Final point is that I have used Microsoft Access 2013 in this article and the above will also be applicable to earlier versions but located in different places if not slightly worded differently too.

Another Tip for you! Talking of VBA (Visual Basic for Applications), you can really take full control and dynamically set options with various events to help handle performance with functionality making the best of the Microsoft Access application. You call the main keyword Application.SetOption followed by the string value assigned normally to a numeric or boolean value i.e. Application.SetOption “Show Status Bar”, True

Original Article Source:

I would like to pass on some of the eBooks that I have written covering the various and popular topics of Microsoft Access Databases that you may wish to own. There are all very reasonably priced and all come with a 100% money back ‘no questions asked‘ guarantee.

This entry was posted in Microsoft Training, MS Access, Utilities and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.