MS Access 2007 ADP connection with SQL Server 2008 Express

I can't figure out how to get my MS Access 2007 ADP file connected to a remote sql server 2008 Express server.

I got it working fine in Management Studio, so I know my connection works.  

I have a server name, not IP, like staging.server.com

It works fine in Man Studio but I was able to specify a TCP/IP connection.  Is that the problem?  

I'm entering the server name like in MS, user and password, and then trying to test the connection.  It is using port 1433 which should be correct.  
dougfosterNYCAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might be better off changing the connection via code. The link below shows how to do both - change it manually, or change it via code:
http://support.microsoft.com/kb/306881

Here's more info on buiding DSNless connections, if needed:
http://www.accessmvp.com/djsteele/DSNLessLinks.html
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You may not have your server set to accept Remote connections:

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
0
 
dougfosterNYCAuthor Commented:
Uhm, but on my same client I have Management Studio and it connects just fine.  So why would Management Studio connect and not  Access?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vadim RappCommented:
I would use Network Monitor, to see what goes where.

Also, perhaps, specify dbmssocn as "network library" in Advanced settings of the connection.
0
 
dougfosterNYCAuthor Commented:
Hi vadimrapp1.

Could you give me a little more direction?  Is Network Monitor something I would have with my machine or is something I need to acquire?  

Can you explain how to specify dbmssocn in advanced settings?

Thanks....
0
 
Vadim RappCommented:
> Is Network Monitor something I would have with my machine or is something I need to acquire?  

It's Microsoft tool that will allow you to monitor in realtime the network traffic on your computer. So you will see if Access is trying to connect to your remote server, and what is the result; also you can monitor successful connection of Management Studio to the same server, and then compare it to unsuccessful connection of Access. You can download Network Monitor from http://www.microsoft.com/downloads , search for Microsoft Network Monitor.

> Can you explain how to specify dbmssocn in advanced settings?

In your ADP, open Connection, select tab "All", and specify dbmssocn for Network Library. It's not very likely that this will help, but not impossible. This forces connecting using TCP/IP rather than any other protocol.
0
 
dougfosterNYCAuthor Commented:
Yikes, I can't believe how hard this is.

If I can connect with Management Studio, why am i killing myself to make a connection with MS Access ADP?  

It must be some extension of the server name or user name or something, or I guess it is a weird issue with MS Access 2007....

But, anybody else have any ideas?

I tried Network Monitor and can't figure out how to use it.  I tried specifying dbmssocn for Network Library and no joy.


0
 
dougfosterNYCAuthor Commented:
I guess it just isn't possible.  Bummer.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.