Microsoft Access is a great application for creating databases. It is also used as a tool for just storing data. Where Excel will limit you to just over a million rows this application has no issues whatsoever to store a couple of million rows. In that state Access just serves as a backend. I enjoy seeing people create databases in Microsoft Access. From small community databases to extensive company databases. It is usually the application to start with before taking the next step to for example SQL Server.
When done creating your database it is good practice to split your database. You will get a front end and a back-end. The front end contains the forms, queries, reports, macros and modules. The back-end contains the tables. The front end contains links to the tables of the backend. Now when multiple users need to use your database you provide them with a copy of your front end. You must also remember that users need to have a copy of Microsoft Access installed on their computer. In all other cases you either have to create an App or create a runtime version. What you need to know about the client application is that 9 out of 10 times this is installed via the IT Department. Users don’t know or don’t care about the environment they are going to use your database in. They should but that’s where your advice comes into play.
When end users develop databases they often use the environment that is presented to them. As I told you Access is a great application but also has a lot of settings that can be turned of when your database is ready to be used. So let’s have a look at the settings when you start creating a database. For this example I am using Access 2013 but it also applies to previous versions. Remember we are talking about client settings for a single user here.
- Use Access Special Keys
This option gives the option to enter the VBA screen via the ALT-F11 keys combination. It also provides the option to show the navigation pane when pressing F11. So if you think users should not be able to do so, uncheck this option.
- Compact on Close
This must ne the favorite option for a lot of people. My advice would be to compact and repair your database manually and not by setting this option to true. When working with multiple users Access will try to compact the database when someone closes his front end. When other users are still using the shared backend Access stops the compacting process because that would conflict with the current usage of the database. This option causes quite some corruption to the various databases. So uncheck! There is a button for it and use that when you know no one is using the database.
- Use windows themed controls on forms? That’s a personal choice. If checked Access will always check first to see if the used theme is present (takes time!). My advice… uncheck!
- Enable layout view. Great if you are developing databases. When done uncheck the end-user only needs the final view and not the layout view! So uncheck.
- Enable design changes for tables in Datasheet View. It surprises me that this option is actually there when end users are working with a finished database. Of course you have to uncheck this one. Nobody makes changes to the database but the developer.
Well that’s the first step. Now for the next step. In this paragraph i will point out the various options you might ‘uncheck’. See if you can find them in the Access – Options dialog.
- Auto index on Import/Create
This is a notorious option in Access. I wrote a special post on this which you can find here. It’s best to clear all the options you find there. Indexing should be done manually but that’s my opinion.
- Enable Error Checking. This is a great option while you are developing the database. But when the database is done uncheck this option on the client setting.
I never use proofing in a database environment. But this could be a good option for the end-user. So your choice here.
- Client settings
– Display options. Check this one very carefully. Show animations is not one essential part of the database. So uncheck. Show Action Tags… (). This option offers additional choices when user works in a form. So if desired leave it on otherwise uncheck. (Add to contacts, show map for address etc).
- Name Auto Correct options
This is also a famous option in Access. Wow this changes all my field changes in my queries and forms if I made changes to my table design. Yes it does and the big issue here is that it does not change your VBA added functionality. Furthermore a background process has to check this every time just to make sure no table design changes have been made. So for the developer yes keep it checked. Once finished please turn this off. You will see a significant change in performance when Access does not have to check this.
Options, options and more options
Microsoft Office applications are great. One downside can be the gazillion settings that are available to the end users. For databases all these options can cause performance issues. So when deploying you database make sure the client settings for your end users are optimized for working with your database. Help them… this is a one time investment which will save a lot of issues later on.