1

I have a SQL Server backend database linking to Access 2016 front end. One of the tables is getting it's primary key incorrectly mapped when linking the table to Access.

The primary key is supposed to be RegistrationID, and this appears to be correct in SQL Server both in the GUI (I can see the key icon next to this field), and in the scripts. However, when I link this table into Access, the primary key is being automatically and incorrectly mapped to ROWGUID, causing all sorts of trouble.

How do I fix this?

Here is the design of the table

USE [BlinkDataMaster2015]
GO

/****** Object:  Table [dbo].[TrnRegistration]    Script Date: 7/5/2016     5:36:24 AM ******/
SET ANSI_NULLS ON  
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TrnRegistration](
    [fkClassSchedID] [int] NULL,
    [fkCompanyID] [int] NULL,
    [NumberUsers] [int] NULL,
    [Notes] [nvarchar](max) NULL,
    [Contact] [nvarchar](50) NULL,
    [fkUserTypeID] [int] NULL,
    [fkOrderTypeID] [int] NULL,
    [OrderRef] [nvarchar](50) NULL,
    [FloatClassID] [int] NULL,
    [LocFrstID] [int] NULL,
    [LocScndID] [int] NULL,
    [SalesRep] [int] NULL,
    [RegistrationID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT     [MSmerge_df_rowguid_7172B8EB13CC4A729BB49A8735FC0B54]  DEFAULT     (newsequentialid()),
    [VerifDate] [bit] NOT NULL,
 CONSTRAINT [PK_TrnRegistration] PRIMARY KEY NONCLUSTERED 
(
    [RegistrationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TrnRegistration]  WITH CHECK ADD  CONSTRAINT     [FK_TrnRegistration_Customers] FOREIGN KEY([fkCompanyID])
REFERENCES [dbo].[Customers] ([ID])
GO

ALTER TABLE [dbo].[TrnRegistration] CHECK CONSTRAINT     [FK_TrnRegistration_Customers]
GO

ALTER TABLE [dbo].[TrnRegistration]  WITH CHECK ADD  CONSTRAINT     [FK_TrnRegistration_TrnClassSched] FOREIGN KEY([fkClassSchedID])
REFERENCES [dbo].[TrnClassSched] ([ClassSchedID])
GO

ALTER TABLE [dbo].[TrnRegistration] CHECK CONSTRAINT     [FK_TrnRegistration_TrnClassSched]
GO

ALTER TABLE [dbo].[TrnRegistration]  WITH CHECK ADD  CONSTRAINT     [FK_TrnRegistration_TrnOrderType] FOREIGN KEY([fkOrderTypeID])
REFERENCES [dbo].[TrnOrderType] ([OrderTypeID])
GO

ALTER TABLE [dbo].[TrnRegistration] CHECK CONSTRAINT     [FK_TrnRegistration_TrnOrderType]
GO

ALTER TABLE [dbo].[TrnRegistration]  WITH CHECK ADD  CONSTRAINT     [FK_TrnRegistration_TrnUserType] FOREIGN KEY([fkUserTypeID])
REFERENCES [dbo].[TrnUserType] ([UserTypeID])
GO

ALTER TABLE [dbo].[TrnRegistration] CHECK CONSTRAINT     [FK_TrnRegistration_TrnUserType]
GO

ALTER TABLE [dbo].[TrnRegistration]  WITH NOCHECK ADD  CONSTRAINT     [repl_identity_range_7AF383DC_269D_4D8C_8E18_2796713D8E44] CHECK NOT FOR     REPLICATION (([RegistrationID]>(516793) AND [RegistrationID]<=(517793) OR     [RegistrationID]>(541793) AND [RegistrationID]<=(542793)))
GO

ALTER TABLE [dbo].[TrnRegistration] CHECK CONSTRAINT     [repl_identity_range_7AF383DC_269D_4D8C_8E18_2796713D8E44]
GO
dkimbrell
  • 67
  • 1
  • 8
  • 1
    You can try running `DROP INDEX` and `CREATE INDEX` statements on the linked table in Access. See [here](http://stackoverflow.com/questions/32316669/how-to-programmatically-create-an-odbc-linked-table-to-a-sql-server-view-and-hav/32316883#32316883) – Andre Jul 05 '16 at 15:06
  • 1
    If I just cut + paste in the create table (without the foreghen constraints), and link to Access - the PK does come out and show fine. I would try linking the table using native 11 sql driver. As noted, if the PK shows ok, then you may well have to create a view and leave out the GUID for replication as that's not required on the Access front end part – Albert D. Kallal Jul 06 '16 at 04:47

0 Answers0