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 datagrid.
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 appreciate it.
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.
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 datagrid.
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 appreciate it.
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.
Last edited: