Access 2003 and SQL

  • Thread starter Thread starter Mat Child
  • Start date Start date
M

Mat Child

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
 
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.
 
Brilliant,

thanks for that

Mat

Daryl S said:
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.
Click to expand...
 
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)
 
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/wa.exe?SUBED1=ACCESS-L
 
Back
Top