This article is the first in a series that discusses a number of issues which will be faced by either an Access programmer when moving to .net or by a .net programmer when converting an Access interface to .net.

In this first article we look at the first of a number of database structural issues, if I build a .net interface to an upsized Access database, what problems will I face?

In this article we tackle drop-down menus, and explore some of the more subtle setting for relationships.

Issues with drop-down list

A drop-down list, such as offering a choice of category when entering a product record, can hide some unforeseen difficulties.

Consider the following relationship (shown in Access) case A.

Or with referential integrity enforced case B.

Or where a developer has decided to allow for categories that can over time become redundant and hidden for new selections we could have the following case C.

Take a look at the following selections

Where the SQL for the drop-down in Access looks like the following.

SELECT tblCategory.Category, tblCategory.HideCategory FROM tblCategory WHERE (((tblCategory.HideCategory)=False));

Top of Page

Access combo boxes are clever

Now let us see what happens when we hide the meat category.

Now even though the meat category is no longer available Access still displays the old value.

We can see that it is not available for selection from the following.

The above examples use a datasheet view, but we could equally have used a form and also be operating with data in SQL Server rather than Access, the behaviour is always the same!

So it turns out that Access and SQL Server database we can have values in fields associated with drop-down lists, where the values are no longer available for selection!

If you go back to our relationships, you will see that for cases A and C this can happen, but not for case B. The remainder of this section discusses what happens with .net in either case A, where a category gets deleted (but we have historical data referring to the category, or case C where list items are hidden).

It turns out that the behaviour with .net, unlike Access is that if we have a value in a field where the value is not available in the drop-down control then the .net control will fail and crash the application. This is a rather serious issue!

Below we see a MUST+WEB translation of this simple Access form.

The drop-down is generated from the following code generated by MUST+WEB

And below we see the .net error message when we go to the record with the illegal value in the drop-down.

To avoid this problem we need to ensure that our data matches case B. This can be achieved by changing the system design, so that if a category is delete then all the associated historical data values are set to null (although this is not very satisfactory from the perspective of maintaining a history over all time, it could be argued that this is not an unreasonable policy to take).

Top of Page

ON DELETE SET NULL

So we need a relationship in SQL Server which sets the value of Category in tblProduct to NULL if a Category is delete from tblCategory.

It turns out that whilst Access only supports CASCADE UPDATE and CASCADE DELETE, SQL Server also supports ON DELETE SET NULL, which is exactly what we want.

Assuming we do not already have a relationship we could use the following :-

ALTER TABLE tblProduct ADD CONSTRAINT fk_Category FOREIGN KEY (Category) REFERENCES tblCategory(Category) ON UPDATE CASCADE ON DELETE SET NULL

Now that’s clever!

So when we delete the meat category, we get the following result.

So you can see from the following, that SQL Server offers several different options for a CASCADE relationship, and that you may want to change the simple Access type behaviour when building a .net application.

So the conclusion to draw from this is that if you have drop-downs for selecting data, you must ensure that existing data values are still available in the drop-downs, and the easiest way to ensure this, yet still preserve the integrity of you data sets is to exploit the SET NULL option on relationships, and ensure that any existing illegal data values are cleared.

Top of Page

MUST+WEB automatically translates Access forms to .net webforms, we pull no punches this is a complex piece of technology, but the savings in development costs we can offer are significant and leave the competition well behind, join us in ensuring you stay well ahead of the competition.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz