Identity used as PK on linked table is insufficient to allow updates in Access (1 Viewer)

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
Hi,

If I create a table in SQL Server Express as below with a default getdate(), and link it in Access, I can add new records via Access, but not update any - instead I get the 'this record has been changed by another user ...'. Changing the default to CURRENT_TIMESTAMP in SQL doesn't work either.

If I add records to the table in SSMS, they get flagged with a red exclamation triangle saying "This row was successfully committed to the database. However a problem occurred when attempting to retrieve the data back after the commit. Because of this the displayed data in this row is read only."

Going back to Access, if I close the table and reopen it, some records are updatable and some are not.

I can work around the problem by either
a) removing the default altogether,
or
b) adding a Timestamp field at the end of the table.

Does anybody know why this might be happening, and if there is a more elegant way to resolve this?

Thanks,

Jack


SQL:
CREATE TABLE [dbo].[tblDropOffClients](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    [ClientID] [int] NOT NULL,
    [DropLocation] [nvarchar](255) NULL,
    [DropTime] [datetime2](7) NULL,
 CONSTRAINT [PK_tblDropOffClients] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDropOffClients] ADD  CONSTRAINT [DF_tblDropOffClients_SessionID]  DEFAULT ((0)) FOR [SessionID]
GO

ALTER TABLE [dbo].[tblDropOffClients] ADD  CONSTRAINT [DF_tblDropOffClients_ClientID]  DEFAULT ((0)) FOR [ClientID]
GO

ALTER TABLE [dbo].[tblDropOffClients] ADD  CONSTRAINT [DF_tblDropOffClients_DropTime]  DEFAULT (getdate()) FOR [DropTime]
GO
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:18
Joined
May 7, 2009
Messages
14,648
[DropTime] should not be Null.
you need to specify the Unique Record Identifier when you create a Linked table.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
Thanks Arnelgp, but I'm sorry to say that it makes no difference.

This is what I did:
I dropped the table and recreated in SSMS with NOT NULL for Drop time.
I deleted and relinked the table in Access.
I checked the indexes for the linked table to be sure it had a URI. And indeed there is a PK for the ID field Primary=Yes, Unique=Yes, Ignore Nulls=No. (Is that what you meant?)
Then (in Access) I manually created 6 new records in the table.
Then I closed the table and reopened it to attempt a manual update on each record, but could update only 3 of them. The other 3 gave me the 'another user is editing' message, no matter which of the fields I updated. The other 3 records can be updated in any field - in fact, if those 3 records are identical except for the PK, they still update ok.

I think it's weird. Any further suggestions appreciated.

Jack
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:18
Joined
May 7, 2009
Messages
14,648
when you re-created the Linked table in msa, did it ask you
to select the Unique Record Identifier? it should and you should select the Identity field.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
No, it doesn't prompt. I would not have expected it if there is a PK defined in SQL. In all my years, only tables with no defined PK will show the URI prompt.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
Actually, I'm finding this problem extraordinary - all my CreatedOn fields in tables are now suspect. eww.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
Ok, this works:

SQL:
CREATE TABLE [dbo].[tblDropOffClients2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    [ClientID] [int] NOT NULL,
    [DropLocation] [nvarchar](255) NULL,
    [DropTime] [datetime2](0) NOT NULL,
 CONSTRAINT [PK_tblDropOffClients2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDropOffClients2] ADD  CONSTRAINT [DF_tblDropOffClients2_SessionID]  DEFAULT ((0)) FOR [SessionID]
GO

ALTER TABLE [dbo].[tblDropOffClients2] ADD  CONSTRAINT [DF_tblDropOffClients2_ClientID]  DEFAULT ((0)) FOR [ClientID]
GO

ALTER TABLE [dbo].[tblDropOffClients2] ADD  CONSTRAINT [DF_tblDropOffClients2_DropTime]  DEFAULT (getdate()) FOR [DropTime]
GO

but if you use the default precision for seconds as below when creating the datetime2 column, it fails randomly (about 50% of the records) when attempting to update in Access:

SQL:
CREATE TABLE [dbo].[tblDropOffClients2](
    ...
    [DropTime] [datetime2](7) NOT NULL,

I'm pretty sure the high precision datetime is not supported by Access, but I can't spend much more time on this for the time being. If I come up with anything else I'll post here again. Probably it is also better to use CURRENT_TIMESTAMP instead of (getdate()), because getdate is not a constant (I guess hence the brackets), but I tried it and it didn't solve the problem so not essential to this discussion.

Thanks,

Jack
 

Minty

AWF VIP
Local time
Today, 07:18
Joined
Jul 26, 2013
Messages
8,910
Unless you need the precision of a DateTime2 use a smalldatetime data type.
Older versions of Access struggle with Datetime2 data types, and I find them best avoided.

It also works fine with Getdate() as a default value.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
thanks Minty.
smalldatetime is not working with the default (getdate()) either :-(

I've now attempted to use smalldatetime, datetime, and datetime2(7) with the default (getdate()), and they all fail to allow updates on random records (roughly half of the records in the table) in Access.

If I remove the default, updating works fine.

Only datetime2(0) works with the default.

I suspect its a problem with Driver 17. Only time will tell - no doubt this issue will arise again, and I'll revisit then.

Thanks,

Ray
 

Minty

AWF VIP
Local time
Today, 07:18
Joined
Jul 26, 2013
Messages
8,910
We use SQL driver V17 as a default in all our apps. It is stable and supports all the latest features, so I would be surprised if that is the cause.

I have seen this behaviour before, but it's not random. It either works or doesn't.
What version of Access are you using?
I'm sure I posted about something similar before... I'm off to do a search.

This is a small table with exactly the set-up you describe, and it works fine, this is with O365.
SQL:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tl_WageTypes](
    [WageType_ID] [int] IDENTITY(1,1) NOT NULL,
    [WageTypeText] [varchar](150) NULL,
    [GL_Code] [nvarchar](50) NULL,
    [DateAdded] [smalldatetime] NULL,
    [AddedByID] [int] NULL,
    [ArchivedDate] [smalldatetime] NULL,
 CONSTRAINT [PK_tl_WageTypes] PRIMARY KEY CLUSTERED
(
    [WageType_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tl_WageTypes] ADD  CONSTRAINT [DF_tl_WageTypes_DateAdded]  DEFAULT (getdate()) FOR [DateAdded]
GO
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:18
Joined
Feb 19, 2002
Messages
33,777
1. Foreign keys should NEVER default to 0. They should always default to null unless you have some record you entered manually with an identity value of 0 and you actually want to link to that row by default.
2. It is not clever, cute, or in any way useful to use "ID" as the name of the PK for each table. All it does is to make it harder for other developers to map a FK to a PK without using the database diagram. Access will default to this name if you don't specifically create your autonumber column. It can't assign any other name because you have not yet saved the table so it doesn't even know the table name and even if it did, it wouldn't know what method you use to create PK names.
3. Make sure that Access supports the datetime2 data type. That seems to be the actual problem. If Access doesn't support that data type, it will convert the field to text as long as the ODBC driver supports it BUT it will also treat it as text and that means the field will not work as a date which will lead to other issues when sorting or comparing.
4. I always use a TimeStamp field (or whatever they are called these days) because it helps Access to operate more efficiently.
 

Auntiejack56

Registered User.
Local time
Today, 18:18
Joined
Aug 7, 2017
Messages
106
Great stuff, thanks.
Wouldn't you know it - for a completely unrelated reason, I've had to remove the defaults! This is because I have a multitenant server with remote logins from different timezones, so a default (getdate()) inserts the server time, not the user time. I'm hoping a change to the GPO will fix that problem, and the previously defaulted fields will be inserted by the application running on local time.
I am also using a TimeStamp field in all tables now, good call on that one.
Jack
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:18
Joined
Feb 19, 2002
Messages
33,777
Did you determine whether or not Access is treating the date field as a string?
In a multi-time zone application, you probably want the times to be "server" time or GMT.
 

Minty

AWF VIP
Local time
Today, 07:18
Joined
Jul 26, 2013
Messages
8,910
SQL Defaults to server time for Getdate(), in fact, I think it's the regional UTC on Azure regardless of local settings.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:18
Joined
Feb 19, 2002
Messages
33,777
We still have no answer to the question about datetime2. Is Access seeing it as a date or a string?
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:18
Joined
Mar 14, 2017
Messages
5,924
In many years I have never had a need to use any sql server datetime other than: datetime
Despite the shiny things they come out with, they are largely irrelevant unless you are working with engineering or scientific data

Never had someone ask me: "Yeah, but which nanosecond/millisecond was it?"

and have never had a problem using that - or a problem with default getdate() for CreatedOn columns, which I use a lot too.
Must be something else..
 

Users who are viewing this thread

Top Bottom