Access To SQL Server Question

[jbl1167]

We do have an Access application that is growing a lot. We are going to upload the data into SQL Server to run our queries there. If we use the most current SQL Server version Do we need to use ODBC? It is efficient to run the Access queries from the local machines (linking to the tables on SQL Server) or is more efficient to convert the queries into SQL Server and then running stored procedures or views or whatever from an Access interface?

Thank you

 

[Crowley16]

1) do you need odbc?
depends on how you are accessing the data from the front end, I suspect yes if you are going to use linked tables...

2) more effecient to run stored procedures?
yes it is, and it's best practice to do it this way, however keep in mind you'll have to create these almost from scratch whereas using your old queries on linked tables can be done without much work. Either way, you should get a huge performance increase just by using sql server as the back end.

--------------------
Procrastinate Now!

 

[yetanotherjo]

If you want to test first without forking out $$$ to MS for a full licence, download SQL 2005 Express - you'll probably also need to download DotNetV2 and SQL Native Client. You'll need to install Native Client on all machines linking to the database via Access - it's the new ODBC and required for SQL 2005.

It is more efficient if you have a lot of data to do as much processing as possible server-side ie as views and stored procedures. I suggest that you first just try linking your tables and using your existing queries (they should work unless you change object names when you upgrade the database). That way, downtime is minimised and you can work on improvements and translating queries to views and stored procedures and incorporating those into your front end as required.

 

[cheerio]

If Access runs its own query on linked tables it will fetch all the table rows from the server across the network and then test each row against the WHERE conditions.

Where the total number of rows is large and the number you are trying to return is small it is far more efficient to ask SQL server to do the selection so that the network traffic is limited to the rows you need to view.

You can get SQL server to do this by using views/stored procedures or by using Access Pass Through queries.