This article discusses upsizing Access tables which have one-one relationships, and gives an indication of the type of design changes that maybe required in an Access application when linked to SQL Server.

Why you may have one-one relationships

Developers may decide to split data over multiple tables, normally to avoid technical restrictions of a product. So whilst this practice tends to increase the complexity of a design sometimes it can not be avoided.

Examples include overcoming restrictions on the volume of data which can be stored in a record, overcoming restrictions on a maximum of 255 fields per table, or to implement security constraints on limiting the availability of data fields.

Top of Page

Clever behaviour in Access

As an example consider splitting customer data into two tables as shown below (this can be demonstrated using either Access 2003 or 2007). The CompanyId in tblCompany is an autonumber, and the CompanyId in tblCompanyExtendedDetails is simply a long integer.

When a single form is constructed to display this data for editing, the record source which includes both tables can be constructed using an outer join to show the tblCompany data regardless of whether entries exist in tblCompanyExtendedDetails as shown below.

The form for data entry would be as shown below, where we are starting to enter a new record.

The really clever behaviour happens as soon as we click into the extended details.

Access automatically updates the CompanyID field in tblCompanyExtendedDetails, even though we did not include this fields as part of the form’s recordsource!

So because Access is doing something very clever, you should not be surprised to see that once the data is transferred to SQL Server, and Access is no longer in control of the relationships, the behaviour of this form is likely to change, indeed as we will see it fails to continue working as anticipated.

Top of Page

Behaviour in SQL Server

When linked to SQL Server, you will find that you can not type any data into the field from the second table.

At the bottom of the screen you will see the following message.

Using a good educated guess, you can next try to include the primary key from the other side of the relationship in the form’s recordource as shown below.

Which has been renamed to OtherCompanyId. So repeating the previous entry of a new record, allows the entry of data in the ExtendedDetails field, but results in the following error message.

It turns out that in this case there is a very simple solution to fixing the problem, all we need to do is add a little program code on the after update for the CompanyName field to force the record to be saved.

This is in fact quite amazing that Access can now insert the key value into the other table all we had to do was provide a little help!

Top of Page

Other strategies

Depending upon your design, you may not always be as fortunate as in this example in finding a simple work-around to the challenge of 1-1 relationships.

If you have created the 1-1 relationship to get around the amount of data that can be saved in a row, then you may reconsider this in the light of now using SQL Server which has a greater capacity for holding data, also if the reason was for security then you may choose to see exploit SQL Server’s improved security using Views.

However, if your limitation was the number of fields, then you should realise that for linked tables you will not be able to link to a table with more that 255 fields in SQL Server (SQL Server allows 1024 fields and you could again use views to split up the table to get around this problem and link to the views).

In summary, 1-1 relationships can need additional consideration in terms of refining form behaviour and eliminating the additional table may well prove to be the best course of action.

Top of Page

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

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