SQL Server to Access import problem

Question

When I try to import an SQL Server database into access I am getting this error

I am using

OS: Windows Vista
App: MS Access 2007
DB: MS SQL 2005

Error:
Connection failed
SQLState; "01000'
SQL Server Error: 52
[Microsoft] [ODBC SQL Server Driver] [DBNETLIB] ConnectionOpen
(Connect()).
Connection failed;
SQLState: '08001'
SQL Server Error: 17
[Microsoft] [ODBC SQL Server Driver] [DBNETLIB] SQL server does not
exist or access denied.

Answer 1

I assume you using the external data tab. You then choose odbc. Are you using the native driver (that is for sql server 2005), or are you using the SQL server driver? I suggest you try using the SQL server driver (that is the legacy one).

So, when you fire up the import wizard, I assume you then create a new data source.

Also is this an local edition of sql server running, or somewhere else on your network? Are you able to fire up the visual studio 2005 and open/view tables use the tools for sql server 2005 on your machine? (it REALLY helps if you can get other connections to sql server working).

So, I would suggest that you try BOTH drivers. Also, can you link to sql server in place of importing?

Answer 2

Yuor connection string is wrong (does not lead to any server)

or

you do not have the right to access the server

Please check it here

Answer 3

I get this error quite often. The easy workaround, if it is temporary, is to just setup an alias to the server in cliconfg.exe and then point access to the alias.