Geek Answers Handbook

MS Access. Specify table insertion order for linked tables? (Foreign key error NULL)

Short version

Is there a way to force (or give a hint) to Microsoft Access to insert (through an updated query) in a certain order (between tables) or say that one column in the query (auto-generated value) of another column in this query depends?

Longer version

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

ODBC - the call failed.

[Microsoft] [SQL Server ODBC driver] [SQL Server] Cannot insert NULL value in <fk column> ', table' <tablename> '; column does not allow zeros. INSERT does not work (# 515) [Microsoft] [SQL Server ODBC driver] [SQL Server] Statement completed. (# 3612)

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

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

Play at home

, , , , . 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
);

Access , , . (Access , - ). :

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;

, Dataset ( , FullName , ).

RecordSource, . FullName Alias. . , .

- . . . -, , , ( Access / ). , / , ( ). , , .

, . .

+5
3

:

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

End Sub

People.

+1

, Remou, , , , . , Remou .

SQL Server ( INSTEAD OF), , Access. Access, , . , ?

( ), SQL Server ( ) Access / . . ( . )

+1

MSAccess? , , MSAccess "", .

0

More articles:


All Articles