Hi,

I have a couple of access projects that use SQL server as their
backend, several of which are in Access 2000 and 2003.  Since Access
2007 causes Access 2000/2003 projects to not open correctly if they
are on the same machine I'm currently creating new projects in 2003
until we can update all to 2007.

I've a server, however, that I'd like to upgrade from sqlserver 2005
to 2008.  Can an Access 2000 or 2003 project it connect to sql server
2008 back end?  If so, are there any known issues?

- jd

JD,

I'm presuming that by saying "access projects" you mean .ADPs.

I am unaware of any major issues regarding using/developing .ADPs against a
SQL 2008 back-end with Access 2003, other than noting that the two versions
were not designed with each other in mind, so there are no guarantees, and
you may or may not find significant issues.

Set up a test server with the free downloadable SQL 2008 on a workstation
and try it out for yourself before investing allot of $$ only to find there
are significant issues.

However, The best advice might be to stop developing .ADPs altogether though.
The current "future direction" for Access development with SQL Server (or
other DBs) for back-end data stores is to use linked tables and/or pass-thru
queries.
Generally, this provides a very similar solution story to using .ADPs, which
have clearly fallen out of favor with MS for their future versions of MS
Access.

Show quoteHide quote

To add to what Mark advised, your best bet for creating database
objects is not an Access ADP, but the Developer Edition of SQL Server
($49/US). It has all the features and functionality of the Enterprise
Edition, but the license agreement restricts its use to single user
and prohibits its use in production. Here are some additional
resources which will help you decide the best strategy for upgrading
your current applications and working efficiently with your SQL Server
back end. --Mary

TechEd Online Panel (video):
Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
search for:
"Are we there yet? Successfully navigating the bumpy road from Access
to SQL Server"

Microsoft Access or SQL Server 2005: What's Right in Your
Organization?
http://www.microsoft.com/sql/solutions/migration/access/sql-or-access.mspx

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

What are the main differences between Access and SQL Server?
http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

SQL Server Migration Assistant for Access (SSMA for Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

FMS Upsizing Center
http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446

Migration Considerations for Access 2007
http://technet.microsoft.com/en-us/library/cc178973.aspx
On Wed, 10 Dec 2008 10:45:35 -0800 (PST), jd_nospam
<jdbnospam@gmail.com> wrote:

Show quoteHide quote

Combining 2 queries
Intermittent and undesired SQL execution performance behavior
Partition Table Split Range Performance
How to look for values in a table
SQL2005 spaces in T-SQL
An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with e
the alert couldn't send an email
Downloading Query Analyzer?
where to find the errors generated by stored procedures
Running Total