I have an Access database that uses SQL Server as its backend. One table is the data for a subform which is loaded into a parent form and displayed in the parent forma as a datasheet. The datasheet is intended to have rows added to it by users.

The subform backend table is defined in SQL Server with a primary key [ID]. The primary key is linked to the Master ID of the current record displayed in the parent form. The subform is then filtered by the relationship between the subform table primary key and the parent form master ID. Nothing unusual here.

The problem that when the parent form is first opened in Access the subform is cannot have records added. It is not until the linked table manager is opened and the backend table is RELINKED can the user add records to the subform. Oddly enough, when relinking the table I am always prompted for the primary key of the table even though it is defined in SQL Server.

The subform has the following settings

Data Entry:   Yes
Allow Additions:  Yes
Allow Deletions:  Yes
Allow Edits:  Yes
Allow Filters:  Yes
Record Locks:  No Locks

Here is the CREATE TABLE for the backend table:

CREATE TABLE [dbo].[ProjectHealth](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Project Health] [nvarchar](2000) NULL,
    [Timestamp] [datetime] NULL,
    [ProjectID] [int] NULL,
    [Entered By] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Again, the RELINK solves the problem. But a VBA TableDef.Refreshlink call does not do the trick. Even if I reset the primary key from VBA.

My suspicion is that I have modified some value in the subform settings that have caused this problem which started happening after a few days of normal behavior. Thanks in advance for any suggestions you can provide.