I am currently working on converting an Access 2003 DB app to using SQL Server 2008 as the backend rather than Access tables. Access will still be the front end, we'll just be running all of the data on SQL Server 2008. The conversion is going pretty good, but I am really looking to speed up some of the data access, which is still pretty slow in certain areas even though SQL Server is now in play. Am I missing anything? Do I have to rebuild indexes to make it faster? I am also going through and setting up Views (and potentially Stored Procedures) as well. I have figured out how to connect the Views in SQL Server to the Access app, but haven't tried Stored Procedures yet. Are calling Views faster than standard in line queries inside the Access app? How about Stored Procedures? Should I be using SPs instead of Views? If so, how is a SP called from inside the Access app - maybe the same as linking up a View as a table?
Any thoughts and advice are greatly appreciated here!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks for the reply and thoughtful advice!
Ok, so, "You use ADO and the Command object" seems to be the real piece here that I need to work on and starting to implement the Stored Procedures on the server. So, going down this road, by using ADO and the Command object, is that all done in code or can I still use a lot of the existing stuff in Access for wiring it all together?
In order to use the ADO interface you have to write code. I know that my not be what you wanted to hear, unfortunately as developers sometimes it is necessary. So I would suggest you get familiar with the ADO object model. Here is a good start:
http://www.w3schools.com/a
Business Accounts
Answer for Membership
by: acperkinsPosted on 2009-11-06 at 10:09:21ID: 25761462
>>which is still pretty slow in certain areas even though SQL Server is now in play. Am I missing anything? Do I have to rebuild indexes to make it faster? <<
If you are just changing the SQL Syntax to comply with the T-SQL syntax, then that is to be expected. It will be a dog.
>>Are calling Views faster than standard in line queries inside the Access app?<<
It depends on the resultset, but in general yes.
>>How about Stored Procedures? Should I be using SPs instead of Views? <<
You should be using Stored Procedures from your Access front end exclusively. There are three reasons for that: Security, maintenance and performance.
>>If so, how is a SP called from inside the Access app - maybe the same as linking up a View as a table?<<
You use ADO and the Command object.