ms accesspermissionssql serversql-server-2016
I have a table we just transferred from MS Access to SQL Server 2016.
It's a simple table for creating load numbers for shipping products. Using access we link to the SQL Server and we are able to add new records and create a load number but we can not edit the record after that. What setting am I missing?
It must be in permissions. Any ideas? I have noticed that I can edit some older records and records that I create so it must be a permissions setting somewhere.
Microsoft Access has a setting in the Query Properties window called Recordset Type. You need to set this to Dynaset (Inconsistent Updates) to allow your query to be updatable.
No, don't loop. Any time you think in SQL "I need to loop over this set and do something to every row" change your thinking to "I need to do something to every row in this set."
DECLARE @start SMALLDATETIME = '2015-08-06T15:00:00';
;WITH x AS
(
SELECT id, creatie, rn = ROW_NUMBER() OVER (ORDER BY id) - 1
FROM dbo.Topic
--WHERE creatie = '2131-08-06T15:00:00'
)
SELECT id, creatie, DATEADD(MINUTE, rn, @start) FROM x ORDER BY id;
-- UPDATE x SET creatie = DATEADD(MINUTE, rn, @start);
When you are happy that this does what you expect, comment the SELECT and uncomment the UPDATE.
Best Answer
The "Write Conflict" dialog is discussed in the Microsoft documentation for MS Access here:
Write Conflict dialog box
That seems pretty straightforward - if other people have access to the SQL Server table (likely), then they have possibly changed the row you're trying to change since the last time results were loaded for you. One way to work around this problem is to periodically refresh your view of the table (for example, after each edit you make).
In practice, there are some caveats to this process with SQL Server specifically around
NULLvalues in the data rows, as well as the presence of a Primary Key and / or Timestamp definition for the rows. These are discussed in this Stack Overflow post:Editing Record issues in Access / SQL (Write Conflict)