Results 1 to 7 of 7
  1. #1
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215

    Update Query Problem (Not an Updateable Query)

    I get an error whenever I try to run an update query.



    I have a Linked Table. I want to take the values in this linked table and run an update query to update a different table in the database.

    However, whenever I run the query i get an error that says "Operation must use an updateable query". I tried so far as to create a query of these linked values and update the table, but still no such luck. Appending works just fine - but not updating.

    Any suggestions? Maybe this is a problem associated with linked tables?

    Thanks,

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    are you using a left join (from the table you are updating TO the table that has the values you want to use)? if not you likely don't have an updateable recordset.

    An easy way to tell if your recordset is updatable is to open the query in datasheet view, go to the last record (ctrl down arrow) and see if there is a 'blank' line with a * in the record selector portion of the query.

    If not the query won't work. Updating one table from a different, linked, table is doable, I have several databases that do it, I suspect it's simply a bad linking of tables.

  3. #3
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    Click image for larger version. 

Name:	Relationship.jpg 
Views:	14 
Size:	34.8 KB 
ID:	19752Click image for larger version. 

Name:	Update.jpg 
Views:	14 
Size:	88.3 KB 
ID:	19753

    Reference is my linked table. ReferenceT is my regular table that I want to update the values from Reference to.

    Do you see a problem?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    yeah, exactly what I said, you are using an undirected query (I don't know if that's the actual term and I highly doubt it is). Click all the links Leading FROM ReferenceT TO other tables, select the option that makes the arrow point AWAY from ReferenceT, Similarly from arrow pointing FROM table VALUET TO table MAININFOT. Think of it as downhill, all the arrows should be pointing away from REFERENCET all the way down to the last table.

  5. #5
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    I made it so that the arrow points away from ReferenceT and points at Reference in the underlying relationship. Didn't work.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,443
    Can you supply a sample database with some junk data in it, I would rather not try to recreate this and populate it with data.

  7. #7
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    I made a mistake...

    I was trying to overwrite (update) the primary key of the ReferenceT table. I can't overwrite the key itself!

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

Similar Threads

  1. update query problem
    By LUMPKINBD in forum Access
    Replies: 4
    Last Post: 06-25-2014, 03:15 PM
  2. Replies: 4
    Last Post: 03-30-2012, 12:09 PM
  3. Replies: 5
    Last Post: 01-11-2012, 04:26 PM
  4. Update query problem
    By pratim09 in forum Queries
    Replies: 5
    Last Post: 05-05-2011, 06:05 AM
  5. Update Query problem
    By sollem1 in forum Queries
    Replies: 0
    Last Post: 11-08-2007, 10:16 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