Using MS Access 2003 to connect to SQL Server 2005

Question

My dad built an MS Access Database to do property management. Now he wants to share it with other users over the internet. So I got a hosted SQL Server account at Network Solutions. Now I'm trying to connect the Access 2003 client to a test SQL database using these guidelines from MS:

http://office.microsoft.com/en-us/access/HP052745861033.aspx

However, the error message I get is "Connection Failed because of an error initializing the provider. Server does not exist or access is denied".

I've run the MSComponentChecker to make sure I have MDAC 2.7 or above. Mine is 2.8 SP1 on XP.

Answer 1

It sounds like either:

(1) your workstation is unable to connect to the SQL server instance because (a) the hostname isn't resolving correctly or (b) a firewall in your network or on your workstation is preventing the outbound connection on the needed port

or

(2) you can connect and have the correct username and password but don't have sufficient privileges to get to the database you're attempting to use. Access gives a different error than what you received if you have a bad username or password ("Login failed for user 'xxxx').

You can check the networking by running "telnet example.com 1433" (whatever hostname and port you were given to use; if you weren't given a port, use 1433) and making sure you can connect (the command window will go to a blank screen). If that works, you can then confirm with your provider that the account you're using has been granted the needed permissions. If you can't telnet to the address you need, check you have the right hostname and the existence of any firewalls.

Answer 2

After a little more research, I figured it out.
In MS Access 2003, you have to enter the server name in this format:

serverName\instanceName,portNumber

This is a BackSlash, not a ForwardSlash. Thank you MS. Not sure why I had to specify the port number when it's the default.

Special thanks to this post: http://dbaspot.com/forums/sqlserver-server/251219-error-connecting-remote-server.html

It should be noted that you can't do much design work in Access 2003 once you are connected. So I needed to upgrade to 2007 to change the SQL schema. Hopefully, 2003 users will still be able to connect and use the app.