ODBC problems

[Darkmatter5]

Hello,

I have an SQL Server 2008 R2 server installed at a remote location. I need to build a front end to manipulate that servers data. I am currently using Access 2013.

I have created a System DSN, to get it to work I had to tell it to specifically use 1433 instead of dynamic ports. If I go to the ODBC Source Administrator, click my DSN and test it, it says it connects successfully. If I go to Access I click "External Data", "ODBC Database", select "Link to blah blah blah...", click "Machine Data Source" and select my DSN and click "OK". It prompts me for my password, so I type it in and I get the following error.

Connection failed:
SQLState: '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB][ConnectionOpen
(Invalid Instance()).
Connection failed:
SQLState: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

I just don't get it, I can connect to the server outside of Access, but when I use the DSN through Access I get errors. Any help is appreciated!

 

[SOS]

What version of Windows are you on and which version of Access?

Are you using the SQL Native Client 10 driver or just the generic SQL Service one? If the later, try using the Native Client 10 one instead.