none
Access Write Conflict in Azure SQL DB RRS feed

  • Question

  • Dear All,

    I have a small DB with some tables in Azure SQL (migrated from MySQL).

    We can add rows using MS Access  as a front end to a specific table but we cannot update records to this table.I tried updating rows only from one user.

    Updating records directly from SSMS works ok.

    I have checked this specific table  if it contains any bit column and it doesnt. Also I added a timestamp column to this table and relinked the table, it still doesn't work.

    Below is the definition of the table :

    [id_candidate] [int] IDENTITY(1,1) NOT NULL, -- PK
    [first_name] [varchar](45) NULL,
    [last_name] [varchar](45) NULL,
    [gender] [varchar](10) NOT NULL DEFAULT ('0') ,
    [id_candidate_status] [int] NOT NULL, --FK to another table
    [phone_number] [bigint] NOT NULL DEFAULT ((0)),
    [birthday] [date] NOT NULL DEFAULT ('1900-01-01'),
    [candidate_email] [varchar](45) NOT NULL DEFAULT ('0'),
    [profile_photo_url] [varchar](255) NULL,
    [date_created] [datetime] NULL DEFAULT getdate(),
    [date_modified] [datetime] NULL DEFAULT getdate(),
    [created_by_id] [int] NULL,
    [comment_HR] [varchar](1024) NULL,
    [interview1] [date] NULL,
    [interview2] [date] NULL,
    [interview3] [date] NULL,
    [id_rank] [int] NULL, --FK to another table
    [id_process] [int] NOT NULL DEFAULT ((0)), --FK to another table
    [id_department] [int] NULL, --FK to another table
    [id_skill_level] [int] NULL, --FK to another table
    [id_user_recomandations] [int] NULL, --FK to another table
    [id_employment_role] [int] NULL, --FK to another table
    [Accepted_Date] [date] NULL,
    [TIMESTAMP] [datetime] NULL DEFAULT (getdate())

    I have tried every suggestion I found on the web, so please, every kind of help would be appreciated.

    Thank you.

    ebartos

    Monday, February 12, 2018 3:51 PM

All replies

  • Can you open the linked table in access and add a record that way? (as opposed to using the form bound to the linked table?).

    If you can add by using the linked table direct, then it is a likely that some control defaults on the form are causing the issue.

    So try adding via the linked table. If that works, then of course try by use of that form.

    I assume this is a link to the table, and NOT a view?

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Monday, February 12, 2018 7:11 PM
  • Dear Albert,

    Thank you for your response.

    Actually, I am updating the table directly from access file and not through any access for.

    So, I added one row and now I am able to edit that row but I cannot edit other rows.

    Yes, It is a link to the table.

    I don't know what else to try, so please suggest.

    Best Regards,

    ebartos

    Monday, February 12, 2018 7:26 PM
  • Your table script as you have it does not define a PK - you really want to do this.

    Before you try editing, adding etc., open up the linked table in design view (from Access) - does it show a primary key?

    You have this:

    [id_candidate] [int]  IDENTITY(1,1) NOT NULL,

    But I don't see anything that sets this:

     CONSTRAINT [PK_MyTest2] PRIMARY KEY CLUSTERED

    A identity column in SQL server is very much like a "auto number", but you can have more then one in a given table when using SQL server. Access only allows ONE autonumber column per table (but that autonumber does NOT have to be PK - this applies to both Access or SQL server).

    So both Access or sql server allows one to create a autonumber column

    So in Access:

    Autonumber    = Sql server IDENTITY 

    However, above is NOT a PK (primary key)

    It is rather rare to see a autonumber column in Access not being the PK column (but it is most certainly possible to create such columns in Access). And in YOUR case, the same applies to SQL server.

    I would open up that table in SSMS and check and set that autonumber (identity) column to ALSO be a primary key column. I would then ensure you delete and re-create the one link for that table.

    So check if you skipped setting the PK for this table. You can execute the above SQL (ddl) statement, but with SSMS there also a handy primary key button that you can just hit (much like I Access).

    My spider sense suggests you by accident skipped clicking on the PK button while working in the SQL management studio.

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada





    Monday, February 12, 2018 8:44 PM
  • Dear Alberto,

    Thank you again for your response.

    Actually, the table has a primary key defined in column id_candidate, as shown below :

    CREATE TABLE [dbo].[t_candidates](
    [id_candidate] [int] IDENTITY(1,1) NOT NULL,
    [first_name] [varchar](45) NULL,
    [last_name] [varchar](45) NULL,
    [gender] [varchar](10) NOT NULL,
    [id_candidate_status] [int] NOT NULL,
    [phone_number] [bigint] NOT NULL,
    [birthday] [date] NOT NULL,
    [candidate_email] [varchar](45) NOT NULL,
    [profile_photo_url] [varchar](255) NULL,
    [date_created] [datetime] NULL,
    [date_modified] [datetime] NULL,
    [created_by_id] [int] NULL,
    [comment_HR] [varchar](1024) NULL,
    [interview1] [date] NULL,
    [interview2] [date] NULL,
    [interview3] [date] NULL,
    [id_rank] [int] NULL,
    [id_process] [int] NOT NULL,
    [id_department] [int] NULL,
    [id_skill_level] [int] NULL,
    [id_user_recomandations] [int] NULL,
    [id_employment_role] [int] NULL,
    [Accepted_Date] [date] NULL,
    [TIMESTAMP] [datetime] NULL,
     CONSTRAINT [pk_t_candidates] PRIMARY KEY CLUSTERED 
    (
    [id_candidate] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    I don't know if this has something to do with my table definition or if it is a Access bug, so please guys one more time I would ask for your help.

    Best Regards,

    ebartos

    Monday, February 12, 2018 11:30 PM
  • Hello ebartos,

    >>So, I added one row and now I am able to edit that row but I cannot edit other rows.

    Does the added row indeed added in the table in Azure SQL DB? Could you see the data in Azure SQL?

    If it does, close and re-open the table in access, will the last added row be able to edit now?

    Besides, you said that the table is migrated from MySQL, if you use the same schema to create a new table for testing, will this error still occur?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 6:45 AM
  • Hello Terry,

    Thank you for your response.

    Yes, I can see the last record I added in the Azure SQL, also I can edit the row after closing and reopening the Access file.I find it strange why I can edit the row I added by my self and not other rows added by other users in a different moment.

    Actually, the table was migrated from MySQL using same definition and I am able to edit the records directly form the database using SSMS.

    Best Regards,

    ebartos


    Tuesday, February 13, 2018 9:19 AM
  • Hello ebartos,

    Do you use the same account for authentication in Access and SSMS? Does the account in access  have the permission to edit the existing data?

    Best Regards,

    Terry

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 9:40 AM
  • Hello Terry,

    Thanks again for your response.

    I have give to my login the following roles :

    

    and if you may find it helpful, I use the following ODBC to connect Access with Azure :

    Thank you.

    ebartos

    Tuesday, February 13, 2018 10:08 AM
  • Hello ebartos,

    Since the screen shot is the server roles setting of the user in SSMS, it should be OK to update existing data. Just wondering if the connection used same user account.

    Besides, if you use some SQL string to update the data in Access , could it work?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 22, 2018 1:23 AM