How to access a machine data source in SQL Server 2008

DB-aha
DB-aha used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.

Author

Commented:
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.

Commented:
... 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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
Commented:
Sorry I think I understand what you are going for, but I don't think you're going to be able to do what you want in SQL Server 2008 Express.  Technically it is possible to create a linked server between SQL Server and another database, but even if you can do that in the Express version you won't achieve anything useful--the queries will just be passed through to the MySQL system, so you'll be getting essentially the same thing as linking the MySQL tables in Access only with more work on your part--you're just adding another layer to the issue.

If you are only running queries, you might consider using the MySQL Workbench tool to connect to the remote MySQL server and run queries...

http://dev.mysql.com/downloads/workbench/

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial