Access/SQL Server 7.0/Optimization??? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Access/SQL Server 7.0/Optimization???

I have been involved in upgradind and access database file to an SQL Server Database File. The first time I used and ODBC to connect to the SQL Server Database from an access front-end, I noticed a significant low speed performance. I decided to switch to an Access Database Project ( ADP file), which uses an OLEDB connection, thinking that it would solve the problem . In Access Project you have access to all SP and View that are stored on the Server. My problem is that I am still experiencing low performances when the file is shared on on the network by mutiple uers( in this case only 4 so far) although an ADP project is supposed to be extremely powerful in terms of speed. Also every time you open the application while it is opened by another user, you get a warning that the application is opened in an exclusive mode and you can’t make any changes to it. Microsoft recommends to make a copy of the application (Front-end) on each user’s machine to solve the problem. Making a copy of the application on each user’s machine is not an option since it would involve instlallig a copy on more than 400 stations. I rather with the option of puting a short cut to the application on each user’s machine. I know that access is not a true client/server environment.But is there any way I could speed up the application? Or I am better off linking to SQL Server through an ODBC?
The keep the story short,how could my application truly benefit from a client/server environment? And what are the best ways to take advantage of power of SQL Server Database Engine? I always thought that SQL Server Database Environement is associated with speed, security, data itegrety,.. etc.
I would truly appreciate any help
Thank you
Tarik Bensehil
Did you consider migrating the database using DTS? My solution would be to simply copy the data out of access database into SQL Server and then do the rest of the migration steps (data modification etc.) in SQL Server environment. SQL Server is highly scalable in terms of no. of users it can support. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
SQL Server is a fast, reliable back-end database, but the front-end application has to be designed to take full advantage of it. And this means not using Access as the front-end. Instead, use VB, ASP, or some other front end that can take full advantage of SQL Server’s capabilities. Also, see this book for more info:http://www.sql-server-performance.com/access_sql_server_developers_guide_review.asp —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Also interesting article about comparision between Access and SQL Server, http://www.mssqlcity.com/Articles/Compare/SQLvsAccess.htm. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you guys for your help. I appreciate it a lot.
I agree with you on the fact that in order to take full advantage of SQl Server you must use a different front end other than access. But for the time bieng I have no other choice.
Is there any way ( and easier way I hope), I could call my views and stored procedures using a new access mdb file that uses an ODBC this time, and keeping all my objects that I have created in my APD file. This is just to test the difference between my adp and this new mdb file?
Thank you. Tarik Bensehil
]]>