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.
Create web database
Ok, the first thing we have to do is create a new web database. So open Access goto Backstage and click new. Instead of choosing database make sure you select webdatabase. I’ll write a post about hybrid databases as well in that case you can choose database. For this sample choose blank web database (the one with the globe). This can be an easy reminder for all your objects, when you see a globe it can be used for webdatabases.So there is a distinction between client and web objects made visible by the globe.
Where is “Design view”
The first thing you will notice when creating webdatabases is the different layout in the ribbon (see image below…). Right from the start you will see the tables tab active with the contextual tab present. The first tab – fields – is active. This tab shows a totally different layout in comparison to the regular database table layout. What struck me the most was the absence of the “Design View” option for table design. Now how am i supposed to define the fields?
Easy just click the little arrow in the table where it says “Click to Add“. This will present a dropdown where you can define the datatype for your field. So there is no design view. The next important point is that your table must have a key field of datatype autonumber – a SharePoint thing. Don’t worry Access will create one for you. You can rename the fieldname no issues there.
Create a Sample table
For this post we will create a sample table consisting of a couple of fields. Nothing fancy but enough to explain. So we will create the following fields: StartDate, EndDate, FirstName, LastName, FullName and Task. We start of by clicking on the arrow next to the “Click to Add” option to see what happens.
You will notice that the dropdown consists of the various datatype options. So for the first field we will select a date type. The first field will be the StartDate. When finished i will add some other fields the same way. For the taskfield i want a drop down so the user can choose an option from the list. To do so choose the “Lookup & Relationship” datatype and follow the instructions in the dialog(s) that follow.
A special field
Now i’m going to show you a field that had a lot of eyebrows raised.The calculated field. In this field i am going to concatenate the first name and the last name to a full name. Yes i know what you are thinking and i thought the same. You don’t do this in a table, again every database developer will tell you that you don’t put calculations or any other kind of concatenation in a table. Clear, there a lots of discussion about this but in the end remember this. We are publishing to a SharePoint website and you are restricted as it is to create databases there. This is not the last feature to raise your eyebrows. So in regular db’s, follow the rules, in webdatabases bend them just al little without worrying to much about it.
So to concatenate the first and the last name i click to add another field and choose “Calculated Fields”. In the next dialog i concate the first and the lastname giving me a fullname in the field Fullname. The final part is to name your field “fullname”. One of the great advantages in Access 2010 is the presence of intellisense. When we type the first letters of our field intellisense will show the available fields to choose from.
Updating Calculated fields
One might wonder how the updating is taken care of. From what i’ve learned this is only incremental. This means that the calculated fields will only be updated when changes are made in the source fields. So that means that there must be some internal object which monitors the changes made at recordlevel otherwise the whole table would be re-calculated everytime you open it. For now i took their word on it 😉
Another interesting option is the record validation. Normally one would do this at the form level. Checking if everything was in place and correctly filled. Sure you could check the fields in the after update or before update but now we can do this at the table level. Let us see how this is accomplished.
When all the fields are defined we click on the button “Validation” in the table tools tab in the ribbon. You can choose either field or record validation. We will choose record validation.
When chosen you will be presented with the familiar dialog. For our sample we want the following checks; Start date must be before End date and the End date must be after the Start date. So the filled in dialog would look something like this:
Make sure you also insert a record validation message which can also be found under “Validation”. When the user makes an incorrect date entry he or she should be notified of their mistake. This could look something like this:
When creating ‘pure’ sharepoint webdatabases you have to take a lot if things into consideration. What can and cannot be done in table design. Well first of all you don’t have a designview so that’s a new one. You have to think broader and not database rule specific because sharepoint doesn’t let you. So yes we denormalize and we break db rules but not because we want to but because sharepoint makes us. After you have designed your first table you will find some other pointers to take care of. You know that every table must have a key but a table cannot have a composite key. So if you need to create a junction table you have a challenge coming. So join me on my journey to tackle those challenges and see how you can create solutions for them.
For now take the first step. Create simple tables and get used to the development environment. You don’t have to publish anything to be restricted. When you choose new webdatabase the restrictions will be there.
Next time we will try to creat a juntion table without two composite keys and see how you can create uniqeness by using a macro to check the existance of the keycombination before updating the record.
Happy database webbing 😉