If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 and SQL
Hi,
I created an Access 2005 database and the back end currently uses an MDB on a network server. As what usually happens it's grown beyond what it was intended for. Coupled with the fact the network connection is slow we are getting poor response times. It's not unusable - yet, but it's getting there. I'm trying to pursuade the powers that be to let me access to an SQL server. I know access 2003 ADP's are not supported on SQL 2005, but i did tests using ODBC drivers the front end connects just fine. (I installed sql express and ran the upsize wizard and it worked!!) Questions i have Will the front end still work as though it's using an MDB, ie pull all 65,000 records in a table to find the one it wants or will the sql server be able to return the relevant records? Is there a better way to connect access 2003 to SQL 2005? I have no other choice as to what versions i can use. Any comments would be appreciated |
#2
|
|||
|
|||
Access 2003 and SQL
Mat -
If you switch to SQL Server as the back-end, I would recommend a couple changes to the code so you can let the SQL Server do all the work, and not return the 65000 records if only one is needed. You can do this by creating some Views in SQL Server or by using pass-through queries in your Access front-end so that SQL Server will do the work. -- Daryl S "Mat Child" wrote: Hi, I created an Access 2005 database and the back end currently uses an MDB on a network server. As what usually happens it's grown beyond what it was intended for. Coupled with the fact the network connection is slow we are getting poor response times. It's not unusable - yet, but it's getting there. I'm trying to pursuade the powers that be to let me access to an SQL server. I know access 2003 ADP's are not supported on SQL 2005, but i did tests using ODBC drivers the front end connects just fine. (I installed sql express and ran the upsize wizard and it worked!!) Questions i have Will the front end still work as though it's using an MDB, ie pull all 65,000 records in a table to find the one it wants or will the sql server be able to return the relevant records? Is there a better way to connect access 2003 to SQL 2005? I have no other choice as to what versions i can use. Any comments would be appreciated |
#3
|
|||
|
|||
Access 2003 and SQL
Brilliant,
thanks for that Mat "Daryl S" wrote: Mat - If you switch to SQL Server as the back-end, I would recommend a couple changes to the code so you can let the SQL Server do all the work, and not return the 65000 records if only one is needed. You can do this by creating some Views in SQL Server or by using pass-through queries in your Access front-end so that SQL Server will do the work. -- Daryl S "Mat Child" wrote: Hi, I created an Access 2005 database and the back end currently uses an MDB on a network server. As what usually happens it's grown beyond what it was intended for. Coupled with the fact the network connection is slow we are getting poor response times. It's not unusable - yet, but it's getting there. I'm trying to pursuade the powers that be to let me access to an SQL server. I know access 2003 ADP's are not supported on SQL 2005, but i did tests using ODBC drivers the front end connects just fine. (I installed sql express and ran the upsize wizard and it worked!!) Questions i have Will the front end still work as though it's using an MDB, ie pull all 65,000 records in a table to find the one it wants or will the sql server be able to return the relevant records? Is there a better way to connect access 2003 to SQL 2005? I have no other choice as to what versions i can use. Any comments would be appreciated |
#4
|
|||
|
|||
Access 2003 and SQL
ADP 2003 works perfectly well against SQL-Server 2005 and is fully supported
as a FrontEnd for the data; it's the design tools which are not supported (and don't work). If your current database is actually slow when working against a MDB database file as the backend, likely, it will be as slow or even slower if you simply replace it with SQL-Server 2005 as the backend without making modifications to your code. With an ADP project, it's easier than with ODBC Linked Tables to make things going a little faster but even then, you'll have a lot of work to do. Beside ADP, other solutions to make things going faster (when working against SQL-Server) would be to use unbound forms or go with the .NET framework. With ODBC, you'll have to use linked Views and Passthrough queries but it's not as easy than the other solutions. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please) Independent consultant and remote programming for Access and SQL-Server (French) "Mat Child" wrote in message ... Hi, I created an Access 2005 database and the back end currently uses an MDB on a network server. As what usually happens it's grown beyond what it was intended for. Coupled with the fact the network connection is slow we are getting poor response times. It's not unusable - yet, but it's getting there. I'm trying to pursuade the powers that be to let me access to an SQL server. I know access 2003 ADP's are not supported on SQL 2005, but i did tests using ODBC drivers the front end connects just fine. (I installed sql express and ran the upsize wizard and it worked!!) Questions i have Will the front end still work as though it's using an MDB, ie pull all 65,000 records in a table to find the one it wants or will the sql server be able to return the relevant records? Is there a better way to connect access 2003 to SQL 2005? I have no other choice as to what versions i can use. Any comments would be appreciated |
#5
|
|||
|
|||
Access 2003 and SQL
I suggest getting a copy of "Microsoft Access Developer's Guide to SQL
Server" by Mary Chipman and Andy Baron. I'm sure you can find it used on Amazon. Although it's for Access 2000, many of the strategies are still valid for Access 2003 and SQL Server 2005. The only caveat I have with the book is that it makes much ado about ADO (sorry about that), and Microsoft has backed off on recommending ADO everywhere. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Mat Child" wrote in message ... Hi, I created an Access 2005 database and the back end currently uses an MDB on a network server. As what usually happens it's grown beyond what it was intended for. Coupled with the fact the network connection is slow we are getting poor response times. It's not unusable - yet, but it's getting there. I'm trying to pursuade the powers that be to let me access to an SQL server. I know access 2003 ADP's are not supported on SQL 2005, but i did tests using ODBC drivers the front end connects just fine. (I installed sql express and ran the upsize wizard and it worked!!) Questions i have Will the front end still work as though it's using an MDB, ie pull all 65,000 records in a table to find the one it wants or will the sql server be able to return the relevant records? Is there a better way to connect access 2003 to SQL 2005? I have no other choice as to what versions i can use. Any comments would be appreciated |
Thread Tools | |
Display Modes | |
|
|