Strange Un-editable Record with SQL Azure BE (1 Viewer)

[Minty]

Hi All, I have a strange problem that I sort of know the answer to but wondered if anyone had a sensible workaround;

Azure SQL back end. Simple client table with a number of fields including CreationDate.
CreationDate has a SQL default value of Getdate()

Once a record is created you cannot edit it. If you set a local default date of either Now() or Date() no problem, so it's something to do with the data stored from the Getdate() function.
The value of the date field is somehow locking the record from being edited.
The server data type on the field is good old fashioned Datetime.
Access sees it as a Date/Time data type.

The table has an Identity field set as the primary key.

Has anyone else seen this behaviour, or know how to get around it? I'm baffled.

I'm using the latest ODBC Driver 17 for SQL Server, so don't think it's that.

 

[Isaac]

You mean once the record is created, you can't edit any of the (other) columns?
Has a column been chosen as the official Primary Key in SQL Server, or was one of the columns selected as the Primary Key at the moment the table was linked in Access?

 

[Minty]

Sorry to clarify, If I open the table in Access I can't Edit any of the other columns.
If I open a query based on the table without the creation date included I can edit it.

If I include the CreationDate column in the query I get the error;

I know it's not a massive issue, as I can write an update query to acheive the end result, but it does indicate somethings not working as it should.
During development it's not unusal to nip into to a table to make some changes to see some diffrerent results.

 

[isladogs]

Could this be the bit field with no default value issue?
If you have any nulls in a sql server bit field, Access throws a hissy fit and shows that write conflict error when anyone tries to update the record even if not updating the bit field.
I've never used Azure but expect it triggers the same response in Access

 

[Minty]

It's a great thought, but I don't use bit fields in SQL always an Int or SmallInt for Boolean values. The value I was updating is exactly that - an Integer field used like a Bit.
Had too much head-scratching grief from bit fields in the past to be bothered with them.

(Probably a bit weird of me, but hey, I'm weird! )

 

[Isaac]

It's a great thought, but I don't use bit fields in SQL always an Int or SmallInt for Boolean values. The value I was updating is exactly that - an Integer field used like a Bit.
Had too much head-scratching grief from bit fields in the past to be bothered with them.

(Probably a bit weird of me, but hey, I'm weird! )

Not weird - I like to keep it simple that way too.

 

[isladogs]

I learned about the null bit fields in SS from bitter experience.
As I said I have no experience of Azure
However I would check whether you have a default value for the Int/SmallInt fields...just in case it matters about null values in such cases.

 

[Minty]

You are using Azure in your homework, impressive.

As I stated the solution is to use a query to select the records you want. But I suspect it's not the exact same issue I had.
More likely that the linked table has not identified a unique key when it was linked, this will render any table uneditable.

 

[The_Doc_Man]

This is perhaps less a problem specific to Azure, though I'm not familiar with the details of Azure DBs. But we've seen this before with native Access DBs. If you get that message when you open the record then something about the way the default value has been defined may be your culprit. I.e. it is silently updating the record and then opening it "already dirty."

I don't claim specific experience on this one, though. I DO know that these days, TWO sessions are open when you open an Access FE under Win 10. You can prove this with Task Manager because there is an "expand" mark ">" to the left of Access on the processes list. One of those will be for MSACCESS.EXE image, the other will be for your app. So if MSACCESS.EXE mods the record because of the default AND gives you a writeable copy of the record through the APP session, that is two different users with two different process IDs modifying the record nearly simultaneously.

Don't know what you can do about that other than look at the way the default value is established.