Link SQL Server table With Access data

P

Prince

Hi,

Please advise easiest way to link SQL server table with Access table. - thanks

Aa
 
T

Tom Wickerath

First, create a DSN, using the Administrative Tools | Data Sources (ODBC)
applet in the Control Panel. A System DSN will allow any user who logs onto
the PC to use the DSN. After clicking the Add button, scroll to the bottom of
the list for SQL Server driver. Finish the wizard steps to generate your DSN.
Make sure to test it at the end (there is a Test button).

Then, back in Access, use File > Get External Data > Link Tables...
In the Files of Type dropdown, at the bottom of the screen, select ODBC
Databases. Select your new DSN. You should be able to link to existing
tables and views at this point.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

Prince

Hi,

Thanks for reply, I am trying same stuff but getting errors please see below:
Using windows authentication & click check box ("Connection to server ......")

'''''''''''' ERROR '''''''''''''''''''''''''''''''''
Connection failed:
SQL State: '01000'
SQL Server Error : 2
[Microsoft][ODBC Server Driver][Shared Memory]Connection Open (Connect())
Connection Failed
SQL State: 08001'
SQL Server Error: 17
[Microsoft][ODBC Server Driver][Shared Memory]SQL Server does not exist or
access denied.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Please advise - thanks

AA
 
T

Tom Wickerath

Hmmm....it sounds like you have done everything right. Did you have SQL
Server running at the time? I have a Word document that includes a
step-by-step procedure, complete with images, that I can make available later
on tonight. (I'm currently at a PC that does not have FTP software available
for me to use).

You might want to try posting your question in a SQL Server newsgroup,
instead of waiting for me to post my Word doc. later on tonight. Also, which
version of SQL Server are you using. The document that I have shows how to
create a DSN for SQL Server 2000. I honestly don't know if there are any
differences in the ODBC wizard for other versions.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Hi Prince,

Here is a zipped copy of the Word document I mentioned earlier that you can
download:

http://home.comcast.net/~tutorme2/samples/configDSN.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom Wickerath said:
Hmmm....it sounds like you have done everything right. Did you have SQL
Server running at the time? I have a Word document that includes a
step-by-step procedure, complete with images, that I can make available later
on tonight. (I'm currently at a PC that does not have FTP software available
for me to use).

You might want to try posting your question in a SQL Server newsgroup,
instead of waiting for me to post my Word doc. later on tonight. Also, which
version of SQL Server are you using. The document that I have shows how to
create a DSN for SQL Server 2000. I honestly don't know if there are any
differences in the ODBC wizard for other versions.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Prince said:
Hi,

Thanks for reply, I am trying same stuff but getting errors please see below:
Using windows authentication & click check box ("Connection to server ......")

'''''''''''' ERROR '''''''''''''''''''''''''''''''''
Connection failed:
SQL State: '01000'
SQL Server Error : 2
[Microsoft][ODBC Server Driver][Shared Memory]Connection Open (Connect())
Connection Failed
SQL State: 08001'
SQL Server Error: 17
[Microsoft][ODBC Server Driver][Shared Memory]SQL Server does not exist or
access denied.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Please advise - thanks

AA
Click to expand...
Click to expand...
 
P

Prince

Thank you so much for your reply and interest, it didn't work I am following
the same steps which you provided, don't you thing it's access problem
becasue at the end it is saying that ".... SQL Server doesn't exist or access
denied".

Please advise - thanks.

Amir
 
T

Tom Wickerath

Hi Amir,

It will be very helpful if you can answer some of the questions I have
already asked. These include:

1.) Which version of SQL Server are you using?
2.) Do you have SQL Server running when you attempt to create the DSN?

Here are some new questions as well:

3.) Are you able to open Enterprise Manager (or whatever the new name is, if
you are using SQL Server 2005)? Are you able to the tables for the database
in question, that you are wanting to create a DSN for? These actions would
not require that a DSN be in place.

4.) Do you have the latest MDAC (Microsoft Data Access Components) release
installed? This would be version 2.8.

5.) Try downloading the MDAC Component Checker from Microsoft and running it:

http://support.microsoft.com/?id=307255

Which version does it report back? Does it report any problems?

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

P

Prince

Hi Tom,

Thanks for help, that was a silly mistake, Server name was wrong now it is
working - thanks for your help.

Amir
 
D

db2nd

Is there any way to create this link without having the user create the DSN
using the admin tools? My hope was they could access the sql table (view
only) without manually creating the DSN.

Thanks for your help.
 
Top