Results 1 to 2 of 2
  1. #1
    ZeroTilt is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019

    Moving to SQL server Back End causes subForms within Main Form to be locked

    Dear Forum,
    I have an Access DB with a main Form containing six subForms which were working fine until I moved the tables out to SQL and made them linked tables within Access.
    Now the subforms display info but I cannot edit or add data within the subforms.
    I have searched and discovered a few solutions but none have yet worked.

    My search has revealed that it has something to do with the key or identity setup in SQL.
    One of the six subforms was linked to a table without a primary key. I added the key and it seemed to work. But the other five already have keys and they do not work.

    Other Details
    I have not modified anything in the setup of the form/subform properties and queries other than the fact that the tables are now linked to SQL. Additionally, I have gone back to test this in the last version of my 'full' Access db (without linked tables) and the forms/subforms worked.

    The main form has a table (linked to SQL) as the Record Source, it is a Dynaset, no Record Locks and allows edits, adds, deletions filters
    The sub forms have a query with one table (linked to SQL) as the record source and the ID for this queried table is the same as the ID on the main form's table which are represented correctly in the 'link Master field' and the 'link child Field' on the same ID, they are enabled and not locked.

    Any further assistance would be appreciated.

    (p.s.) I also posted this in another forum - will not do it again, sorry, I am new
    Last edited by ZeroTilt; 04-19-2019 at 01:24 PM.

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Hi, when linking the tables to access, have you correctly identified the PK fields as PK for all the access tables (main and sub tables)? Are the PK fields identity fields? If they were autonumbers in Access the isidentity property in SQL should be set to True.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. can't put 2 subforms on a main form
    By cjohnson in forum Forms
    Replies: 6
    Last Post: 04-28-2017, 01:19 PM
  2. Moving fields from main form to subform
    By jtalka in forum Forms
    Replies: 2
    Last Post: 10-30-2015, 01:39 PM
  3. Replies: 11
    Last Post: 05-13-2015, 11:10 PM
  4. Replies: 7
    Last Post: 01-09-2015, 12:15 PM
  5. Replies: 1
    Last Post: 08-02-2012, 02:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums