Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011

    Unanswered: Lookup Foreign Key Value Using SQL

    I am populating an Access 2010 database using VBA to parse Excel spreadsheets. I am having problems with FK's.

    A simpler version of my database that would answer my question is as follows:

    2 Tables: Companies and Projects

    Companies.CID - Company ID and PK
    Companies.CName - Company Name

    Projects.PID - Project ID and PK
    Projects.CIDFK - Foreign Key to CID that links the two tables
    Projects.PName - Project Name, because the table has to have something new

    companyName is a VBA variable containing a string with a viable CName. Let's say it's "Big Company" which has a CID of 3. If you aren't familiar with VBA, just use the string "Big Company" instead.

    My goal is to put the number 3 into the CIDFK for Project 4.

    sqlComm =
    "UPDATE Projects
    SET Projects.CIDFK =
    SELECT Companies.CID
    FROM Companies
    WHERE (((Companies.CName) = '" & companyName & "'))
    WHERE Projects.PID = 4;"

    I then use DoCmd****nSQL(sqlComm).

    I can't seem to do it. I get an Error 3073 - Operation must use an updateable query. I suppose I could do a lookup in VBA prior to the SQL statement, but I would prefer to use the database's power instead of VBA.

    Any help would be greatly appreciated. Thank you.

  2. #2
    Join Date
    Dec 2010
    How about this:

    "UPDATE Projects
    SET CIDFK = " & DLookup("CID", "Companies", "CName = '" & companyName & "'")
    WHERE PID = 4"

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    Me personally, I would create a function to lookup the CompanyID and call that function prior to running the UPDATE SQL.

    Even though it is one SQL statement there are two distinct steps in the SQL. So whether you do one first and then the other or have the Jet engine to them both I don't think it adds too much overhead.

    If you still want to try one SQL command I would try SELECT TOP 1 in thesubquery to see if that makes the query updateable.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts