I'm a newb - so go easy.
I have a SQL 2000 back end database which runs the business day to day activities (Sales, Dispatch etc).
I have a requirement to update 10,000 prices in the system. I can't do this through the GUI as it will take too long drilling
into each product 1 by 1. I need a tool which will allow me to dump out all the data I need into a data grid (excel look and
feel), let me update it and then allow me to save the updates directly back into SQL as they are updated through the
I can do this with access if I link to a single table, however, my problem is, the data I need to update is located in
several different tables within SQL. The only way to get this information out, is query the database like this:
select sku,desc,price,suppliersku,shipmethod from products
inner join price on products.sku = price.sku
left outer join suppliersku on products.sku = suppliersku.sku and price.supplier = suppliersku.supplier
My idea is to use access with 3 linked tables. Use a query to export this to a temporary "access" table, make my updates and
then save this back into the relavant tables in the SQL database.
My questions are:
Can this be done?
Is this the most efficient way of doing this?
Can I not just interact with the table directly?
How do I do it?
Can I use excel to achieve the same results?
I'm using access 2007 and I'm no expert - if you can give me a simple step by step example of how to do this, I'd really
My issue is not with pulling out the data, but saving this back into the SQL backend.
If anyone can send me an example of how this is done, I'd be really greatful.
Also, if you think you can knock this up quickly and easily and would like the job of doing so, let me know and I will pay
for this work to be done. I have much more work we're crying out for at the moment. it's all access front end with sql
backend for many users.