Access projects and SQL Server 2008

[Glenn]

Are Access projects connected to SQL Server 2008 supported?

 

[Sylvain Lafontaine]

If by Access project, you mean an ADP file then yes for the data: an Access
project (any edition: 2000-2007) connected to a SQL-Server 2008 will give
the users the possibility of editing the data (insert, update, delete) on
the server.

However, you cannot use Access in design mode to bring change to the design
of the SQL-Server 2008 database. For this, you'll have to use the
SQL-Server Management Studio 2008.

--
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)

 

[Glenn]

How do I connect the two?? (or create a new project .adp)
Connecting to SQL Server 2005 was easy but Access doesn't seem to recognise
my SQL Server 2008 instance (says requires 'version 6.50 or higher'),
Management Studio (2008) etc seem to be installed and work OK and I thought
the version number of Server 2008 was 10.x

 

[Sylvain Lafontaine]

Nothing special or different from what I know. I just used a copy of an
Access 2003 ADP project to connect to a SQL-Server 2008 instance and there
were no problem and in the past, I did the same with an Access 2007 ADP
project. (All this on WinXP.).

What version of Access are you using?

--
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)

 

[Glenn]

Access 2007 , Windows vista, SQL Server 2008 Developer

 

[aaron.kempf]

I use Access 2003 and Access 2007 wit mixed 2000, 2005, 2008
environment.. and I really don't have too many problems.

do most of the development in a lower version of the database and then
upsize for production.

 

[Glenn]

Can you think of any reason I might be having problems with 2008 but not with
2005?? (Apart from the obvious one!)

 

[Sylvain Lafontaine]

There are many potential connection problems; for example you might have
forgot to specify the instance name. You can try adding the prefix tcp: or
np: before the name of the server and/or to add the port number at the end
(separated by a comma as in ,1433 but as this is probably not the default
instance, then of course you must not use this port 1433) in the case of
TCP/P. Don't forget to put the instance name if there is one.

If you know how, I would suggest that you create an alias and use it to
connect to the SQL-Server 2008. You can verify this alias using your
SSMS2008. This is often the safest way when you have a connection problem.

--
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)

 

[aaron.kempf]

I'm not sure I agree with that statement.

multiple instances use 1433, don't they?

it's just a difference in the servername


thanks

-Aaron

 

[Fernando Oliveira]

Hi,
Looks like someone at Microsoft made a big mistake:
ACCESS 2007 projects work perfectly with SQL Server up to 2005: you can
create and alter tables, create stored proceedures, everything.
But with SQL Server it´s almost useless since it's limited to openning
tables and manipulating data.

Let's hope that "someone" wakes up and fix this.

 

[Paul Shapiro]

Access adp's have only supported design modifications to SQL Server versions
that already exist when the Access version is released. Access has not been
updated in the past to support later SQL versions, and it's doubtful it will
be now. Use the SQL Server tools for working with the db structure, or use
DDL sql statements.