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.