Update Table in Sage300 (Timberline) in Access from Excel

I need to update the addresses in a Sage300 table. I have the addresses in excel. I can pull in the table from Sage through Access and pull in the excel sheet with the corrected addresses. Once I click query and then update, how do I setup the info in the query to look at each key and update the address from the excel to the table?

I thought I would bring in the table and the excel and link the keys, then in the field area I would put in the fields that I want to update from the table. In the update to I would put the excel fields that have the correct info and click run. This changed all data to the same info. Example:
Field - table.address1
Update to - excel.address1

I thought this would update all the address1 info from excel and based on the key being the same it would update table.address1
SystemsystemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I thought this would update all the address1 info from excel and based on the key being the same it would update table.address1
And this did not work? Did you get any errors? Or did it just simply not update the data?

You'd have to be sure to Join the two "tables" (the Sage table, and the Excel worksheet) on the right field(s) in order for the update to occur, and you'd also have to have the necessary permissions on the Sage table for the updates to occur. You can check the permissions by simply editing a value in the linked table, then moving off that record. If the change "sticks" then you should have sufficient permissions to perform the update.

You may also find that you have to actually "import" the Excel worksheet, and not simply link it. The "link" fields will need to have the same datatype, and you may find that a linked Excel worksheet reports the wrong datatype for that link. With an imported table you can define the datatype you wish to use.

Here's an MSDN tutorial about the various types of Update queries:

http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SystemsystemAuthor Commented:
Ok I will look at that. What it did was update all the address to the same address.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.