Cannot link to SQL server

  • Thread starter Thread starter Stephen Raftery
  • Start date Start date
S

Stephen Raftery

Can any of you guys help?
I am new to using SQL Server. I have a database that I wish to upsize from
Access to put the back end on a server, and use Access as a front-end.
However, I am stuck at the very first stage - I cannot link Access to the
SQL server.

I am using SQL Server Management Studio on a single computer for development
purposes, no network involved yet.
In Access 2010 beta, when I try External Data / ODBC / Link, then I am
presented with the Select Data Source dialog. I click on New at the DSN Name
field, and select SQL Server, then type in a name fro the new data source,
and Finish. I then get another dialog 'Create a New Data Source to SQL
Server', where I type in a description and select (local) for the server to
connect to, and choose Windows authentication. This is where my problem
arises: I get 'Connection Failed: SQLState 08001, SQL Server Error 17, SQL
Serverdoes not exist or access denied'

I am sure this is a pretty basic problem - can anyone tell me where I need
to turn next?

Stephen
 
Additional info: I have already created an SQL database with all the table I
need, I just cannot connect to it from within Access.

Stephen
 
When you created your table in SQL-Server, did you set permissions?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Stephen,

In addition to Jeff's reply... the Server also has to be running when you
try to connect to it.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Additional info: I have already created an SQL database with all the table I
need, I just cannot connect to it from within Access.

Stephen
 
No I did not set any permissions: where do I do that?
The database was created using SQL Server Management Studio: does that have
to be running as well?


Stephen
 
I have tried linking while the SQL Studio is open: no joy there.
I cannot find any option to alter Permissions: where would that be?

Stephen
 
Stephen,

Before we get to the permissions issue (and the answer to the the question
will answer the permissions issue)... Opening SQL Server Management Studio
does not mean the database is *running*. Please go to the the Database
folder and navigate to your database and double click... what happens? AND
when you open SSMS do you get a prompt to log on?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
I have tried linking while the SQL Studio is open: no joy there.
I cannot find any option to alter Permissions: where would that be?

Stephen
 
Ah, I get you.
When I click on the database file, I get the message
'Windows cannot open the file 'Rotamaster.sdf'
To open it, Windows needs to know the program you want to use to open it.
What do you want to do:
@ Use the Web Service to find the corre nt program
@Select a program from a list of installed programs.


Does this shed some light?

Stephen
 
Yep... now we are getting somewhere... if that is the name of your database.
Also sounds like you don't have SQL Server installed. What version did you
install? Did you download or use CD's?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Ah, I get you.
When I click on the database file, I get the message
'Windows cannot open the file 'Rotamaster.sdf'
To open it, Windows needs to know the program you want to use to open it.
What do you want to do:
@ Use the Web Service to find the corre nt program
@Select a program from a list of installed programs.


Does this shed some light?

Stephen
 
Right, I am with you now.
I downloaded SQL Server Management Studio along with Configuration Tools and
Integration Services. I assumed that that was what I needed. Is there
something else? I obtained them from the SQL Express website.

Stephen
 
Right, I have gone back and downloaded SQL Server Express 2005, installed it
and rebooted. When I browse to the .sdf file, I still get the 'Windows cannot
open the file.
When I try to make an ODBC connection, I get a SQL Server Error 2, Named
Pipes Provider: could not open a connection to SQL Server [2]

I am micro-step further, but still stuck!

Stephen
 
Stephen,

Okay, SQL Server Express is the Server not the Management Studio. So now
you have it and now you need to create your database OR start it up and
upsize your tables. Did you do that yet?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Right, I have gone back and downloaded SQL Server Express 2005, installed it
and rebooted. When I browse to the .sdf file, I still get the 'Windows
cannot
open the file.
When I try to make an ODBC connection, I get a SQL Server Error 2, Named
Pipes Provider: could not open a connection to SQL Server [2]

I am micro-step further, but still stuck!

Stephen
 
Gina, you are being very helpful, and I am very grateful to you, but I seem
to be stuck in the link between ODBC and SQL.

I have SQL working (ie, in SQL Configuration manager / SQL Server Services /
SQL Server SQLEXPRESS has a green arrow next to it to show it is running, and
under SQL Server Network Config , the protocols for Shared memory, Named
Pipes, TCP/IP, and VIA are all enabled). Also under the Task Manager /
Services I can see SQLEXPRESS listed as one of the services that is started.

However, the ODBC Data Source Administrator keeps on failing to link to SQL
I get an error message like this:

Microsoft SQL Native Client Version 09.00.4035
Running connectivity tests...
Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections.

TESTS FAILED!


Gina Whipp said:
Stephen,

Okay, SQL Server Express is the Server not the Management Studio. So now
you have it and now you need to create your database OR start it up and
upsize your tables. Did you do that yet?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Right, I have gone back and downloaded SQL Server Express 2005, installed it
and rebooted. When I browse to the .sdf file, I still get the 'Windows
cannot
open the file.
When I try to make an ODBC connection, I get a SQL Server Error 2, Named
Pipes Provider: could not open a connection to SQL Server [2]

I am micro-step further, but still stuck!

Stephen


Stephen Raftery said:
Right, I am with you now.
I downloaded SQL Server Management Studio along with Configuration Tools
and
Integration Services. I assumed that that was what I needed. Is there
something else? I obtained them from the SQL Express website.

Stephen
Click to expand...
Click to expand...
 
Stephen,

You said Access 2010? Never tried that to SQL Express 2005 and not sure it
would work. Please download version 2008.

Thanks for the kind words... I'm here till we get this resolved. I know it
works I've done it!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Gina, you are being very helpful, and I am very grateful to you, but I seem
to be stuck in the link between ODBC and SQL.

I have SQL working (ie, in SQL Configuration manager / SQL Server Services /
SQL Server SQLEXPRESS has a green arrow next to it to show it is running,
and
under SQL Server Network Config , the protocols for Shared memory, Named
Pipes, TCP/IP, and VIA are all enabled). Also under the Task Manager /
Services I can see SQLEXPRESS listed as one of the services that is started.

However, the ODBC Data Source Administrator keeps on failing to link to SQL
I get an error message like this:

Microsoft SQL Native Client Version 09.00.4035
Running connectivity tests...
Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does
not
allow remote connections.

TESTS FAILED!


Gina Whipp said:
Stephen,

Okay, SQL Server Express is the Server not the Management Studio. So now
you have it and now you need to create your database OR start it up and
upsize your tables. Did you do that yet?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Right, I have gone back and downloaded SQL Server Express 2005, installed
it
and rebooted. When I browse to the .sdf file, I still get the 'Windows
cannot
open the file.
When I try to make an ODBC connection, I get a SQL Server Error 2, Named
Pipes Provider: could not open a connection to SQL Server [2]

I am micro-step further, but still stuck!

Stephen


Stephen Raftery said:
Right, I am with you now.
I downloaded SQL Server Management Studio along with Configuration Tools
and
Integration Services. I assumed that that was what I needed. Is there
something else? I obtained them from the SQL Express website.

Stephen
Click to expand...
Click to expand...
 
I have decided to delete SQL Server, and re-installed it again (SQL Express
2005).

I have tried to create a new ODBC System DSN using the ODBC Data Source
Administrator, outside of Access completely, and I still get the same
problem, so I do not think the problem is with in Access.

The error message is this:
Microsoft SQL Native Client Version 09.00.4035

Running connectivity tests...

Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does not
allow remote connections.

TESTS FAILED!


I have gone through the SUrface Area Configuration, and enabled remote
connections, but no joy yet.

Stephen


Gina Whipp said:
Stephen,

You said Access 2010? Never tried that to SQL Express 2005 and not sure it
would work. Please download version 2008.

Thanks for the kind words... I'm here till we get this resolved. I know it
works I've done it!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Gina, you are being very helpful, and I am very grateful to you, but I seem
to be stuck in the link between ODBC and SQL.

I have SQL working (ie, in SQL Configuration manager / SQL Server Services /
SQL Server SQLEXPRESS has a green arrow next to it to show it is running,
and
under SQL Server Network Config , the protocols for Shared memory, Named
Pipes, TCP/IP, and VIA are all enabled). Also under the Task Manager /
Services I can see SQLEXPRESS listed as one of the services that is started.

However, the ODBC Data Source Administrator keeps on failing to link to SQL
I get an error message like this:

Microsoft SQL Native Client Version 09.00.4035
Running connectivity tests...
Attempting connection
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [2].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does
not
allow remote connections.

TESTS FAILED!


Gina Whipp said:
Stephen,

Okay, SQL Server Express is the Server not the Management Studio. So now
you have it and now you need to create your database OR start it up and
upsize your tables. Did you do that yet?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Right, I have gone back and downloaded SQL Server Express 2005, installed
it
and rebooted. When I browse to the .sdf file, I still get the 'Windows
cannot
open the file.
When I try to make an ODBC connection, I get a SQL Server Error 2, Named
Pipes Provider: could not open a connection to SQL Server [2]

I am micro-step further, but still stuck!

Stephen


Stephen Raftery said:
Right, I am with you now.
I downloaded SQL Server Management Studio along with Configuration Tools
and
Integration Services. I assumed that that was what I needed. Is there
something else? I obtained them from the SQL Express website.

Stephen


:

Yep... now we are getting somewhere... if that is the name of your
database.
Also sounds like you don't have SQL Server installed. What version
did
you
install? Did you download or use CD's?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Ah, I get you.
When I click on the database file, I get the message
'Windows cannot open the file 'Rotamaster.sdf'
To open it, Windows needs to know the program you want to use to open
it.
What do you want to do:
@ Use the Web Service to find the corre nt program
@Select a program from a list of installed programs.


Does this shed some light?

Stephen


:

Stephen,

Before we get to the permissions issue (and the answer to the the
question
will answer the permissions issue)... Opening SQL Server Management
Studio
does not mean the database is *running*. Please go to the the
Database
folder and navigate to your database and double click... what
happens?
AND
when you open SSMS do you get a prompt to log on?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
I have tried linking while the SQL Studio is open: no joy there.
I cannot find any option to alter Permissions: where would that be?

Stephen


:

Stephen,

In addition to Jeff's reply... the Server also has to be running
when
you
try to connect to it.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Additional info: I have already created an SQL database with all
the
table
I
need, I just cannot connect to it from within Access.

Stephen


:

Can any of you guys help?
I am new to using SQL Server. I have a database that I wish to
upsize
from
Access to put the back end on a server, and use Access as a
front-end.
However, I am stuck at the very first stage - I cannot link
Access
to
the
SQL server.

I am using SQL Server Management Studio on a single computer for
development
purposes, no network involved yet.
In Access 2010 beta, when I try External Data / ODBC / Link,
then
I am
presented with the Select Data Source dialog. I click on New at
the
DSN
Name
field, and select SQL Server, then type in a name fro the new
data
source,
and Finish. I then get another dialog 'Create a New Data Source
to
SQL
Server', where I type in a description and select (local) for
the
server
to
connect to, and choose Windows authentication. This is where my
problem
arises: I get 'Connection Failed: SQLState 08001, SQL Server
Error
17,
SQL
Serverdoes not exist or access denied'

I am sure this is a pretty basic problem - can anyone tell me
where I
need
to turn next?

Stephen
Click to expand...
Click to expand...
Click to expand...
 
This is the ODBC data source configuration:

Microsoft SQL Native Client Version 09.00.4035

Data Source Name: Rotamaster
Data Source Description:
Server: localhost
Use Integrated Security: Yes
Database: (Default)
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
 
Did you see my last suggestion? I am not sure Access 2010 *talks* to SQL
Server 2005, try downloading SQL Server 2006

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
This is the ODBC data source configuration:

Microsoft SQL Native Client Version 09.00.4035

Data Source Name: Rotamaster
Data Source Description:
Server: localhost
Use Integrated Security: Yes
Database: (Default)
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
 
Stephen,

Congrats! Glad to hear it is finally up and running...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

<Stephen> wrote in message
Victory! Victory is mine!
Bring me all the muffins in the land, that I may celebrate my victory!

I am not quite sure how it worked, but it worked.
I re-installed SQL Express (Advanced Options), and this time when it asked I
declined the option for NT authentication, so I entered a password for the
sa
account.
I then used the Surface Area configurations to turn on TCP/IP, and CLR.

For some reason, the ODBC wizard then just went through without a problem.

So, Gina, and all you others, thanks for all your help.

Stephen
 
Back
Top