Using Access 2007 adp project files with SQL Server 2008 Express

[AppleBonker]

I am trying to work my way through Access 2007 Inside Out. On page 133 the
book instructs me to download and install SQL Server 2005 Express from
www.microsoft.com/sql/editions/express/default.mspx and I could only find SQL
Server 2008 Express. I installed SQL Server 2008 Express and when I tried to
open the project file Contact.adp that came with the book, I got the same
error message that I got before I installed SQL Server 2008
Express...something to the effect that it could not find SQL Server 2005
Desktop Engine or Microsoft SQL Server (even though SQL Server 2008 Express
was running). There is nothing on www.microsoft.com/learning/support/books
about this problem.

I did a search on "Access 2007 and SQL Server 2008 Express" in Microsoft
Technical Communities and one of the threads in Discussion Groups led me to
the SQL Server 2008 Migration Assistant for Access. I successfully installed
it, but it seems to only want to accept .accdb (or older) database files for
conversion and NOT .adp project files.

Is there any way to use SQL Server 2008 Express with Access 2007 .adp
project files or do I need to find the 2005 version? Thanks

 

[Sylvain Lafontaine]

For ADP, SQL-Server 2008 Express should work the same as the 2005 version.
However, by default, all Express are installed in a restricted way (no
external connection, no TCP/IP, no unnamed instance or something like that).
You don't say how this version was installed nor if there were any previous
installation of SQL-Server on this machine or if other thing like the SQL
Server Browser Service is up and running, so it's hard to tell you more on
this.

Try adding the prefix np: (for the Named Pipes protocol) before the name of
the server to see if this will help; you can also try the tcp: prefix. If
this doesn't work, you might have to add the name of the instance after it
or the port number (,1433 - for TCP/IP only) at the end; for example with
"sqlexpress" as the name of the instance:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

or with an unnamed instance and the named pipes protocol:

sqlcmd -S np:NameOfTheServer

You might have to deal with the Surface Area Configuration of SQL-Server
and/or its Network Client Utilities. For the 2008 version, I don't know
what these names are. By setting up an Alias, you can alleviate some of
these problems. Here are some more references for helping you on this topic
but I didn't check them recently. There are for SQL-2005 but most of this
stuff should be OK for the 2008 version:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

 

[Chris Woodard]

Thanks so much for the information. I found that I connect easily if I turn on the SQL Browser service. I was just not sure if SQL Browser offered any bad security repercussions. If there is no real security problems with it running, then I do not have an issue. I actually set the database engine to listen on a specific port and created and Alias to reflect this. Is this good security protocol?

Thanks again.



Sylvain Lafontaine wrote:

Re: Using Access 2007 adp project files with SQL Server 2008 Express
21-Aug-08

For ADP, SQL-Server 2008 Express should work the same as the 2005 version.
However, by default, all Express are installed in a restricted way (no
external connection, no TCP/IP, no unnamed instance or something like that).
You don't say how this version was installed nor if there were any previous
installation of SQL-Server on this machine or if other thing like the SQL
Server Browser Service is up and running, so it's hard to tell you more on
this.

Try adding the prefix np: (for the Named Pipes protocol) before the name of
the server to see if this will help; you can also try the tcp: prefix. If
this doesn't work, you might have to add the name of the instance after it
or the port number (,1433 - for TCP/IP only) at the end; for example with
"sqlexpress" as the name of the instance:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

or with an unnamed instance and the named pipes protocol:

sqlcmd -S np:NameOfTheServer

You might have to deal with the Surface Area Configuration of SQL-Server
and/or its Network Client Utilities. For the 2008 version, I don't know
what these names are. By setting up an Alias, you can alleviate some of
these problems. Here are some more references for helping you on this topic
but I didn't check them recently. There are for SQL-2005 but most of this
stuff should be OK for the 2008 version:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)



EggHeadCafe - Software Developer Portal of Choice
Modifying the Windows Registry on a Web Server ;using ASP / Windows Script Host
http://www.eggheadcafe.com/tutorial...2-e989d757b8f2/modifying-the-windows-reg.aspx

 

[Sylvain Lafontaine]

I'm myself surprised that you cannot connect using a specific port; even
after using an alias. Maybe that for some reason, the firewall is blocking
this port in the case of ADP?

If this is a named instance, maybe you'll still have to specify the name of
the instance in the case of ADP.

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