By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,828 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,828 IT Pros & Developers. It's quick & easy.

Importing from excel to access 2002

P: 7
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.
Jul 24 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW * FROM [Tools v3] RIGHT JOIN [Bulk Import] ON [Tools v3].Serial = [Bulk Import].[Tool Serial];
  2.  
Jul 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.