Access 2003 and SQL 2005

[Patrick D.]

Hi,

By creating a new project in Access 2003, which should be connected to SQL
2005, I get the message, that the SQL Server is newer than SQL 2000 and
Access 2003 does not support this version.

Is Access 2003 not supporting SQL 2005?
Do we need a patch for Access? (Office Update Center tells me, that my
Office components are up to date)


Thanks for your hint.

regards
Patrick

 

[Craig Alexander Morrison]

Patrick

ADPs are on the way out SQL Server 2005 can be connected to when your design
is complete but you cannot use the daVinci toolset in Access unless you are
connected at design time to SQL Server 2000.

Access 12 will not be supporting ADPs to SQL Server 2005 either, so far as I
am aware.

Unless you really need something that SQL Server 2005 has I would recommend
using SQL Server 2000 certainly when designing and developing and if your
solution was going to use SQL Server 2005 Express you can always use that
for deployments with level 8 compatibility, I believe.

I have moved on to using IBM DB2 UDB so my understanding of SQL Server 2005
is from the September 2005 Beta but I don't believe anything significant has
changed.

 

[Mary Chipman [MSFT]]

Hi Craig,

ADP's will continue to be supported, just not the designers. That
could, of course change between now and Access 12; however the team is
more focused on Access-Jet functionality (many cool new features
there). You can easily connect an ADP to an existing SQLS 2005
database running in 2000 compat mode.

The Access team is recommending using linked table techniques for all
*new* applications--they are not going to yank support for existing
ones. For best results, and this goes for both SQLS 2000 and 2005--use
the Developer edition of SQLS to create and secure the back end
database. The licensing agreement prevents it from being used for
deployed apps or as a production server, but it has all of the tools
of the Enterprise edition, which neither Access nor VS possess. The
$49 price is hard to beat.

--Mary

 

[dbahooker]

i call bullshit on this

ADP is the best program anywhere; and these drunk fools are too stupid
to realize their importance.

of course Microsoft WILL come out with a patch for this.

i mean-- server side crosstab query wizard anyone? of course it will
happen.

i just claim that with $50bn in the bank that Microsoft _CAN_ afford to
bring Access into the 21st century.

as it is; MDB wimps use technology from 1990.

MDB is obsolete
Long Live ADP!!

 

[Robert Morley]

I don't know if anybody would know this, but I don't suppose there's some
kind of hybrid mode for SQL Server 2005, is there? What I'm thinking of is
things like pivot tables and other such SQL language improvements that would
be forward-compatible, allowing the older ADPs to run normally with full
design permissions, while allowing you to use some of the newer features.

I've only ever used SQL Server 2000, so I don't know if the above is a
"stupid question", as I've never had to deal with transitioning from one
version to another.



Rob

 

[Mary Chipman [MSFT]]

There are new T-SQL constructs, but if you're developing a SQLS 2005
application you will want to use the Developer edition ($49, with a
licensing restriction that prevents its use as a production server).
If you create a new SQLS 2005 database, you can connect to it using an
older ADP. However, new features like complex types won't be
available, which is what you'd expect since the older software can't
be designed for 100% forwards compability. And no, it's not a stupid
question. Lots of people have been asking questions along the same
lines

--Mary

 

[dbahooker]

it's a stupid ****ing company that can't come out with a patch for SQL
2005

i mean.. this is from the company that didnt have the FORESIGHT to make
SQL 2000 and Access 2000 work together without a patch.

GIVE US A NEW VERSION OF ADP; I DONT CARE IF YOU SELL IT TO US

ADP IS _SUPERIOR_ TO EM / QA / SQL Management Studio
a) real sproc designer
b) design view for subqueries
c) i mean.. you can COPY and PASTE tables-- you can't do that anywhere
else

i jsut swear

MICROSOFT YOU CAN DO A BETTER JOB

GIVE US THE PATCH FOR SQL 2005 THAT WILL LET US DO A SQL-SERVER BASED
CROSSTAB QUERY WIZARD.
IN ADP.

WITH OFFICE 2003.

RIGHT NOW KIDS.

 

[Michelle]

Hi Mary

It's obvious you know a fair amount about this and I would like to ask some
specific questions.

I have read this thread fully but didn't fully comprehend it. I have posted
a thread just the other day asking about the SQL 2000 / 2005 Express issue
with ADP and received a reply that was straight to the point which cleared
up one of my questions.

I am having immense trouble trying to install SQL Server 2000 Developer
Edition (which came with my VS 2003). I am making the assumption that this
is the same product SQLS you are talking about for $49? is this a correct
assumption to make?

I currently have SQL Express 2005 installed because I could install SQL
2000. Is it possible that the trouble I'm having installing SQL 2000 is
because of SQL Express 2005 is installed, or that may have nothing to do
with it at all?

I would appreciate any help that you have as I'm starting to get quite
frustrated as to why I can't install this and would dearly like to further
expand into ADP.

Thanks in advance
Michelle

 

[Sylvain Lafontaine]

Yes, the edition that comes with VS 2003 is the same thing as the one which
is sold separately for 49$. Also, any version of SQL-Server 2000 (MSDE,
Standard, Enterprise, Personal or Developer) will work perfectly well with
ADP.

ADP will also work with SQL-2005 but only for the data stuff, not the design
tools. For the design part, you can use the management studio for SQL-2005;
however, ADP will still keep displaying its nasty message about the newer
version of SQL-Server.

You should be able to install SQL-2000 alongside the 2005 version without
any problem. The usual way is to install 2000 first and then 2005 after;
however, installing 2005 first should not be a big deal; especially if you
take the precaution of reinstalling/repairing the 2005 version after
installing SQL-2000.

You don't tell us what's your problem with the installation of SQL-Server
2000; so it's hard to find you a solution. However, as you want to use ADP,
the best solution might simply to remove SQL-Express 2005 first.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

 

[John Q.]

Mary,

Just to confirm what I think I'm hearing - the MDB linked table
approach to SQL 2005 backend should work fine? I'm assuming I've just
got to make sure I get the latest SQL ODBC driver.

Also, the SQL Compatibility mode associated with a SQL 2005 database -
I don't have to deal with that, right, if I'm just linking to the
tables in the database from my Access 2003 front ent, right?

Thank you!

 

[Mary Chipman [MSFT]]

Yes, the edition that comes with VS 2003 is the same thing as the one which

is sold separately for 49$. Also, any version of SQL-Server 2000 (MSDE,
Standard, Enterprise, Personal or Developer) will work perfectly well with
ADP.

The Developer edition of SQLS 2005 is the Enterprise Edition, with
licensing restrictions. It is not the same as the version that ships
with VS 2003.

--Mary

 

[Mary Chipman [MSFT]]

I'm confused as to why you want to install VS 2003 if you are
developing an Access/ADP application. You can install multiple
versions of SQL Server, but you can have only one default instance. If
you want to create an Access/SQL 2000 app from scratch, the Access
team recommends that you use linked tables instead. However, if you
prefer ADP's, you should have no problems with ADP/2000 development.

--Mary

 

[Mary Chipman [MSFT]]

If you're doing linked table, make sure your MDAC verrsion is up to
speed--you don't need to worry about odbc drives separately.

You can link to a 2005 database not in 2000 compat mode, but Access
obviously won't be able to handle the new complex data types like
UDTs, CLR in proc, and other new functionality all that well. You'll
have to play with it and see how it goes.

--Mary