Avatar of DB-aha
 asked on

How to access a machine data source in SQL Server 2008

I am currently using access 2010 to get data from an external source. The steps I take to link to the external views provided to us are the following:

1. Click on the External Data Tab
2. Click on ODBC Database
3. Select Link to the data source by creating a linked table
4. Click ok
5. Click on the Machine Data Source  Tab in the pop up that appears
6. Select the data source I want to connect to
7. A list of views is presented to me to link to.

Now I would like to replicate this in SQL Server 2008 express edition as I believe I can do better with my queries in SQL server than being stuck in access 2010. Please provide the steps needed to be able to get to the same data source I am acessing in access but instead in SQL Server 2008. Thanks in advance.
Microsoft AccessMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment

8/22/2022 - Mon

Well you can create a machine level data source for your SQL Server instance (go to the Machine Datasource tab, click on Add, Select the SQL Server Client and fill in the resulting dialog with the connection settings you need to connect to your instance) then just follow your above steps to link tables.

kmslogic, I would need you to walk me through the process. The only way I can view any data sources that I know  of is to try to import from a source and that way I do not see an option to add. The connection would be from a remote MySQL connection to SQL Server 2008 express which I have installed locally.

... How are you linking MySQL to SQL Server 2008 express and why don't you just install the MySQL ODBC Driver and link the tables directly from MySQL?

Assuming you have the 32bit version of Office installed (which is normal even for 64bit windows installations) you'll have to find the 32bit ODBC Administrator which is in C:\Windows\System32 and named odbcad32.exe -- you run that program to add and configure new ODBC data sources (I usually find it in Explorer and then right click on it / Send To->Desktop (create shortcut) so I have a shortcut to run it when I need it.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

I have not connected MySQL  to  SQL Server yet, that is the goal here. The ODBC driver is apparently being used currently in my ACCESS 2010 installation because I can see and access all of the MySQL views through it. The driver seems to be available when I go to Control Panel > Administrative Tools > ODBC connection, and I see the connection in Access but not in SQL Server 2008.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

This solution would work but the data vendor only gives us access to views.