MS Access Specify Table Insert Order for Linked Tables? (NULL Foreign Key Error)

Question

Short Version

Is there a way to force (or provide a hint to) Microsoft Access to perform an insert (through an updateable query) in a certain order (between tables), or to tell it that one column in a query depends on (the auto-generated value of) another column in that query?

Longer Version

When I insert values into an updateable query which spans 2 joined tables (both of which are linked tables on an MS SQL Server) through a form, I get the following error:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column <fk column>', table '<tablename>'; column does not allow nulls. INSERT fails (#515) [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (#3612)

This is because Access does not recognize that the fk column of the second table should have the same value as the pk column of the first table in the query (or doesn't successfully execute the query so this happens).

This behavior is observed on Access 2000, 2003, 2007 connecting to MS SQL Server 2005 (Express or Workgroup) through the Microsoft SQL Server ODBC connector.

Play Along At Home

For those of you who would like to play this game at home, here's how to reproduce the behavior I am describing. First, create the test tables in SQL Server:

CREATE TABLE People
(
    PersonID INT IDENTITY NOT NULL
        CONSTRAINT PK_People PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL
);
CREATE TABLE Gamers
(
    PersonID INT NOT NULL
        CONSTRAINT PK_Gamers PRIMARY KEY
        CONSTRAINT FK_Gamers_People REFERENCES People (PersonID)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    Alias NVARCHAR(100) NOT NULL
);

Then, create a new blank Access database and create new linked tables which reference the tables created above. Create a relationship between the tables in the Relationships editor (Access will correctly determine that it is a one-to-one relationship). Then create the following query:

SELECT People.PersonID AS People_PersonID, People.FullName,
    Gamers.PersonID AS Gamers_PersonID, Gamers.Alias
FROM People
    INNER JOIN Gamers ON People.PersonID = Gamers.PersonID;

Note that the above query does work properly for inserting through the Dataset view (as long as FullName is entered, of course).

Then, create a form with the RecordSource set to the query above. Create text boxes for FullName and Alias. Then switch to Form View and try to insert data. With any luck, you will see the error message quoted above.

Possible Workarounds

One way to work-around this problem is to create a sub-form for the data in the child table. With suitable border and coloring it is almost transparent to the user. However, this has a few problems. First, extra code must be added to prevent entering the subform before the parent form is completed (otherwise Access will provide dense errors to the user about master/child link fields). Also, users can't sort/filter on any field in the subform, since it would only filter subform records (of which there will always be exactly one). That being said, it is the solution that I am using for now.

I'm hoping one of you know of a better solution. Thanks for reading.

Answer 1

You could use:

Private Sub FullName_AfterUpdate()
If Me.Dirty Then Me.Dirty = False

End Sub

This will force the creation of the People record.

Answer 2

Although the solution provided by Remou does work, the solution I am currently using is a bit different. It is not necessarily better or worse, and the choice between this solution and the one proposed by Remou will depend on the specific situation.

I created an Updatable View on the SQL Server (using INSTEAD OF triggers) which provided the same data as the query in Access. Next, I created a linked table in Access which referenced the view, then modified the form to use that view instead of the query. Simple, right?

The tricky part was maintaining the pseudo-index when changing the connection string (which I do often), since I did not want to create an index on the view in SQL Server (since data in the underlying table is updated frequently) and Access can only insert/update tables with indexes. The solution was to re-create the index after changing the connection string. (For more on that specific problem, see my writeup)