Importing from excel to access 2002

[nerd19]

hi, im trying to do an update query that will take 2fields from 3 excel tables through a union query, BulkUpdate then update an access table, Tools, with the new information. I have done a similar process but with an add query which works fine, this one is just goin to update old information if it already exists. Here is my sql code for the queries:

Importing from excel, BulkImport union query-
SELECT [Dies].[Customer Serial#] AS [Tool Serial], [Dies].[(inches) I#D# Size] AS [Size]
FROM [Dies]
WHERE (([Dies].[Customer Serial#]) Is Not Null)
UNION SELECT [Punches].[Serial number], [Punches].[c'bore dia#]
FROM [Punches]
WHERE (( [Punches].[Serial number]) Is Not Null)
UNION SELECT [Redraws].[Serial number], [Redraws].[Required O#D# Size]
FROM [Redraws]
WHERE (([Redraws].[Serial number]) Is Not Null);

Query to update access table, tools, to new information from BulkInput Query-
UPDATE DISTINCTROW [Tools v3] RIGHT JOIN [Bulk Import] ON [Tools v3].Serial = [Bulk Import].[Tool Serial] SET [Tools v3].Serial = [Bulk Import].[Tool Serial], [Tools v3].[Size] = [Bulk Import].Size, [Tools v3].LastUpdate = Now(), [Tools v3].Status = "In", [Tools v3].Regrinder = Forms![Bulk Import]!Regrinder
WHERE ((([Tools v3].Serial) Is Not Null And ([Tools v3].Serial)=[Bulk Import].[Tool Serial]) AND (([Bulk Import].[Tool Serial]) Is Not Null));

What i am having trouble with is i get an error that says: Operation must use updateable query. Error 3073. I have searched the net and found that a lot of people have the permissions issue or ODODB driver issue but i have neither. Can Someone give any advice to what might be the cause? Thanks in advance.

[FishVal]

hi, im trying to do an update query that will take 2fields from 3 excel tables through a union query, BulkUpdate then update an access table, Tools, with the new information. I have done a similar process but with an add query which works fine, this one is just goin to update old information if it already exists. Here is my sql code for the queries:

Importing from excel, BulkImport union query-
SELECT [Dies].[Customer Serial#] AS [Tool Serial], [Dies].[(inches) I#D# Size] AS [Size]
FROM [Dies]
WHERE (([Dies].[Customer Serial#]) Is Not Null)
UNION SELECT [Punches].[Serial number], [Punches].[c'bore dia#]
FROM [Punches]
WHERE (( [Punches].[Serial number]) Is Not Null)
UNION SELECT [Redraws].[Serial number], [Redraws].[Required O#D# Size]
FROM [Redraws]
WHERE (([Redraws].[Serial number]) Is Not Null);

Query to update access table, tools, to new information from BulkInput Query-
UPDATE DISTINCTROW [Tools v3] RIGHT JOIN [Bulk Import] ON [Tools v3].Serial = [Bulk Import].[Tool Serial] SET [Tools v3].Serial = [Bulk Import].[Tool Serial], [Tools v3].[Size] = [Bulk Import].Size, [Tools v3].LastUpdate = Now(), [Tools v3].Status = "In", [Tools v3].Regrinder = Forms![Bulk Import]!Regrinder
WHERE ((([Tools v3].Serial) Is Not Null And ([Tools v3].Serial)=[Bulk Import].[Tool Serial]) AND (([Bulk Import].[Tool Serial]) Is Not Null));

What i am having trouble with is i get an error that says: Operation must use updateable query. Error 3073. I have searched the net and found that a lot of people have the permissions issue or ODODB driver issue but i have neither. Can Someone give any advice to what might be the cause? Thanks in advance.

Hi.

I'm not sure whether the query you try to update is indeed updatable. Run it as SELECT query and check this.

SELECT DISTINCTROW * FROM [Tools v3] RIGHT JOIN [Bulk Import] ON [Tools v3].Serial = [Bulk Import].[Tool Serial];