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

    Editing Linked Databases via Query


    I am faced with the 2 GB/database limit. To circumvent, I want to create 2 databases on the back-end to store the same data and link the tables on the front-end. Once linked, I want to be able to use queries to edit/manipulate data in both data bases. However, I tested this process and it appears that I can certainly view the data in both, but I cannot edit/manipulate via queries using linked tables from both databases. Although, I can edit/manipulate with queries that point to each database, separately.
    What can I do to be able to query AND edit linked databases in the same query? Or, is this a limitation in Access?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should be able to edit/manipulate data in ALL linked tables.
    *unless by manipulate you mean table design , then no, you cannot edit table design. You must edit directly in the main db.

    but edit & queries data all day long in the FE.

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

    Need your help to resolve this. I have two back-end databases each with 2 linked tables (tables ending with suffix ver1 and ver2). I set up mu front-end database importing those tables and set up my test query. 2 pics show the arrangement.

    Now, in my query, I can't change anything. So, what am I doing wrong?Click image for larger version. 

Name:	Link1.JPG 
Views:	25 
Size:	53.2 KB 
ID:	42161Click image for larger version. 

Name:	Link2.JPG 
Views:	24 
Size:	88.5 KB 
ID:	42162

  4. #4
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @ranman256Click image for larger version. 

Name:	Link3.JPG 
Views:	24 
Size:	65.3 KB 
ID:	42163
    After I posted I realized that I simply imported one of the databases tables. I corrected that by linking them instead. But, I still can't edit anything through the query.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Linking two primary keys is a one-to-one relationship. Why is NSver2 linking to 2 tables?

    Relationships don't make sense to me. Why are you joining tables that have identical structure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @ranman256
    Table1 ver1 and Table NS ver1 are linked in their own database. They carry different data, despite appearances.

    Table1 ver2 and TableNS ver 2 are linked in their own database. Same comment as above.

    I then linked Table1Ver1 to Table1ver2. And the same for the NS ver1 and ver2 to align them on the primary key to be able extract/edit the data for each primary key.

    How else should these Tables be linked in the query?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How is data differenct since field names are exactly same?

    How did you split the data - by date period?

    Again, linking on primary keys results in a one-to-one relationship. Your query is non-updatable.

    Use forms for data entry/edit. Normally a form does data entry/edit for one table. Use form/subform arrangement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

    OK, so I unlinked all links in primary databases (ver1 and ver2). Then, I only linked them in my Query, as shown. Still, I can't use teh query to edit anything.

    Question1: can a query be used to edit any linked Databases?

    if yes,Click image for larger version. 

Name:	Link1a.JPG 
Views:	22 
Size:	98.5 KB 
ID:	42165

    Question2: how do I link the needed databases to do this?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Now your links don't involve any primary keys. Links should be on associated primary/foreign key fields.

    Your data structure is not making sense. Did you see post 7?

    Don't do edits directly in table or query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just wanted to point out that "Type", "Cycle" and "Name" are reserved words in Access and shouldn't be used for object names

  11. #11
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @June7 and ssnafu

    Thank you for your input and I made some modifications. In the end, I am able to link 2 databases and via queries, can edit data in all tables. First, I query/linked the tables "directly split" between the 2 DBs, and then I used a "Summ" Query for manipulating data. The test works fine now. Thank you.Click image for larger version. 

Name:	FinalLink1.JPG 
Views:	12 
Size:	66.3 KB 
ID:	42182Click image for larger version. 

Name:	FinalLink2.JPG 
Views:	12 
Size:	65.9 KB 
ID:	42183Click image for larger version. 

Name:	FinalLink3.JPG 
Views:	12 
Size:	67.6 KB 
ID:	42184Click image for larger version. 

Name:	FinalLink4.JPG 
Views:	12 
Size:	60.6 KB 
ID:	42185

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe the queries work but links in Relationship builder don't make sense.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Splitting databases and editing it afterwards
    By Tazmaniac in forum Access
    Replies: 2
    Last Post: 03-25-2018, 01:21 PM
  2. linked databases query
    By vicsaccess in forum Queries
    Replies: 5
    Last Post: 10-09-2015, 03:01 PM
  3. Workaround for editing Linked Tables
    By Stretholox in forum Import/Export Data
    Replies: 3
    Last Post: 12-12-2014, 02:03 PM
  4. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  5. Editing a Linked Table
    By amndza in forum Access
    Replies: 2
    Last Post: 01-21-2009, 01:27 PM

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