Data Management

Configure IT Quick: Configure Access and SQL ODBC connections for user database access

Provide a common interface to multiple types of relational database systems with ODBC technology.


Most IT pros maintain at least one database containing information important to the business they support. You may need to share that database with other users or enable other applications to query the data. For example, let’s say you’ve created a database using Microsoft Access, but you have a non-Microsoft application that needs to pull data from it. Maybe you need to make a database available from the Internet, but you’re not sure how to go about it. Or, maybe you need to pull SQL data into Microsoft Access. In all of these situations, ODBC is the tool you need to make it happen. In this Daily Drill Down, I’ll explain what ODBC is all about and how you can put it to work sharing databases with other users and applications.

ODBC simplified
ODBC stands for Open Database Connectivity. ODBC is a standards-based technology that provides a common interface to multiple types of relational database systems. ODBC enables applications to query data from hosted databases without requiring that the application support the database management system (DBMS) that is hosting the database. For example, assume your company maintains an extensive Oracle database of customer information. Your Web application developers need to design an intranet site that lets sales staff view and modify the contact database. In another division, you have an IBM AS/400 running IBM’s DB2 that hosts an inventory control database. Your Web developers must also integrate that database into the intranet site to give the sales staff access to current inventory levels. Without a common interface to the databases, the developers would have to create code specific to both Oracle and DB2. But with ODBC, they can write code to a single standard and framework.

ODBC is also useful in situations where the data access needs aren’t quite so complex. For example, you can use ODBC to link a product database created with Microsoft Access from a server into users' own local databases.

To draw a network analogy, ODBC enables a computer to act as a database gateway. The actual data might reside locally on the computer or on a remote system, including back-end AS/400, AS/36, or UNIX platforms. Clients then access the database through the ODBC server. Here’s how it works: The server hosting the ODBC connection runs a database-specific ODBC driver. In the dual-database example I cited above, the server would run an Oracle ODBC driver and a DB2 ODBC driver. Each driver is configured with a Data Service Name (DSN) that defines the connection to the target database. The DSN specifies the database location, logon information (if any), and other properties, such as connection pooling, buffer size, and read/write state. Clients and client applications can then use standard SQL queries against the ODBC connection. The driver takes care of querying the database using its native query requirements, translating to and from the SQL queries as needed.

How you make the connection to the database from the ODBC server depends largely on the type of database and server on which it is hosted. For example, the server hosting the DSN might also host the database. In this case, the DSN references the local database. In another example, you might need to tie into a DB2 database running on a back-end AS/400. In this case, you’ll need the ODBC driver for DB2, which is included with Microsoft’s Host Integration Server (HIS).

Note
In addition to supporting connections to DB2, HIS provides several other services to enable client systems to use data stored on host servers.

Where you put the DSN depends in large part on where the database is located and how you deploy access to the database. For example, if you’re making the database available as part of an intranet or Internet site, the DSN resides on the Web server. Users who want to share their local databases with others can set up the DSN on their workstation, turning that workstation into a database server of sorts.

The management interface for ODBC is much the same between Windows NT Server, Windows 2000 Professional and Server, Windows XP, and Windows .NET. There are some minor differences, depending on the version of Microsoft Data Access Components (MDAC) installed on the computer, but by and large, the main features are the same.

Configuring ODBC connections
You configure and manage ODBC connections through the ODBC Data Source Administrator management application. Even in Windows 2000/.NET and Windows XP, which use MMC consoles for most management tools, the ODBC Data Source Administrator is a stand-alone application. You’ll find it in the Administrative Tools folder.

The Administrator applet is a multitabbed dialog box that lets you create, modify, and remove DSNs, configure drivers, and manage advanced features, such as connection pooling and tracking. The first three tabs—User DSN, System DSN, and File DSN—are essentially identical. They let you create DSNs for target databases. However, the types of DSNs have different scopes.

A user DSN is user specific, and it's only available from the computer where it's created. For example, assume user Joe creates a user DSN for a database on a computer he shares with Jane. When Jane logs on, she can’t see or use Joe’s DSN, but she can create her own user DSNs. Target databases for user DSNs must be local to the computer where the DSN is defined. Windows stores user DSNs in the HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data Sources registry key.

A system DSN is available to all users on the local computer and to all services on the computer, such as IIS. If you’re setting up an ODBC connection on a Web server, for example, you would set it up as a system DSN, enabling IIS to access the connection to query the target database. Target databases for system DSNs must be local to the computer where the DSN is defined. Windows stores system DSNs in the HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data Sources registry key.

The third type of DSN, file DSNs, are not specific to users, nor must the database be local to the computer where the DSN is defined. File DSNs are stored in a text file, and the contents of the text file define the data connection. The following is the contents of a sample file DSN:
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=I:\Inetpub\databases
DBQ=I:\Inetpub\databases\products.mdb


File DSNs are stored only as files and are not added to the registry. The default location is \Program Files\Common Files\ODBC\Data Sources. The advantage of a file DSN is that you can deploy it to multiple workstations to enable those systems to access the underlying target database. So, rather than create a system DSN on several hundred workstations, you only need to deploy the file to those workstations, or you can even place it in a shared network folder accessible to all clients that need to use it. For example, assume several Excel users need to import data from a shared database into their monthly reports. You'd create a file DSN on a system and then place it in a network share. A user would open Excel, choose Data | Import External Data | Import Data, and browse to the folder containing the DSN. When the user opens the DSN, Excel uses the information contained in the file to determine how to connect to the database. Excel then displays the tables stored in the database, enabling the user to select and import the data.

DSN setup
In general, setting up a DSN is easy, but it can get a bit more complex, depending on the target database type. For example, setting up a DSN for a SQL Server database is a lot more complex than setting up one for an Access database. Even so, you shouldn't have much trouble doing it once you have a few under your belt.

First, decide where you need to create the DSN and what type it should be. Second, open the ODBC Data Source Administrator from the Administrative Tools folder. Take a look at user and system DSNs first by clicking either the User DSN or System DSN tab (the process is the same for both). Finally, click Add, select the driver for the ODBC connection based on the target database management system, and click Finish.

What you see at this point depends on the ODBC driver. Some drivers, like Microsoft Access or dBase drivers, display a dialog box that gives you access to all of the options and settings for the connection. Others, such as the SQL Server driver, use a wizard to step you through the process of defining the connection settings. Rather than cover every possible driver, I’ll cover two different drivers to give you a broad overview of the process and types of settings you’re likely to run across, plus point you to a third type you might find interesting.

Configuring an Access DSN
The Microsoft Access ODBC driver displays a dialog box in which you enter the connection settings. Clients use the Data Source Name to connect to the database. Although you can specify a name containing spaces, I recommend you use a single word or initial cap letters, such as NewProductsDB, for the DSN. I’ve had problems with some development platforms connecting to a DSN containing spaces. The Description is optional, but it can help you identify a DSN on systems where there are several.

Click Select in the Database group to select an existing Access database file, or click Create to create a new, empty database. You can use the Repair and Compact buttons to fix or compact a database.

The System Database group lets you select a secondary database to control access to the target database. Access supports a workgroup information file, which specifies the users who can open a database and the permissions they have within the database. You use the Tools | Security menu in Access to create the workgroup information file and define users, groups, and permissions. If you don’t specify a system database for an Access database, the ODBC driver allows access to the database without prompting for a user name or password, granting all connections the level of access defined by the Users group in the workgroup. If you want the ODBC driver to prompt the user for the logon information and apply permissions according to the workgroup information file, click Database in the System Database group, then click the System Database button and select the workgroup information file for the database.

Click Options to expand the dialog box if you want to set the following settings:
  • Page Timeout—Sets the length of time in tenths of a second that an unused page remains in the buffer.
  • Exclusive—Select this option to open the database in exclusive mode locked for a single user; clear the option to open the database in shared mode.
  • Read Only—Select this option to prevent changes to the database.
  • Buffer Size—Sets the buffer size for disk I/O. Use a larger value to increase performance (uses more memory).

Finally, click Advanced to set advanced options for the Access driver. The Set Advanced Options dialog box lets you set the user name and password to be used to connect to the database. You should always specify a user name and password for security reasons; some development platforms have problems connecting if you don’t use a login name and password. Use the Options group to set a handful of additional options for the connection. In most cases you won’t need to change the default values. Click Help for a description of each setting.

Configuring a SQL Server DSN
The SQL Server ODBC driver uses a wizard to step you through the process of creating the DSN. The first page of the wizard prompts for the DSN name, optional description, and the server. You can select the server from the drop-down list or enter the name. Select or enter Local to reference SQL Server running on the local computer. If there's more than one instance of SQL Server running on the computer, enter the server name in the form  ServerName\Instance.

The next page of the wizard prompts for authentication and network configuration properties:
  • With Windows NT Authentication Using The Network Login ID—The SQL Server ODBC driver requests a trusted connection to the server, using the current client logon user name and password to authenticate the request. The credentials must have an association on the SQL Server to a SQL Server login ID.
  • With SQL Server Authentication Using A Login ID And Password Entered By The User—The driver requires the user to provide a SQL Server login ID and password for all connection requests.
  • Connect To SQL Server To Obtain Default Settings For Additional Configuration—Select this option to have the driver connect to the specified SQL Server for additional configuration settings when you click Next. Clear this option if you want to use default settings and not have the driver connect to the server to obtain the settings.
  • Login ID/Password—Specify the SQL Server login ID and password to be used to connect to the server to obtain configuration settings. This ID is not used for client connections.
  • Client Configuration—Click to display the Add Network Library Configuration dialog box, which lets you select the network connection mechanism and associated settings needed to connect to the server. For example, you might choose TCP/IP and specify the server name and port number for the connection.

The next page of the wizard lets you configure a handful of options that determine how the driver makes and uses the connection. These settings include:
  • Change The Default Database To—Choose a database from the drop-down list to override the default database for the specified login ID. Clear this option to use the default database defined for the login ID on the SQL Server.
  • Attach Database Filename—Specify the full name and path of the primary file for an attachable database, which the driver uses as the default database for the data source.
  • Create Temporary Stored Procedure For Prepared SQL Statements And Drop The Stored Procedures—Direct the driver to create temporary stored procedures to support the SQLPrepare ODBC function.
  • Only When You Disconnect—Have the driver drop stored procedures for the SQLPrepare function only when the SQLDisconnect function is called. This reduces overhead and improves performance, but it can lead to a buildup of stored procedures. This is a problem for applications that issue a lot of SQLPrepare calls or that run for a long time without disconnecting.
  • When You Disconnect And As Appropriate While You Are Connected—Direct the driver to drop stored procedures when SQLDisconnect is called; when DQLFreeHandle is called for the statement handle; when SLPrepare or SQLExecDirect are called to process a new SQL statement on the same handle; or when a catalog function is called. This option adds more system overhead, but it prevents a buildup of stored procedures.
  • Use ANSI Quoted Identifiers—Enforce ANSI rules for quote marks so that they can only be used for identifiers, such as in table and column names, and require strings to be enclosed in quotes.
  • Use ANSI Nulls, Paddings, And Warnings—Turn on ANSI_NULLS, ANSI_PADDINGS, and ANSI_WARNINGS options when the connection is made to the database.
  • Use The Failover SQL Server If The Primary SQL Server Is Not Available—If the primary server becomes unavailable, the driver attempts to reconnect to the primary SQL Server; failing that, it attempts to connect to the failover server. The failover server must be defined at the SQL Server.

The final page of the wizard sets a handful of miscellaneous options for the connection:
  • Change The Language Of SQL Server System Messages To—Set the language for system messages for the connection on a SQL Server on which multiple languages are installed.
  • Perform Translation For Character Data—Convert extended ANSI characters between the client and server using Unicode.
  • Use Regional Settings When Outputting Currency, Numbers, Dates, And Times—Format these types of data using the client’s regional settings.
  • Save Long Running Queries To The Log File—Queries that take longer than the amount of time specified by the Long Query Time value are logged to the specified log file. Clear this option to prevent logging.
  • Long Query Time (Milliseconds)—Set the query time threshold.
  • Log ODBC Driver Statistics To The Log File—Specify the location of the log for driver statistics and enable logging.

Text DSNs
One generic DSN type you might want to explore is the Microsoft Text ODBC driver. With this driver, you can set up an ODBC connection to one or more text files, which can be a very useful tool for integrating data you might otherwise have no means for accessing through ODBC. For example, assume you have an application for which an ODBC driver isn’t available, but the application can export to a comma-delimited file. You can export the data and then create an ODBC connection to the text file. The driver lets you specify the directory location, file types to include, and how fields in the files are mapped to data types.

Tracing and pooling
The Trace tab lets you perform tracing for ODBC to troubleshoot connection or client problems. When you turn on tracing, the ODBC driver logs actions to the file that you specify. You can use a text editor such as Notepad or WordPad to view the file.

Use the Connection Pooling tab to specify which device drivers can pool connections, or reuse open connection handles. Connection pooling can improve performance by reducing the need for client applications to establish connections, which reduces server overhead. Windows pools Oracle and SQL Server connections by default. To configure pooling, double-click a driver in the list to open the Set Connection Pooling Attributes dialog box. If you enable pooling, you can also specify the number of seconds that unused connections remain in the pool.

You can also enable or disable counters in Performance Monitor for connection pooling through the Connection Pooling tab. By enabling the counters, you can monitor the pooled connections through the Performance Monitor, which you’ll find in the Administrative Tools folder. The last option on the Connection Pooling tab specifies the amount of time in seconds that Windows waits after a failed or lost connection before retrying the connection.

Using ODBC connections
You have several options for actually using ODBC connections, and I won’t go into all the possibilities here. However, end-user applications like Word, Excel, and other Office components can query ODBC connections, and this capability isn’t limited to Microsoft applications. Many Web development environments, such as Macromedia UltraDev and Microsoft FrontPage, provide development tools that make it easy to establish an ODBC connection and view or modify a database. You can also write applications in C, Visual Basic, ASP, and other environments that tie into ODBC connections.

If you’re interested in exploring ODBC integration in your favorite development environment, search the support documentation for references to ODBC. You’ll likely find that integrating a database into your application or Web site isn’t as difficult as you thought, thanks to ODBC.

Editor's Picks