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.
SET Projects.CIDFK =
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.