Results 1 to 7 of 7
  1. #1
    mjblay is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    5

    Help with Update query: updating one table based on data selected from another table?

    I've been scratching my head over this for hours, searching all over this site and the internet, but I can't understand how to perform a seemingly simple Update query. I'm ready to ask for help. My setup:



    Brands Table:
    Code:
    CREATE TABLE Brands (ID INTEGER PRIMARY KEY, BrandName CHAR (255))
    ID BrandName
    1 Dell
    2 Lenovo
    3 HAL Laboratories


    Computers Table
    Code:
     CREATE TABLE Computers (ID INTEGER PRIMARY KEY, BrandID INTEGER, ComputerName CHAR (255))
    ID BrandID ComputerName
    1 3 Hal9000


    The following INSERT INTO query works to addend the row above to the Computers table:
    Code:
    INSERT INTO Computers ( ComputerName, BrandID ) SELECT "Hal9000", ID FROM Brands WHERE BrandName = "HAL Laboratories"

    I'd like to change Hal9000's brand to Dell. This means I have to change the BrandID field of the record with ID 1 in the Computers table from 3 to 1. A user doesn't know that Dell's ID is 1 in the Brands table, so I have to retrieve that information using only the BrandName in order to update the Computers table. I cannot figure out how to use an UPDATE query to change the BrandID using user-supplied text specifying the ComputerName and BrandName:
    Code:
    
    UPDATE Computers SET BrandID = (SELECT ID FROM Brands WHERE BrandName = "Dell") WHERE ComputerName = "Hal9000"
    The query above results in the "Operation must use an updateable query" error. I cannot find a "Show" box to uncheck, however, as referenced on Allen Browne's site (http://allenbrowne.com/subquery-02.html). Is it there in Access 2010?


    Code:
     UPDATE Computers INNER JOIN Brands ON Computers.BrandID = Brands.ID SET Computers.BrandID = Brands.ID WHERE Brands.BrandName = "Dell" AND Computers.ComputerName = "Hal9000"
    
    The query above just tells me I am about to update zero rows.

    I know I can just execute one query to find the BrandID and programatically store the result of the query in a variable, then use that variable to execute a second query:
    Code:
    Sub UpdateTable(sComputerName As String, sBrandName As String)
        Dim oConn As ADODB.Connection, oRSet As ADODB.Recordset
    
        Set oConn = New ADODB.Connection
        oConn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;Data Source=MyDatabase.accdb;"
    
        Set oRSet = oConn.Execute("SELECT ID FROM Brands WHERE BrandName = '" & sBrandName & "';")
        oConn.Execute ("UPDATE Computers SET BrandID = " & oRSet(0).Value & " WHERE ComputerName = '" & sComputerName & "';")
        
        oConn.Close
        Set oConn = Nothing
    End Sub
    But isn't there a way to do this with SQL using only one query? Is the execution of two queries necessary?

    Thanks for your help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How is the user selecting the brand - a combobox? Use a multi-column combobox with columns for the ID and brand name. Then when your code refers to the combobox it will have the ID available as criteria for the SELECT. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.

    Why are you using UPDATE and INSERT queries for simple data entry/edit? Are your forms bound?
    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.

  3. #3
    mjblay is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    5
    Thanks, but I'm trying to make Access work as the database behind a small ASP web page. I don't have the luxury of using combo boxes or any controls within forms. The only way I know of to interact with Access via ASP is using the ADODB object model (or perhaps the DAO object model) which limits my interaction to the database to SQL queries only.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The 'Show' box is under each field of query design grid. I don't actually see how that pertains to your situation.

    Sorry, I have no experience with web database.
    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.

  5. #5
    mjblay is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    5
    I see the 'Show' box now. The reason I hadn't seen it before is because it's not there for an Update query.

    I appreciate your responses, and I think it's amazing that places like this exist where people just volunteer their expertise for the benefit of others, but I don't think you have to know much about web databases to answer my question. Basically, all I'm looking to do is update a record using SQL only... you can do that with a query in design/SQL view.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, and I can't see anything wrong with the nested UPDATE but I have never had to do one. Can you use domain aggregate functions (DLookup, DSum, etc)?
    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.

  7. #7
    mjblay is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    5
    Thanks for the suggestion. I played around with it a bit today...

    So this works from a SQL query run from within access. It also works when using the ADODB object model to call a SQL query from outside Access (i.e. an ASP page):
    Code:
    UPDATE  Computers SET Computers.BrandID = DLookup("ID",  "Brands",  "BrandName =  'Dell'") WHERE Computers.ComputerName="Hal9000";

    Unfortunately, Allen Browne states that it is very slow. After creating a VBA module and a subroutine with Allen Browne's ELookup code (http://allenbrowne.com/ser-42.html), I found that this query works within a SQL query executed inside Access too:
    Code:
    UPDATE  Computers SET Computers.BrandID = ELookup("ID",  "Brands",   "BrandName  = 'Dell'") WHERE Computers.ComputerName="Hal9000";

    This query above using ELookup is purportedly faster, but it does not work when an SQL query is executed from outside Access. Rather, it gives the error:
    Run-time error '-2147217900 (80040e14)': Undefined function 'ELookup' in expression.

    I guess you can't use custom VBA functions in SQL queries executed from outside Access??

    Looking at his code, it's really just using VBA to run another query anyway. So I suppose I could use DLookup, but I think that's the equivalent of running a separate query.

    I tried to make Access work as the db for a website back when I had Access 2003, and I ended up abandoning it in favor of MySQL. I'd hoped it might work better in the 2010 iteration, but it still seems that its database component is reliant on its forms and controls for full functionality. The following query:
    UPDATE Computers SET BrandID = (SELECT ID FROM Brands WHERE BrandName = "Dell") WHERE ComputerName = "Hal9000"


    which gives such problems in Access works just fine in MySQL using the same table definitions. I'm not trying to knock Access. I think it's great for what it does. And I would really like to use Access because it can integrate other components of the Office suite (namely Word and Excel), but I still need this same database it to be able to serve data to web pages. I guess my best bet is just to use two queries.

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

Similar Threads

  1. Query isnt updating table based on criteria
    By shabbaranks in forum Queries
    Replies: 12
    Last Post: 01-10-2012, 11:51 AM
  2. Replies: 3
    Last Post: 12-06-2011, 11:32 AM
  3. Replies: 3
    Last Post: 01-17-2011, 01:48 AM
  4. Replies: 1
    Last Post: 05-25-2010, 02:58 PM
  5. Replies: 4
    Last Post: 09-03-2009, 02:01 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