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.
Current database Options
Wow that are a lot of options to choose from. Which ones are good and more important what does it mean when I disable an option? Let’s have a closer look.
- 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.
The Access Webdatabase functionality came available with the release of Office 2010. Wow finally we can create databases for the web was my initial thought. It did not take long to realize that this was a wrong assumption. One of the prerequisites was SharePoint 2010 – Access Services. Hmm i did not have SharePoint on my pc so i was not able to build my first web database. Sure there were third parties offering hosted environments to create Access Webdatabases but i registered to late to take advantage of their free offer. After a certain period you had to pay for their services. I asked my company if it was possible to use some space in our SharePoint environment to play around with Access Services. They agreed and i was able to build my first webdatabase.
A new learning curve
I soon discovered that creating webdatabases is a whole new experience. The embedded macro, the macro editor, the switchboard everything i almost never used in my Access development became important. What? Why would you want to do it like that when you can do it like this… everytime this question popped up in my mind when creating my database. During that process i forgot one major part and that was Access Services (on SharePoint 2010). It was not that Access could not do it the way i wanted it but it was SharePoint restricting me constantly. I decided to start all over again and tried to find out what could be done instead of what i would like. First steps: create a table and record validation. In this blogpost i will write about creating a table for a webdatabase. In future post i will write about the process of creating a simple webdatabase for SharePoint. So if you have the possibility join me in this adventure of creating a table for a webdatabase to publish later.
Websclice in a webcontrol
In this post i’ll show you how to add a webslice to a webcontrol in Access 2010. I got the idea watching the video from Albert Kallal. He created a webdatabase called “Room Bookers”. In this database Albert used various techniques to try out the new web database technology added in the Access 2010 version.
Now one of the features I liked was the fact that he imlpemented a webcontrol showing the weather in a certain place. That’s what I wanted to try. The webcontrol is a control you can add to your form in Access. You can use it (among other things) to show internetpages or webslicers. Webslicers are designated pieces which are a part of a website. In the weather part I just wanted a piece of the website to use in my Access form. So here we go.
Add the webcontrol to the form
First create an empty db with one form. On this form add a webcontrol which you can find in the ribbon under controls. After you click the webcontrol on the form you’ll be prompted to enter the hyperlink or webaddress. In this case I added the webaddress from Bing. The webcontrol got filled with the complete website and that’s not what I wanted. I just wanted the little piece showing me the weather. So how did Albert do this? He used a webslice instead of a whole website. Ok so it’s of to create a webslice.
Access is one of those applications used by millions of people to create databases. A great application with maximum flexibility. I have met a lot of people who develop databases in Access. Looking at their work I discovered that a lot of people do not realize that there are a lot of unnecessary indexes on their tables. If I ask them about that they often tell me that they did not put that specific index on that specific field. So I tell them about the Access feature that does that for them without them knowing it. Let me explain.
If you create a table in Access (design view is best to see it happen) you add a fieldname and set a datatype. If you look at the field properties at the bottom of the screen you can see if that field will be indexed or not. Now here’s the catch. If your fieldname ends on any of the following letters Access will autmatically add an index to that field. [ID], [Code], [Num], [Key] This can vary depending on the language you are using.
Let’s take a look at the following scenario:
We design a table with just one field something stupid. So we go to table design and add the first field [ID] set to autonumber. We add another field called [Stupid] and set this to a Yes/No data type. If you do that you will see that Access actually adds an index to the [Stupid] field because the name of this field ends on [ID]. If you don’t know that Access will do this then you will end up with a lot of unnecessary indexes in your table.
You can try this yourself (my office is set to English). Add the following fields to the table in design: Liquid – Yes/No datatype, Fluid – Yes/No datatype, PrivatePostalCode – Text datatype. You will see that you have actually got three fields with indexes without you setting them.
How to avoid this?
Ok you made your point so how can we avoid this? The first remedy would of course be think about your fieldnames carefully. If you do need to add fields which end with the designated letters then go to the Access options screen and disable or remove the options there.
- Open Access
- File – Options – Object Designers (view settings in the picture) and change or remove accordingly
In 2007 you will go to Office button – Access Options – Object Designers. In Earlier versions go to “Tools – Options – Table design”.
So concluding: if you don’t want any indexes you did not set check your tables after you are done designing them. Remove any unwanted indexes or change your settings in the Options settings.
Happy programming 🙂