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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
I guess it just isn't possible.  Bummer.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.