Excel Tip of the Day: How to connect to an external Data source Part I

You can connect Excel to an external data source using a DSN Connection or using a DSN connectionless string.

I will first show you how to connect to a DSN Connection for an Access database

First of all you need to create a DSN Connection. To do that go to

Control Panel  -> Administrative tools,-> Data Sources (ODBC)

The ODBC Data Source Administator Window will pop up.

Then click the Add.. button and the following window will show up (for W7 64bit see here )

Select the drive you need. For this example I am going to connect to the North wind Database.

Scroll down and select the  Microsoft Access Driver. I am picking the second one in the list because I need to connect to an Access 2007 DB.

Click Finish.
This window will show up

Insert the ODBC Data source name and a description for it.
Click Select... and browse to the .accdb/.mdb file that you need to connect to.
You are done with the ODBC connection.

Go to Excel and click and go to

Data -> From Other Sources -> From MS Query,

This action will display the Choose Data Source window.
Select Northwind and click Ok.
Select the Employee Table and few ot its fields
Click Next 3 times
Click Finish
Click OK

You are done!

if you right click on the Table and Select Table -> External Data Properties,

The External Data Property Window will show up.

If you click the property button, which is on the righ side of the Name Text box you will see the Connection Property screen.

Click on the Definition Tab.

If you have a look at the Connection String Text Box, there is "DNS=Northwind", this means that the Query is using a DSN Connection to connect to Northwind

In the Next Tutorial, I will show you how to create DSN Connection less connections.

Personally I normally use DSN Connection when I am connecting to External Data Source (Access, SQL Server) from Excel, DSN Connectionless when I am connecting to Excel as an external data source (This is an advanced topic I will cover in a future post) and the JET.OLDE.DB provider when connecting using VBA (This will be part of another post).

As usual please leave a message if you like the post or have any comment.