Identity used as PK on linked table is insufficient to allow updates in Access

[Auntiejack56]

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

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]

[DropTime] should not be Null.
you need to specify the Unique Record Identifier when you create a Linked table.

 

[Auntiejack56]

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]

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]

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]

Actually, I'm finding this problem extraordinary - all my CreatedOn fields in tables are now suspect. eww.

 

[Auntiejack56]

Ok, this works:

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:

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]

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]

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]

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.

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]

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]

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]

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]

SQL Defaults to server time for Getdate(), in fact, I think it's the regional UTC on Azure regardless of local settings.

 

[Pat Hartman]

We still have no answer to the question about datetime2. Is Access seeing it as a date or a string?

 

[Isaac]

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..

 

[GPGeorge]

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..

PMFJI: this is a long thread and I may have missed something along the way.

The problem with DateTime2 is down to the ODBC driver, not Access itself, well not entirely. Current drivers (ODBC Driver 17 for SQL Server) do handle the more precise times, and won't convert them to strings. However, the older drivers, including the default SQL Server driver will see them as unknown datatypes. It's not Access decision as to whether it uses text or dates; the older driver is saying "here's this thingee, you decide what it is, I don't recognize it." Access takes the safe option and decides it's a string.

Whether or not Access can handle the greater precision is another question. I believe it will simply render those dates with the precision it is capable of. Test that to be sure, though.

You have two choices: Use only the basic datetime datatype in SQL Server or upgrade the ODBC driver you deploy to your users. It will have to be installed on all computers using your relational database application in Access.

TimeStamp fields ( which are now supposed to be called RowVersion fields, but nobody does it) are not really date related at all.

"Rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type."

Access needs these fields, in addition to primary keys, in order to identify local changes that need to be saved back to the SQL Server table. They are useful in nearly all situations. The only one that I can recall at the moment where they are NOT helpful is when you have an application in which two different users can be changing fields in the same row at the same time. I.e. User one has a form to update field1, field2 and field3, while User two has a different form to update field4 and field5. In such situations, the Timestamp can raise an error because both people are seen as changing the entire ROW at the same time, which is not permitted. It's pretty rare, I guess, but possible.

 

[The_Doc_Man]

Whether or not Access can handle the greater precision is another question. I believe it will simply render those dates with the precision it is capable of.

Pardon ME for also jumping in. GPGeorge, you are quite correct to point to the rendering routines.

Native Access DATE fields use DOUBLE as the base typecast. Given that the current day number is in the 40,000 range (i.e. <64K), that is 16 bits. You have 86,400 seconds in a day, which is 64K < #23:59:59# < 128K, so 17 bits for the time. That's 33 bits. You have 53 bits for the mantissa of a DOUBLE, leaving you 20 bits of leeway by the time you get down to seconds.

By experiment I have been able to "play some tricks" with DATE variables to retain fractions of second. The problem with DATE formats in Access is that the formatting routines that convert numbers to text and vice-versa don't understand fractions of a second. The variable does just fine with the fractions, though, since a DATE is merely a typecast of DOUBLE. The variable doesn't "know" that it's a typecast so works just fine.

To GET the fraction, you DO have to "roll your own" on the formatting routines, but converting the DATE via CDBL(datevar) lets you get to those bits. You have 20 bits to play with. The first 10 of that remainder gives you times down to 1 msec. The last 10 COULD give you microseconds but you would have significant rounding issues in the last digit.

In case anyone asks, certain network logs use the internal system crystal-clock timer which has even more resolution. I once had to "diddle" with network logs that were recorded to the millisecond. Navy issue on network timing required it. Access managed it.

 

[Isaac]

PMFJI: this is a long thread and I may have missed something along the way.

The problem with DateTime2 is down to the ODBC driver, not Access itself, well not entirely. Current drivers (ODBC Driver 17 for SQL Server) do handle the more precise times, and won't convert them to strings. However, the older drivers, including the default SQL Server driver will see them as unknown datatypes. It's not Access decision as to whether it uses text or dates; the older driver is saying "here's this thingee, you decide what it is, I don't recognize it." Access takes the safe option and decides it's a string.

Whether or not Access can handle the greater precision is another question. I believe it will simply render those dates with the precision it is capable of. Test that to be sure, though.

You have two choices: Use only the basic datetime datatype in SQL Server or upgrade the ODBC driver you deploy to your users. It will have to be installed on all computers using your relational database application in Access.

TimeStamp fields ( which are now supposed to be called RowVersion fields, but nobody does it) are not really date related at all.

"Rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type."

Access needs these fields, in addition to primary keys, in order to identify local changes that need to be saved back to the SQL Server table. They are useful in nearly all situations. The only one that I can recall at the moment where they are NOT helpful is when you have an application in which two different users can be changing fields in the same row at the same time. I.e. User one has a form to update field1, field2 and field3, while User two has a different form to update field4 and field5. In such situations, the Timestamp can raise an error because both people are seen as changing the entire ROW at the same time, which is not permitted. It's pretty rare, I guess, but possible.

Right.
So in my personal experience this has boiled down to 2 reasons not to, both of which are usually present at the same time:
1) Unnecessary
2) Might work poorly with your Access install

I've never built an Access app that needed datetime2, and haven't had any problems with it saving data back to the SQL table.

On the other hand, I'm more generous with the decision on datetime vs. date since, of course, there is a major benefit to capturing the time in a hindsight's value-add and is easy enough to discard when not needed.

Practical considerations.