Access UPDATE command

Tom Dunne

I've been roped into "fixing" a crappy Access database that is being used by the Upper Echelons here at work.

I am trying to normalise a particular table, General Contacts, which had company information in it. What I did was split off the Company details off into a separate table, leaving behind a Company_ID field (along with other fields, such as names, phone, e-mail, etc.), which is now the foreign key of the ID field in the Company table.

So, I have
[General Contacts]
Company
Company_ID (FK)

and

[Company]
ID (PK)
Company

(other columns left out for clarity)

I am trying to Update [General Contacts].Company_ID field with the [Company].ID field where the [General Contacts].Company = [Company].Company.

Here's the code I have:

UPDATE Company INNER JOIN [General Contacts] ON Company.ID = [General Contacts].Company_ID
SET [General Contacts].Company_ID = (SELECT [Company].ID FROM [Company])
WHERE (([General Contacts].[Company]=[Company].[Company]));

the error I am getting is "Operation must use an updateable query"

Any ideas what I am doing wrong here? Google isn't really giving me a lot.

Delirium

Tom Dunne wrote: »

I've been roped into "fixing" a crappy Access database that is being used by the Upper Echelons here at work.

I am trying to normalise a particular table, General Contacts, which had company information in it. What I did was split off the Company details off into a separate table, leaving behind a Company_ID field (along with other fields, such as names, phone, e-mail, etc.), which is now the foreign key of the ID field in the Company table.

So, I have
[General Contacts]
Company
Company_ID (FK)

and

[Company]
ID (PK)
Company

(other columns left out for clarity)

I am trying to Update [General Contacts].Company_ID field with the [Company].ID field where the [General Contacts].Company = [Company].Company.

Here's the code I have:

UPDATE Company INNER JOIN [General Contacts] ON Company.ID = [General Contacts].Company_ID
SET [General Contacts].Company_ID = (SELECT [Company].ID FROM [Company])
WHERE (([General Contacts].[Company]=[Company].[Company]));

the error I am getting is "Operation must use an updateable query"

Any ideas what I am doing wrong here? Google isn't really giving me a lot.

Try

[B]UPDATE [/B][General Contacts] INNER JOIN Company ON Company.ID = [General Contacts].Company_ID 
[B]SET [/B][General Contacts].Company_ID = (SELECT [Company].ID FROM [Company])
[B]WHERE [/B](([General Contacts].[Company]=[Company].[Company]));
 

the table specified after the UPDATE keyword should be the table you want to update I would think.

INNER JOIN is altered to reflect that too.

hope that helps:)

FSL

1) You don't need the inner join for the update.

2) Unless I am missing something the query is nonsensical.

The two tables appear to be joined on on Id so why on earth are you trying to change the id by matching on company.

Or when you split the original table did you forget to leave the id in both tables and are now attempting to get the id back, in which case the update without the join would work provided company is unique.

Tom Dunne

FSL wrote: »

2) Unless I am missing something the query is nonsensical.

The two tables appear to be joined on on Id so why on earth are you trying to change the id by matching on company.

Or when you split the original table did you forget to leave the id in both tables and are now attempting to get the id back, in which case the update without the join would work provided company is unique.

You are missing something.

As I said, I am normalising - the original table had no Company_ID, I had to split off the company details and create an ID in a new table (Company). I am now trying to get that ID back into the original table (General Contacts) so I can delete the company details from the contacts table.

I tried the update without the join and it didn't work. I tried it manually (using SQL commands), and I tried it with Access's wizard, which gave me the above.

FSL

Have you tried removing the Foreign Key constraint from the contacts table then running the query below then replacing the FK constraint once all the ID's have been updated. It shouldn't make any difference but then it is Access.


UPDATE [General Contacts]
SET [General Contacts].Company_ID = (SELECT [Company].ID FROM [Company])
WHERE (([Company].[Company]=[General Contacts].[Company]))

Freddio

Its been a long time since I used access but do you not put double or single quote around a table name with two words?

Seth Brundle

Freddio wrote: »

Its been a long time since I used access but do you not put double or single quote around a table name with two words?

Nope - you use square brackets around all table and field names!