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.
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.
Last Comment
kmslogic
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.
ASKER
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This solution would work but the data vendor only gives us access to views.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
