Fast and Flexible Access to Databases

Microsoft's ODBCDirect offers programmers better ways of processing ODBC data sources.

Rick Dobson

The crown jewels of your company's data will often be in back-end databases, such as Oracle, SQL Server, and Sybase. Microsoft Office 97's Data Access Objects (DAO) introduces a new technology--ODBCDirect--that manages interactions with back-end databases. Besides Microsoft's Access 8 database, you can use this technology from within other applications, such as Excel 8, Visual Basic 5, and Visual C++ 4.2 and 5.0. Third-party database applications that use Visual Basic for Applications can r eadily access ODBCDirect's functions through DAO. Developers must have a license for Jet, the traditional ODBC database engine that ships with Access and uses DAO as its programming interface. Database development products such as Powersoft's PowerBuilder use their own interfaces to back-end databases and thus do not need DAO.

ODBCDirect offers several advantages over Jet. First, when working with an application other than Access, developers can use familiar DAO code without loading Jet. Second, developers can create stored procedures and run them on a back-end server. Third, asynchronous queries do not "freeze" a local workstation while the back-end server processes th e query. Fourth, developers can speed performance and reduce network traffic by building applications that cache changes locally and update the back-end server in one batch.

ODBCDirect Object Model

The ODBCDirect object model has two types of objects. One group manages connections to back- end databases, while the second set processes objects in a database, as shown in the figure "ODBCDirect Object Model."

The top or root DAO object is DBEngine . You create either ODBCDirect or standard Jet workspaces from this object, where a workspace corresponds to a session. Each session can contain one or more databases and connections, and a procedure can consecutively open multiple sessions. Each workspace type has its own object model. DBEngine's Errors collection permits custom procedures to override system responses to run-time errors. (A collection is a group of like objects.)

Procedures can connect to back-end databases through either a Database or a Connection object, but connections offer three advantages. First, Connection objects permit asynchronous operations. Second, Connection objects permit the use of querydef objects. Third, ODBCDirect querydefs enable client workstations to create and run server-side stored procedures und er program control.

Recordset collections belong to either Connection or Database objects. A recordset represents a return set (a table of data) from a query. Recordsets contain collections that comprise all the fields in a recordset . You use the OpenRecordset method of a Database , a Connection , or a querydef to create a recordset and append it to the Recordset collection. You can also use a Jet workspace recordset that refers to a local base table in an ODBCDirect workspace.

Querydef collections also belong exclusively to connections in ODBCDirect workspaces. This collection contains all the querydefs in a Connection object. ODBCDirect querydefs can contain parameters for dynamically setting criteria. Note that Querydefs in ODBCDirect workspaces do not have field collections. Use a querydef 's OpenRecordset method to view its return set.

Connecting to the Database

Bef ore using ODBCDirect, you must register your back-end database. You can accomplish this manually with the 32-bit ODBC icon in the Control Panel, or programmatically with the RegisterDatabase method of DAO's DBEngine object.

After registering your ODBC data source, you can connect to it with just two steps. First, establish a Workspace . Second, create either a Connection or a Database object. The CreateWorkspace method creates DBEngine workspaces. Set this method's type argument to dbUseODBC to open an ODBCDirect workspace. Alternatively, set DBEngine's DefaultType property to dbUseODBC . This causes the CreateWorkspace method to generate ODBCDirect workspaces without a type argument.

The second step involves establishing a link to a back-end database via a workspace's OpenConnection or OpenDatabase methods. Since the OpenConnection method generates a more flexible outcome, developers will often prefe r it.

OpenConnection takes four arguments. The name argument defines the connection's name property. The options argument accepts constants that specify if the connection will be asynchronous, and to determine the types of prompts permitted during an ODBC link attempt. The readonly argument accepts a Boolean value designating whether the link is read-only. The connect argument specifies the information that the ODBC driver needs to make the link, such as back-end database name, user's server ID, user's password, and data source name (DSN).

Access Forms

"Using ODBCDirect" presents a pair of procedures that work together to update an Access form based on ODBCDirect processing. The first, AuthorTitleCount , conducts a pair of queries against the Pubs database that ships with SQL Server. It transfers the results to a table that serves as the record source for an Access form. The second cmdComputeIt_Click event procedure launches AuthorTitleCount and moves its form to the record storing return values from the two back-end queries. Clicking the cmdComputeIt button invokes cmdComputeIt_Click .

AuthorTitleCount starts with an On Error statement to trap errors followed by a series of variable declarations. After the declarations, the code establishes an ODBCDirect link to the Pubs database. It creates a workspace with the CreateWorkspace method. Notice the listing sets the last argument to dbUseODBC . Next, it uses the OpenConnection method to link the workspace to the Pubs database. The stConnect string sets the connect parameters for the back-end database. Pubs is the DSN name for the Pubs database.

The subsequent block of code computes the number of authors in the Authors table and the number of titles in the Titles table. The outcomes are stored in two recordsets. The next code segment transfers the results from the temporary recordsets to a recordset based on a local table, tblPubs . This segment also time-stamps the transfer. The local table is the record source for the form with the command button that launches AuthorTitleCount . The final code block before the error trap procedure closes both the connection and the workspace, freeing those resources for use by others.

The error routine (not shown) traps two common conditions, and it presents a helpful hint to resolve the problem. (The complete listing "odbc1lis.txt" can be downloaded from the BYTE Site at http://www.byte.com/art/download.htm .) The routine also traps other errors and writes their number and description to the debug window. This avoids an abnormal end that can lock up the local workstation.

Now that we've got the basics down, next month I'll demonstrate ODBCDirect's more advanced features.

---

Using ODBCDirect

Manage ODBCDirectLink


Public Sub AuthorTitleCount()
On Error GoTo _
  AuthorTitleCount_Trap
Dim wspPubs As Workspace
Dim conPubs As Connection
'Other variable declarations
'   omitted to save space
Set wspPubs =
   CreateWorkspace _
   ("PubsSession", _
   "admin", "", dbUseODBC)
stConnect = "ODBC;DSN=Pubs; _
UID=sa;PWD=;" & _
    "DATABASE=Pubs"
Set conPubs = _
  wspPubs.OpenConnection("", _
  dbDriverNoPrompt, True, _
  stConnect)
stASQL = "SELECT _
  Count(Authors.Au_id)" & _
  "'AuthorCount' FROM Authors"
stTSQL = "SELECT _
Count(Titles.Title_id)" & _
 "'TitleCount' FROM Titles"
Set qdAuthors = _
 conPubs.Create
querydef
("", _
 stASQL)
Set rsACount = _
  qdAuthors.OpenRecordset
Set qdTitles = _
  conPubs.Create
querydef
("", _
  stTSQL)
Set rsTCount = _
  qdTitles.OpenRecordset
Set dbsMyDb = Cur
rentDb
Set rsPubs = _
  dbsMyDb.OpenRecordset( _
  "tblPubs")
rsPubs.AddNew
    rsPubs![AuthorCount] = _
      rsACount![AuthorCount]
    rsPubs![TitleCount] = _
      rsTCount![TitleCount]
    rsPubs![Date] = Now()
rsPubs.Update
AuthorTitleCount_Exit:
conPubs.Close
wspPubs.Close
Exit Sub


Call ODBCDirect Sub  and Update Form


Private Sub _
  cmdComputeIt_Click()
AuthorTitleCount
Me.Requery
DoCmd.GoToRecord , , acLast
End Sub

---

ODBCDirect Object Model

illustration_link (10 Kbytes)

Some ODBCDirect objects allow asynchronous operations and access to server-side scripts.

---

Rick Dobson, Ph.D. , is president of CAB, Inc., a database and Internet development consultancy. You can send e-mail to him at Rick_Dobson@msn.com .