• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

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

Minty

AWF VIP
Local time
Today, 17:11
Joined
Jul 26, 2013
Messages
7,159
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

Well-known member
Local time
Today, 09:11
Joined
Mar 14, 2017
Messages
1,460
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?
 
Last edited:

Minty

AWF VIP
Local time
Today, 17:11
Joined
Jul 26, 2013
Messages
7,159
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;
1595527968948.png

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.
 

Attachments

isladogs

CID
Local time
Today, 17:11
Joined
Jan 14, 2017
Messages
13,596
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

AWF VIP
Local time
Today, 17:11
Joined
Jul 26, 2013
Messages
7,159
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! o_O )
 

Isaac

Well-known member
Local time
Today, 09:11
Joined
Mar 14, 2017
Messages
1,460
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! o_O )
Not weird - I like to keep it simple that way too.
 

isladogs

CID
Local time
Today, 17:11
Joined
Jan 14, 2017
Messages
13,596
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom