Chapter 10

Linking Databases to Other Programs

A database is a repository of systematically organized information. Applications that use database systems hide the database structure from users and instead present logically ordered information. Often, external (nondatabase) applications that do not have database access, such as a word processor or spreadsheet, must retrieve database information. How can you provide data to such applications while hiding database-access techniques from the application integrator? The answer lies in creating distributed applications that you can easily integrate into external applications.

In this chapter, you learn about the following:

• Using DDE to share data among applications
• Creating programmable applications with OLE Automation
• Choosing between DDE or OLE
• Understanding architectural concerns related to data sharing
• Database-enabling other applications

Creating Distributed Applications and Systems Integration

Distributed applications enable you to divide complex computing tasks into separate processes that can run on the same or different machines. Creating distributed applications requires a different set of design and coding skills than creating stand-alone applications. Knowledge of distributed architectures, interprocess communications, and systems integration is essential.

To create distributed applications, you need a thorough understanding of the operating environment. The Windows operating systems provide several interprocess communications (IPC) facilities that applications can use, including Dynamic Data Exchange (DDE) and Object Linking and Embedding (OLE). Some of the operating systems (Windows for Workgroups, Windows NT, and Windows 95) enable you to use DDE across the network with NetDDE. Soon, you will also be able to distribute OLE across the network.

The Distributed Operating Environment

In today's operating environments, no application stands alone. In typical operating environments, users often work with many applications simultaneously, frequently with data common to some or all applications. Microsoft has introduced many technologies into their operating systems to facilitate the integration of data sharing and application interoperability.

These technologies have moved the desktop from an application-centered to a data-centered mode of operation. No longer do you use monolithic applications for all tasks. Now, more dedicated applications share and move information seamlessly among one another. These applications form a distributed operating environment. In layman's terms, a distributed environment is one in which you use many applications to accomplish a task. A simple example of a distributed environment is one in which you fax a word processing document. You use the word processor to create your fax, and the word processor's print menu sends the document to a background fax spooler, which sends the document to its final destination.

Database applications fit naturally into the distributed operating environment. You can employ many methods to share such applications' data. Many users can open and use the same data in a file server environment, in which the database tables reside on a common server and many instances of an application access them. The client/server environment is inherently distributed, as a client application accesses a database server for information.

Windows incorporates many technologies that enable interprocess communication among applications. The interprocess capabilities enable your applications to share data with other applications. The interprocess communication capabilities also enable you to build business solutions by integrating many applications, thus letting your application become part of the distributed environment. These technologies include OLE, DDE, OLE Automation, and Remote Procedure Calls (RPC).

Database-Enabling Other Applications

For a database application to share information with an external application, the two applications must communicate. A typical database application resides on a local computer or on a local area network (LAN). To database-enable an external application, you must establish an interprocess communication protocol between the two applications. The Windows family of operating systems provides several methods of interprocess communication (IPC) to data-enable other applications. These methods include Dynamic Data Exchange (DDE) and Object Linking and Embedding (OLE).

Each of these protocols makes your application programmable from external applications. Because the Windows operating systems are multitasking (capable of running many programs simultaneously), program interoperability is crucial to a user-friendly operating environment. In the days of MS-DOS, you could run only one program at a time, so interprocess communications were not very important. Today, your applications must employ an IPC mechanism to provide users with a powerful programming tool that they can use to solve their computing problems.

Systems Integration: A New Type of Computing Problem

The distributed operating environment creates a new problem that didn't exist in the application-centered environment: systems integration. Systems integration involves creating an environment in which, ideally, all applications run together and share information among each other. Unfortunately, this environment is easier to create on paper than in a real computing environment.

In the real world, claims that an application is "OLE-enabled" or "supports DDE" don't carry the same meaning from vendor to vendor. The meaning of such claims changes even from application to application. Although this chapter doesn't solve the problem of application coexistence, it does describe some techniques that you can employ in your applications to make them "well-behaved" Windows applications.

Architectural Concerns in Exposing the Database to Other Applications

When exposing your database to other applications, be particularly careful that you do not compromise your database's integrity. After all, a database is only as good as the information that it contains.

Determining which features of your database application to expose to other applications is often your toughest choice. Keep in mind that your application should contain all the error checking and data dictionary rules to follow for performing database operations. External applications do not contain all of your application's error-checking and referential-integrity features. With this in mind, you should design your application to be an object, or a "black box," to external applications. The black box approach provides an interface for external applications while sheltering the internal implementation details from external applications.

The object or black box concept provides meaningful functionality to other applications without providing direct access to your application's data. By denying such access, you prevent rogue applications from corrupting your data or causing your application to behave incorrectly. Also, by denying direct access to your application's data members, you can change the internal implementation of your application's interfaces. By doing so, you ensure that as you release new versions of your application, it remains compatible with other programs.

Figure 10.1 contrasts the black box concept with direct access to data members.

Fig. 10.1

Implementation differences between black box and direct-access interfaces.

Planning for a Distributed Architecture

When planning for a distributed architecture, one of the foremost decisions that you must make is whether to use DDE or OLE Automation. DDE is an existing protocol that Microsoft is phasing out, and OLE Automation is the new standard that many existing applications do not support.

Implementing a DDE server is a simple and quick easy task that you can accomplish during the design phase of building your applications. You probably should implement such a server to provide backward compatibility with existing programs.

OLE Automation is one of the most important technologies that Microsoft has released. You should provide this support in your applications. Windows 95, Windows NT, and future versions of the Windows operating systems will support OLE and OLE Automation. Implementing these technologies is critical to running on future operating system platforms.

When implementing OLE Automation in your application, it is easier to place your upfront thought in the design and creation of your programmable objects. After designing your objects, incorporate these objects into your application. If you define the interfaces that you are providing to external applications, many of the helper functions that you need for the user interface will reside in your object and can then be used by the user interface forms.

For distributed applications, take special precautions for code maintenance. Your solution depends on many different components, so you must carefully design the interfaces among applications. In a distributed environment, you can easily add functionality to one application and simultaneously break the interface to another.

Using DDE and OLE to Pass Data

The most common form of IPC is to pass data among applications. DDE and OLE both are capable of sharing and passing data, but the mechanics that you use are quite different.

DDE uses shared memory to exchange data among processes. To obtain shared memory, you use the function GlobalAlloc. This function returns a handle to memory of the size specified by the user. When creating shared memory, you must pass the flag GMEM_DDESHARE in a parameter to the function call. This flag informs Windows that many programs besides the one allocating the memory can access the memory block. The GlobalAlloc function then fills the memory block with the data and sends the block to the destination program. Fortunately, the Visual Basic environment handles all the memory management details, so you only see the data passing between programs.

OLE also uses shared memory, but the memory is allocated within the OLE libraries instead of being specified by the sending application. As data passes from the sending program, the OLE libraries perform boundary marshaling across process or DLL boundaries. OLE allocates shared memory for the receiving program and copies the data it is sending into this memory space. The receiving program then gets a copy of the data. Figure 10.2 illustrates OLE data passing.

Fig. 10.2

How OLE passes data between applications.

Dynamic Data Exchange (DDE)

Dynamic Data Exchange (DDE), which Microsoft introduced with Windows 1.0, defines a standard protocol for exchanging data among active applications. DDE was the first IPC protocol implemented in the Windows environment, so most existing programs provide support for DDE. This is in contrast to OLE technologies, which are much harder to implement. Today, most application vendors add OLE support into their applications.

DDE enables your program to be a client, server, or both. Therefore, your application can both provide data to and receive data from other applications. Later you learn how to create DDE clients and servers, but the next section first provides an overview of DDE.

DDE Overview

All DDE connections or conversations require a few basic items: the application, topic, and item. These items establish the protocol used in forming the conversation. In this context, the term application refers to the application name. In Visual Basic, this name is that of the makefile, without the .MAK extension, when running from the Visual Basic development environment, or the name of a compiled executable program without the .EXE extension.

The topic refers to nature of the conversation. Some default conversations are system wide and usually supported by most programs. The most widely available topic is the system topic. Each application creates its own unique conversations. You should document your applications' conversions so that other programs can take advantage of them. The application and topic uniquely identify a conversation.

The item is a reference to data that is meaningful to both applications. Each application should create and document its own items. When an application's control (such as an edit field) is the client (or destination) in a conversation, the control's LinkItem property defines the item. When the control is to be a server (or source), the control's name is used as the item name.

DDE's capability to establish connections automatically is limited. Before you can establish a DDE connection, both the client and server application must be running. Unlike OLE, DDE does not automatically start the server application. If the DDE server isn't already running, the Visual Basic function Shell can start it.

DDE terminology and documentation refer to the server application as the source and the client application as the destination. A conversation starts when a source application receives a request for a topic that it recognizes. After establishing a conversation, neither the source nor destination applications can switch topics. If either the application or topic changes, the conversation terminates.

During a conversation, the applications can exchange information about one or more items. Either the source or the destination can change without affecting the state of the conversation. Combined, the application, topic, and item uniquely identify the data that applications pass between each other.

DDE conversations are often referred to as links, because the source and destination applications are linked by the data that they are exchanging. As table 10.1 indicates, applications can establish three kinds of links. The difference among the links is the manner by which the source updates the destination.

Table 10.1 The DDE Links Available with Windows

Using the DDE Execute command is similar to issuing a function call (or calling a function in a DLL) to the server applications. After establishing a connection, an application can use the Execute command to send command strings.

Table 10.2 lists Visual Basic methods that you can use to manipulate other applications after establishing a conversation. These methods are part of the Visual Basic controls.

Table 10.2 Visual Basic Methods Available for DDE Conversations

Your application must provide handlers for two DDE events: LinkNotify and LinkExecute. The LinkExecute event attaches to a form and defines the set of commands to which your application responds. The LinkNotify event attaches to controls and performs the action required to notify destination applications when data changes within the control.

Today, DDE offers one significant advantage over other IPC technologies: remote or network access to other applications. Starting with Windows for Workgroups, the Windows operating systems include NetDDE, which enables you to establish links to applications running on remote computers. Visual Basic 4.0 offers an implementation of distributed OLE, although distributed OLE will not become a standard part of the Windows operating systems until the Cairo version of Windows NT is available.

Creating a DDE Server

Creating a DDE server application is relatively easy. In the Visual Basic development environment, the project name determines the application name, and if you are running a stand-alone application, the executable name determines the application name. The form that performs the DDE conversation specifies the topic name, which you set in the form's LinkTopic property. You set the form's LinkMode property to 1 (Source), indicating that the application will supply information.

Individual controls that you place on the form (such as text boxes and edit boxes) comprise the topic's items. The control names, which you specify in the name property, identify these items. After you specify these settings, Visual Basic handles all the conversation negotiations.

Most applications that support DDE also provide support for the System topic. You can use this topic to request from the application such information as which topics and data formats the application supports. To support the System topic, create a form and set the LinkTopic property to System. Then create controls on that form that correspond to the various system topics. One of the more typical system items is the topic item, which specifies all the topics to which the application can respond. Table 10.3 describes the common items supported by applications responding to the System topic.

Table 10.3 Available Items Used for System Conversations

In summary, the following steps are necessary to create a DDE server:

1. Set the LinkTopic property in the form that will respond to the conversation topic. This property corresponds to the topic name.
2. Set the form's property to 1 (Source). If you do not set this property, no destination application can initiate a conversation with that form as a topic. All the form's controls are topic items.

At design time, you must set the LinkMode property to 1 (SOURCE). You cannot switch the form from None at design time to SOURCE at run time. If you set the property to SOURCE at design time, you can set it to None and then back to SOURCE.

Creating a DDE Client

Creating a DDE client is also fairly simple in Visual Basic. You can do so either at run time or at application design time. In either case, the rules that you follow are the same.

To establish a DDE conversation, both the client and the server must know the application name, topics, and items. You might be able to query the names of the topics and items that an application supports through the System topic, but when you establish a DDE conversation, you usually do so for a specific purpose. Typically, the names of the topics and items available are queried as a method of error checking to ensure that the server supports the requested conversation.

You specify the application name and topic in a control that you place on a form. To do so, you use a control's LinkTopic property. In Visual Basic, the application name and topic both reside on this code line, which you specify with the pipe symbol, |. For example, to connect to the Address Book application, you set the LinkTopic property as follows:

Control.LinkTopic = "Address | Address"

A control's LinkItem property specifies the item for the conversation defined in the LinkTopic property. The LinkItem property contains the name of the control in the source application from which you want to receive data.

The last item that you must set is the control's LinkMode property. This property specifies how the control is to establish the DDE link. The possible values are None, Automatic, Manual, or Notify. If you set these values at design time, the source application must be running if you want to assign a value other than None. Usually, you set this property at run time rather than design time.

If you use the Notify link mode, you must fill in the control's LinkNotify event to perform an action when the source data changes.

Listing 10.1 establishes an automatic connection to the Address Book application for a Text1 text box. The text box needs to receive information about an employee name.

Listing 10.1 Establishing an Automatic Connection

Text1.LinkMode = vbLinkNone ' terminate any connection
Text1.LinkTopic = "Address|Address"
Text1.LinkItem = "AddressCtl(1)"
Text1.LinkMode = vbLinkAutomatic
     ' Automatic connection to Address Name field

Responding to DDE Execute Commands

After establishing a DDE conversation, you can send commands to the source application to instruct it to perform an action. For example, the Address Book application supports the command string "Find Name," where "Name" is the search criteria for the Address Book application. This support can be useful for updating automatic links in an application based on the results of a database query.

To process DDE Execute, the source form processes the LinkExecute command. This command processes the string that the source application sends. The LinkExecute event acts like a filter because it is responsible for processing all execute commands for the form topic.

Listing 10.2 is the code for the Address Book LinkEvent. This subroutine handles only one event, a database search. LinkEvent parses the string that the event receives and places the string's "Name" portion in the database Name field. The event then searches the database, using the name as the search criteria.

Listing 10.2 The Address Book

Private Sub Form_LinkExecute(CmdStr As String, Cancel As Integer)
    Dim LWord, Msg, RWord, SpcPos   ' Declare variables.
    SpcPos = InStr(1, CmdStr, " ")  ' Find space.
    If SpcPos > 0 Then
        LWord = Left(CmdStr, SpcPos - 1)    ' Get left word.
        RWord = Right(CmdStr, Len(CmdStr) - SpcPos) ' Get right word.
    End If
    If 0 = StrComp(LWord, "Name", 1) Then
      AddressCtl(NAME_FLD) = RWord
      FindFirst_Click
    End If
End Sub

Object Linking and Embedding (OLE)

OLE (Object Linking and Embedding) provides a new method, OLE Automation, that you can use to share data with other applications. OLE Automation creates programmable objects and applications that other applications can call. Visual Basic 4.0 provides the capability both to create a programmable application (OLE Automation server) and to access programmable applications (OLE Automation client).

Using OLE Automation to Provide Database Access

OLE Automation is one of the greatest technological advances that Microsoft has ever provided for the Windows operating systems. The method is part of OLE 2.0, which Microsoft introduced in 1993.

The "holy grail" for the software development industry is a way to create application-independent software-code that developers can use repeatedly without modification. To create such reuseable code, you must hide from the user the internal implementation details, and call the code with an interface that is independent of the development environment. The internal implementation doesn't matter, only the interface and the expected results.

Windows started a smaller subset of this capability with the DLL (Dynamic Link Library). A DLL enables developers to bind code to one of many executables at run time rather than link time. This creates an environment in which many applications can use the same code base. Windows is based on the DLL concept.

The DLL is very function-oriented. Newer software techniques like object-oriented design don't follow the DLL function call paradigm. Even though languages like C++ can expose objects in a DLL, the object is not transportable among development environments. Even in the C++ environment, compilers such as Borland's and Microsoft's follow different naming conventions for their objects so that you cannot link them together.

OLE Automation is the first step toward producing truly reusable software components. These object-oriented components are independent of the development environment. In OLE, you work with containers and servers. A container is the program that uses a server's services. An example of a container application is Visual Basic. A server is an entity that provides services for a container. OLE Automation servers can be either in-process (.DLL) or local (.EXE). Soon, distributed OLE will provide access to OLE Automation servers residing on a LAN.

OLE Automation provides access to an application's objects to the programmer. The objects contain properties and methods. A property represents data variables that the container manipulates to shape the server object's appearance and behavior. A method is an action that the object performs.

You have already seen an example of OLE Automation in action by using the data access objects (DAO) in Visual Basic 4. The DBEngine is an OLE Automation object that provides all the database-access services to Visual Basic. This DBEngine is the primary object, and contains a few properties and methods. DBEngine's true power derives from the aggregate objects (such as workspaces, databases, and fields) that DBEngine provides to the programmer. All the data access objects are OLE Automation objects that you can use in your program.

The Windows registration database registers OLE Automation objects. You can view this database by invoking the program REGEDIT.EXE (Windows 3.x and Windows 95) or REGEDT32.EXE in the Windows NT environment. The registration database contains the IDs and the OLE Automation objects that are available for system use. Figure 10.3 shows a sample registration database.

Fig. 10.3

Viewing the Windows registration database.

Windows registers an OLE Automation object in two parts: by the object's name and by the GUID (globally unique identifier), a 32-bit ID generated from the time/date stamp and a network interface card ID.

In figure 10.3, you can see that the DBEngine object is registered as DAO.DBEngine. By searching under the CLSID (Class ID) key in the registration and using the DAO.DBEngine GUID, you can see that DAO.DBEngine resides in an in-process server, DAO3032.DLL.

Using an OLE Automation object is quite simple, and much like using any other form of control (such as a DAO, .VBX , or .OCX). The Visual Basic function CreateObject loads the OLE Automation server and creates an instance of the object. The following lines of code can create an OLE Automation object for DBEngine, then get the workspace for the object:

Dim DB As Object
Dim Ws As Workspace
Set DB = CreateObject("DAO.DBEngine")
Set Ws = DB.Workspaces(0)

Creating OLE Automation Objects

Visual Basic 3.0 enabled you to use OLE Automation servers to obtain additional functionality for your programs. Visual Basic 4.0 provides the additional capability of enabling you to create your own OLE Automation objects for use in other programs-a very powerful feature.

To demonstrate how you can use this feature, this chapter describes how to create an OLE Automation interface to the Address Book application, which you developed in Chapter 8, "Accessing Other Databases with the Jet Engine," to provide information to other programs. Before you create this interface, however, you must understand the basics of creating an OLE Automation object in Visual Basic.

In Visual Basic, you represent an OLE Automation object as a class module. You create a class module by choosing Insert, Class Module. This command creates a code-entry window for the class module, as shown in figure 10.4. Press F4 to invoke the Properties dialog box for the class, as shown in figure 10.5. The Properties dialog box for the class module displays the properties listed in table 10.4.

Fig. 10.4

Adding code to the OLE Automation object.

Fig. 10.5

Viewing the properties of the OLE Automation object.

To add properties and methods to the object, choose Insert, Procedure. The Insert Procedure dialog box then appears as shown in figure 10.6.

Fig. 10.6

Adding properties and methods to a class module.

In the Insert Procedure dialog box, you type the property or method name in the Name edit box. In the Type group, choose the appropriate option (Sub, Function, or Property) for the property or method. Sub (subroutine) specifies a method that returns no value, Function specifies a method that returns a value, and Property specifies a property or data member.

In the Scope group, you indicate whether external applications can access the property or method. Public specifies access for external applications, and Private limits use to within the program.

In the check box All Local Variables as Statics, you indicate whether to make all local variables or properties static. If you select this check box, the data members can retain memory between calls to the object. Usually you should select this check box.

The Insert Procedure dialog box adds only template code to the class programming module. You still have some work to do to make the object functional. For methods, this work is fairly simple: you must add the return types and parameters to the function or subroutine declaration. For properties, slightly more work is involved.

Listing 10.3 shows the code that you add to the code module for the Address Book application's Name property. The implementation of the Name property appears later in this section.

Listing 10.3 Additional Code for the Address Book Application's Name Property

Public Static Property Get Name()
End Property

Public Static Property Let Name()
End Property

Public Static Property Get Name() As String
    FirstName = Address.AddressCtl(NAME_FLD)
End Property

Public Static Property Let Name(Value As String)
    Address.Command1_Click
    Adress.AddressCtl(NAME_FLD) = Value
End Property

For properties, Visual Basic has Property Get and Property Let operations. The Get function retrieves the property's value. The example application shown in listing 10.4 implements the Get property by retrieving the value of an edit control in the application's form. The Let function sets the property's value. To implement the Let property, you first clear your application's form and then set the edit control to the value passed into the function.

Listing 10.4 The Implementation of the Name Property

Const NAME_FLD = 1
Const STREET_FLD = 2
Const STREET2_FLD = 3
Const CITY_FLD = 4
Const STATE_FLD = 5
Const ZIP_FLD = 6
Const PHONE_FLD = 7
Const WORK_FLD = 8
Const FAX_FLD = 9
Const NOTES_FLD = 10
Const FIRST_FLD = NAME_FLD
Const LAST_FLD = NOTES_FLD

Private Sub Class_Initialize()
   Address.Visible = False
   Load Address
   Address.Command1_Click
End Sub

Private Sub Class_Terminate()
    If Address.Visible = False Then
        Unload Address
    End If
End Sub

Public Sub FindTelephoneNumbers()
    Address.FindFirst_Click
End Sub

Public Static Property Get Name() As String
    FirstName = Address.AddressCtl(NAME_FLD)
End Property

Public Static Property Let Name(Value As String)
    Address.Command1_Click
    Adress.AddressCtl(NAME_FLD) = Value
End Property

Public Static Property Get Phone() As String
    Phone = Address.AddressCtl(PHONE_FLD)
End Property

Public Static Property Let Phone(Value As String)
    Address.Command1_Click
    Adress.AddressCtl(PHONE_FLD) = Value
End Property

Public Static Property Get Workphone() As String
    Workphone = Address.AddressCtl(WORK_FLD)
End Property

Public Static Property Let Workphone(Value As String)
    Address.Command1_Click
    Adress.AddressCtl(WORK_FLD) = Value
End Property

Public Static Property Get Fax() As String
    Fax = Address.AddressCtl(FAX_FLD)
End Property

Public Static Property Let Fax(Value As String)
    Address.Command1_Click
    Adress.AddressCtl(FAX_FLD) = Value
End Property

Listing 10.4 shows the code for the class module that you add to the Address Book application. The AddressObject uses the application's form extensively for storing, searching, and retrieving values. You could move the functions located in the address form into the class module, but this example simply reuses the existing application code.

Each class module contains a Class_Initialize and a Class_Terminate function. The Class_Initialize function is where all object-instance initialization code resides. The Class_Terminate function is where you put any object-instance exit code. Visual Basic executes the initialization and termination code for each instance of the class module, not once each time that you load or unload the application.

When you first load the object, you also load into memory the application that contains the object. This scheme is exactly the same as starting the program from Program Manager. You load the startup form, call the Class Initialize code, and create the object.

After removing all references to the object, you unload the program, unless the user starts it (for example, through Program Manager).

From Here...

As this chapter has shown, Visual Basic provides easy access to Windows interprocess communications facilities. This enables your application to use other applications as part of a system solution, and also enables other applications to use your application as part of a solution.

OLE Automation is one technology that you should implement for compatibility with future versions of the Windows operating systems. You should use DDE to provide network access and to maintain compatibility with older applications. Also, any of the Visual Basic OLE applications that you create can be used from Microsoft Office or any other OLE 2.0 enabled applications.

For further information on using the Jet database engine for creating database applications, see the following chapters:

• Chapter 8, "Accessing Other Databases with the Jet Engine," covers all the fundamental database-access techniques.
• Chapter 9, "Using OBDC to Access Databases," provides insight into using client/server databases and ODBC for implementing database front-ends.