 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Link SQL Server table With Access data
Hi, Please advise easiest way to link SQL server table with Access table. - thanks Aa >Hi, > >Please advise easiest way to link SQL server table with Access table. - thanks > >Aa Probably an ODBC connection, though there are several options. See http://www.granite.ab.ca/access/sqlserverupsizing.htm for a multitude of links to the various choices, and some very thoughtful discussion. John W. Vinson [MVP] 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 __________________________________________ > Hi, > > Please advise easiest way to link SQL server table with Access table. - thanks > > Aa 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 > 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 [quoted text clipped - 18 lines] > > > > Aa 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 __________________________________________ > Hi, > [quoted text clipped - 17 lines] > > AA 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 __________________________________________ > 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 [quoted text clipped - 35 lines] > > > > AA 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 > 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 [quoted text clipped - 18 lines] > > > > Aa 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 __________________________________________ > 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 [quoted text clipped - 4 lines] > > Amir Correction: My question #3 was missing the word "open": Are you able to the tables for the database in question, that you are wanting to create a DSN for? Should have read: Are you able to open the tables for the database in question, that you are wanting to create a DSN for? Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hi Amir, > [quoted text clipped - 24 lines] > https://mvp.support.microsoft.com/profile/Tom > http://www.access.qbuilt.com/html/expert_contributors.html Hi Tom, Thanks for help, that was a silly mistake, Server name was wrong now it is working - thanks for your help. Amir > Correction: My question #3 was missing the word "open": > [quoted text clipped - 40 lines] > > https://mvp.support.microsoft.com/profile/Tom > > http://www.access.qbuilt.com/html/expert_contributors.html Ah, that will do it! Thanks for reporting back with your finding. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Hi Tom, > > Thanks for help, that was a silly mistake, Server name was wrong now it is > working - thanks for your help. > > Amir > Hi Tom, > [quoted text clipped - 53 lines] >> > https://mvp.support.microsoft.com/profile/Tom >> > http://www.access.qbuilt.com/html/expert_contributors.html 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. > 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 [quoted text clipped - 18 lines] > > > > Aa The DSN has to be created only once and then you can link to any tables that the user has permission to query, etc. But unfortunately Access needs the DSN in order to make the connection across platforms. If you have an IT technician, he/she can just go to the PC and set up the DSN only once. I agree, the user shouldn't have to do that. HTH, Paul. > Is there any way to create this link without having the user create the > DSN [quoted text clipped - 29 lines] >> > >> > Aa |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.