Access: Access Web Database To Issue Or Not

Well today i went ahead with eperimenting on the Web Database features of Access 2010. I'm very excited about this feature in Access. I've read a lot about it and Alan Cossey has been experimenting a lot with it since it became available. His comments, questions and blogpost help me a lot in understanding what's going on in the process of creating a web database. You can find his blog here: http://premierdatatech.spaces.live.com/

I actually stared a couple of weeks a go with a simple table and two forms and published this to the sharepoint site. All went well and i was thinking it's about time i took it a step further. So based on the next scenario i'll describe the issues i ran into.

Old database
For starters i created a new 2010 db. Clean as a whistle. I then imported all objects from an old database i created back in 2002 (don't want to mess with productiondata do we now?). Import went fine and i took the glove and put on my user face to publish this database to sharepoint. I hear you thinking 'glove' 'userface' what's he talking about? Well i decided to go about it as a first time 2010 user who hasn't read about Web Databases and just went ahead to see what happens. Well this happened.

Web Compatibility checker
This is actually a great feature build into Access 2010. It checks to see if your table objects are web compatible. Well how do you know what's compatible or not. Just give it a try...
I did a little testing before so i knew that i would be better to check the tables separately. So right click the table and choose [Check Web Compatibility]. After a few seconds i receive the following messagebox:

This is great this means this table is perfect. On to the next table. Next one same story, next one same story. Great i'm doing fine i'll be up and running in no time.

But wait the next table isn't all that perfect according to the next messagebox i see appearing. Wait it's telling me i should look in the table Web Compatibility Issues. So let's do that:

Lookup tables and relationships

The table is filled with fields telling me my datatypes are wrong, the lookup table isn't correct and that a subdatasheetname should be set to [auto] and defaultvalues aren't allowed and should be set to empty. Wow that's a lot of info to swallow. So put down the coffee and start repairing this table...

In the original db i had a relationship build in the relationshipwindow between two tables. One data-entry table which pulls data from a static table with a few options in it. In the form i created a combo and set the value there. In this table a had a reference to a table set via a combo. It referred straight to the table and the first column in it. This works in your regular client db (although it's not done to do so as you all might know). For the webdatabase you create as select statement for the lookup or you use the lookup wizard from the datatype combo which will guide you through the process. After doing so i ran the checker again. Great this works and the table Web Compatibilities actually decreases with one record. So basically if you fix all the problems your webtable should be empty when the table is perfect.

A lot of issues
I didn't understand the fact that you couldn't set a default value. Well let's fix this and remove the defaultvalue from the table. Remember that when creating a number field in your db (client) it sets a defaultvalue of 0 by default. Realizing this i knew i had a lot of defaultvalues to clear in various tables. Another pointer which i ran into is the format of the date time fields. I had them formatted as "dd-mm-yyyy" a format regulary used in my country (Netherlands). Webdatabases have issues with a format set so i had to remove those as well. Ouch i had a lot of fields set with various formats, i was in for quit some fixing. Short time? No not accepted, long time however is accepted. Formatted numberfields for VAT etc. all had to be fixed.

Finally let's publish to sharepoint
After a couple of hours fixing things, trying to understand why some things had to be fixed and rethinking logic i was ready for some publishing. All tables were webcompatible so nothing could stop me now!
wrong...

Yep a huge dialog showed up telling me it wasn't ready for me yet. Ok let's read this and try to figure out what went wrong. The dialog is telling me that info regarding the errors can be found in the table [Sharepoint issues]. Clear, great that i'm being told where i go wrong. So let's have it tell me what's wrong.

Data is being checked

While you think it may not be so the fields are actually being checked for data. How do i know? Well this table is telling me it doesn't accept dates entered prior to 1900 or after 8900. I check the table and the only issue i can detect is the fact that one date field is empty. So brute force here let's delete that record. That did the job. I can go ahead and try publish again. Now this is where i ran into a point i don't like very much. Everytime the publish action encounters problems it will start all over again. That's not a problem and i can live with that. No the point i'm talking about is closing the sharepoint issuetable. When i tried to close this table i had the following messagebox presented:

And let me tell you i got this very often. Only way i could get the table closed was going to designview and then close the table. Hmm takes up a lot of extra handling along the way. Now explain to me what's the current code being run in the background? Well let's carry on. All tables are fixed all fields are filled as they should be (no nulls allowed so be prepared for that in some cases!)

Publish succeeded!

Yes I did it. It published well at least according to this nice big dialog i'm staring at:

So it's of to sharepoint to see if my webdatabase is actually there. As you would have guessed it's there and all the objects are listed. Hmm why am i not seeing a database? Right all the forms i'm looking at are clientforms and those will not work in webdatabases. So it's of to the drawingboard again to create some forms.

Navigation form

This is really a great feature and should be a nice replacement for the switchboard in future db's. I add a navigation form and to that i add two regular forms which i just drag to the navigationform. Easy as that. Now to make sure Sharepoint understands this i set the Startupform in the client to the navigation form (Options - settings - current database). Let's synch this once more and see if i get some output now. YES it's there my navigationform with two additional forms to show for.

One final issue

Now one final issue i ran into popped up a question. My very first webdatabase i published had a regular navigationform with one subform based on one table, worked like a charm. Now this one also has a navigationform with two subforms and the results are what you expect to be. However i see up and down arrows besides every textbox on my form. So i thought well the scrollbars are set to yes and tried to adjust that in design. Strange thing is that the scrollbars aren't set to yes and keyfield behaviour is set to default (not to next line in box). So this issue had me startled? Anyone knows how this might hapen and wat i can do about it. I just created two webdatabases so i haven't got much reference material.

here's how it looks (partially because of the sensitive data it's cropped) but every textbox looks like this.

scrollbars with every textbox? what's this for setting...

What's next
So after this experiment it's time to take it to the next level. A real webdatabase which you can actually use and which has restrictions build in. That means i have to get started on this other new feature the data macros. As a old school kind a guy i was taught that you don't use macro's and go straight into VBA. Well the webdatabase doesn't allow you to use VBA so you have to use the macros. That's where a new world opens for me and it will take an effort or two befor i get it right but i'll give it a shot and let you know in the next blogpost.