Appendix C Data Connection Basics |
- SQL Server Databases
- Microsoft Access Databases
- Microsoft Excel Services
- Microsoft Excel Workbooks
And there are a host of others not mentioned.
Why do we continually create and maintain external data connections? There are benefits to maintaining data connections, some of which include:
- Analyzing data without having to repeatedly re-copy data to an Excel workbook saves time, commands a high convenience factor, and reduces cost. This is a rather significant benefit since due to very large datasets, recopying data can be potentially very time-consuming and a number of errors are possible.
- Creating and maintaining data connections is a process that can be and is very duplicatable, reducing development time and cost.
- Connection information is stored in the workbook and can also be stored in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).
ODBC and OLE DB Components (Open DataBase Connectivity and Object Linking and Embedding)
To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.
To see a complete list of ODBC providers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.
You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database, or contact your database vendor.
Using ODBC to connect to data sources
To connect to ODBC data sources, do the following:
Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.
Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the registry or a DSN file, or a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.
To define a data source, in Windows click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. Click Performance and Maintenance, click Administrative Tools. and then click Data Sources (ODBC). For more information about the different options, click the Help button in each dialog box.
Machine data sources
Machine data sources store connection information in the registry, on a specific computer, with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer.
A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.
File data sources
File data sources (also called DSN files) store connection information in a text file, not the registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer with the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.
A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.
Using OLE DB to connect to data sources
In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Excel), and the program that allows native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).
A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:
In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider.
Create a blank text file with a .udl file name extension, and then edit the file, which displays the Data Link Properties dialog box.
Key points about data connections.
- There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Access, other OLAP and relational databases, Excel workbooks, and CSV/text files.
- Many data sources have an associated ODBC driver or OLE DB provider.
- A connection file defines all the information that is needed to access and retrieve data from a data source.
- Connection information is copied from a connection file into a workbook, and the connection information can easily be edited.
- The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.
Export Connection File
|
2. Select the data connection file; right-button mouse click it and select “Edit Connection Properties”(top-left graphic)
|
 |
|
3. In the Query Properties dialog box, select the “Definition” tab and select the “Export Connection File” button at the bottom of the dialog box. (top-right graphic)
|
 |
|
4. Save file to the “Documents/My Data Sources” subfolder or save to a Network folder. (bottom graphic)
|
 |