Results 1 to 7 of 7
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    How to create a new Table on the back-end of a split DB with editing capability

    I have a split DB with a back-end and a front-end. I created a new Table Survey 123 Data on the back-end, joined it in relationships and linked the new table to the Click image for larger version. 
<br /><script async src=
    Name: Westfield BE pic.JPG  Views: 13  Size: 102.3 KB  ID: 36219" class="thumbnail" style="float:CONFIG" />
    front-end.

    However, after creating a Query1 which included the new Table Survey 123 Data on the front-end I noticed that I no longer have editing capability within the Query1 as I do in other queries on my front-end.

    I tried creating a similar Query1 on the back-end, with the same problem (no editing capability). But I can create other queries on the backend with using my original tables, and they do have editing capability.

    What do I need to do to have my new Table Survey 123 Data behave as the original tables in my DB?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are many reasons why queries become read only.
    Allen Bowne has a good article on this http://allenbrowne.com/ser-61.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, I notice you have several tables with the same field names. Seems like they should be on one table with a field to identify area they come from. For that reason you might want to consider properly normalizing your database.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I might be wrong but I assumed these were multiple iterations of the same table using aliases
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Thanks. From the list of read only queries it looks like the join type. There is no unique index. And, looking deeper into my Table1 somehow I have 25 duplicates which have crept in. OK, now I think I know how to fix this by removing those duplicates.

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Oh and just noticed another problem upon further review. You should be linking Primary Key to Foreign Key. And if those ID's are Autonumbers Data Types then that is going to cause another problem because they skip and will cause an even bigger problem.

  7. #7
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    Smile

    Quote Originally Posted by GinaWhipp View Post
    Oh and just noticed another problem upon further review. You should be linking Primary Key to Foreign Key. And if those ID's are Autonumbers Data Types then that is going to cause another problem because they skip and will cause an even bigger problem.

    OK thank you.

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

Similar Threads

  1. Editing a split database
    By cwrockloff in forum Access
    Replies: 2
    Last Post: 04-09-2018, 08:21 PM
  2. SharePoint Table vs. Back End Split Database
    By Access4444 in forum Database Design
    Replies: 8
    Last Post: 12-13-2016, 03:31 PM
  3. Replies: 1
    Last Post: 04-10-2014, 12:17 PM
  4. Replies: 4
    Last Post: 05-21-2012, 08:21 AM
  5. Editing Primary Key in Split Database
    By jaypoppin in forum Access
    Replies: 10
    Last Post: 03-16-2011, 10:39 AM

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