Access Front End / SQL 2000 back end

barks

New member
Local time
Today, 11:09
Joined
Nov 17, 2008
Messages
1
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.
 
Last edited:
Yes, it is doable.

Generally, you would need to use ODBC or OLE DB to access the SQL Server. OLE DB is Microsoft's preferred method, but I can't remember if this is true for 2000 or not. You'll need to find out what is the ideal driver for the 2000 server and the given Access version you have.

You can do same thing with Excel, but not as easily as you could in Access; you would need to write VBA codes to do what Access can do in UI. That said, you can just use Access as a GUI client to examine the result and export it to Excel.

The first thing you would need is to go to Microsoft's support site, look for a whitepaper "Jet/ODBC Connectivity" which will igve you all the basic information you need to get started.

HTH.
 
Best way is to make use of stored-procedure directly in SQL-database.
How, is to difficult to explain over here.

In sql-db you can also make use of tempory tables.
So with a stored procedure (SP) you can store data into a tempory table.
After this you can export this result to other tables.

All of this is not quit difficult, but it asks some study
 

Users who are viewing this thread

Back
Top Bottom