Visual FoxPro as an Application Front End

As a Visual FoxPro developer, you probably find it natural to design your applications around the program's visual design tools. For example, you probably think of your application's user interface in terms of Visual FoxPro forms, menus, and reports. In addition, when you develop applications in Visual FoxPro, you most likely think of storing the application's data in Visual FoxPro tables.

One way to integrate Visual FoxPro into an enterprise-wide application is to use the visual design tools in Visual FoxPro, but enhance them with the capabilities of other products. Another way is to create your application's look and feel using Visual FoxPro, but to extend the data storage capabilities of your application by taking advantage of the capabilities of other programs or of non-Visual FoxPro data storage options. You can also upsize your Visual FoxPro data by moving it to a database server.

Extending the Visual Design Tools in Visual FoxPro

The base classes of Visual FoxPro controls were designed to accommodate the vast majority of application interface needs. Visual FoxPro provides all the basic controls and interface elements that are required to create a standard Windows application. However, you will often find that your application requires objects or controls beyond those provided in the base Visual FoxPro classes. If so, you can extend the visual design tools by creating subclasses and by using ActiveX controls.

Creating Subclasses

An extremely powerful feature of Visual FoxPro is the ability to create subclasses of the base controls. By creating one or more subclasses, you can customize the basic Visual FoxPro controls in almost any way that's required for your application. This ability extends to being able to create new objects or controls that combine the features of other controls. For example, the grid control in Visual FoxPro contains not only its own container, properties, and methods, but those of the objects that appear in the grid such as buttons, text boxes, and so on.

Similarly, by subclassing base controls, you can extend the capabilities of Visual FoxPro by creating objects that add new features to existing base classes, or that combine the capabilities of several objects. For example, you can add visual features such as frames or three-dimensional effects to a text box. Or you could combine an image control, buttons, and a text box to create a bitmap-viewing control in which users can move through a series of .bmp files. Creating custom classes in this way can help you manage company-wide development by allowing you to create standardized controls that appear in all your applications. For more information, see Object-Oriented Programming.

Using ActiveX Controls

An alternative to creating a new control using subclasses in Visual FoxPro is to use an ActiveX control (.ocx file). These controls are created independently from Visual FoxPro, and can be integrated not only into Visual FoxPro, but into many other Windows applications as well.

In effect, ActiveX controls are off-the-shelf components that you can integrate seamlessly into your application. Using ActiveX controls provides several benefits:

• It saves you the time and effort required to create, test, and maintain a Visual FoxPro-specific control to accomplish the same tasks. The more capable the ActiveX control, the more time you save.
• Many ActiveX controls are already available from third-party suppliers to answer common application requirements. For example, if your application calls for you to display a calendar and allow users to choose dates on it, you can probably find an ActiveX control (perhaps several) that already manages this task.
• The same control can be used in multiple programs. For example, if it makes sense to do so, you can use the same ActiveX control in Visual FoxPro and Visual Basic. The same properties and methods are used in each case to manage the control, and the control will have the same appearance in all programs, making it easier for users to work with.
• ActiveX controls often provide access to Windows functionality that can otherwise be awkward or time-consuming to include using only Visual FoxPro tools. For example, you can find ActiveX controls that provide access to electronic mail (using Windows MAPI functions), to low-level Windows graphics functions, and so on. By including an ActiveX control, you can add these types of features to your application in a way that's easy to control using the ActiveX control's properties, methods, and events.

In short, using ActiveX controls enables you to extend your applications not only by integrating Windows-wide functionality, but by adding a common look-and-feel between your application and others in the same enterprise. For more information about using ActiveX controls, see Adding OLE. For information about creating your own ActiveX controls, see Accessing the Visual FoxPro API.

Integrating Functionality from Other Programs

You might find when developing an application that other programs are uniquely suited to accomplishing certain tasks. For example, Microsoft Word has unsurpassed merge letter capabilities, while Microsoft Excel is optimized to calculate complex formulas and easily create charts and graphs from them.

Rather than emulating these capabilities in Visual FoxPro, you can make your application an enterprise-wide solution by integrating them into your application. This way you can match the requirement of your application to the best possible tool to address it.

You can integrate the functionality of other applications into Visual FoxPro in these ways:

• Run a Visual FoxPro wizard that makes Visual FoxPro data available for use by another application.
• Write Visual FoxPro programs that use Automation to communicate with, control, and share data with other Windows programs.

The following sections provide details about these methods of extending the capabilities of Visual FoxPro.

Using Wizards

A number of Visual FoxPro wizards allow you to integrate Visual FoxPro data with the functionality of other Windows programs. For example, you can send form letters to your customers by using the Mail Merge Wizard. When you run the wizard, you can specify a table or view that contains the Visual FoxPro data to use, and then either export the data to a suitable file format (such as comma-delimited) or specify that your word processing program use the Visual FoxPro OLE DB provider to access the data. If you use Microsoft Word, the wizard will even start the word processing program, create the blank merge document, and display the Mail Merge toolbar for you to link fields to your Visual FoxPro data.

Similarly, using Microsoft Excel and Microsoft Query, you can analyze your data using a pivot table, which summarizes data in columns and allows you to rearrange it to view it in different ways. By using the PivotTable Wizard in Visual FoxPro, you can use your application data as the source data for Microsoft Excel, and generate the pivot table in Microsoft Excel.

Using Automation

A more powerful way to interact with other applications is to use Automation. Using Visual FoxPro programs, you can access the objects exposed by other applications, and then control them by setting their properties and calling their methods. For example, Microsoft Excel exposes an application object as well as worksheets, columns, rows, and cells within the application object. You can directly manipulate any of these objects, including getting or setting data in them. In addition, you can usually control the application object using the full range of commands available in the program itself. For example, by managing the application object in Microsoft Excel, you can open, save, or print worksheets, invoke the Microsoft Excel chart wizard, and so on.

Automation is a particularly attractive and powerful way to work with Windows programs using Visual FoxPro for several reasons:

• You have direct access to the other program, including all its objects and commands.
• You can share data directly with the other program without having to export it or convert it to another format.
• You can control the other program using the familiar properties and methods model.
• The other program doesn't necessarily need to be visible to the user when you invoke it. For example, you can invoke Microsoft Excel, place some data into cells, run a complex calculation on the data, read the result, and then display it in Visual FoxPro, all without ever displaying Microsoft Excel. Your user would continue to see only Visual FoxPro, unless you explicitly wanted to display Microsoft Excel.
• The commands (methods and properties) to control the other program are embedded within familiar Visual FoxPro programs. You don't need to learn a different programming language in order to be able to control the other program.

Automation is particularly powerful because it's an open-ended method for working with other programs. In essence, Automation simply makes available to you the data and commands from other applications for you to use in the way best suited to your application.

A sample scenario illustrates how you can integrate several Windows programs. Imagine that you store your customer and sales data in Visual FoxPro. You'd like to create a sales report that summarizes quarterly sales.

One solution would be to use Automation to copy the Visual FoxPro sales data to cells in a Microsoft Excel worksheet. You can then invoke the Microsoft Excel chart wizard to create a chart of the data and copy it to the Windows Clipboard. Still using Automation, you can invoke Microsoft Word and create or open a sales report document (if you create it as a new document, you can insert standard text that you store in Visual FoxPro), and then paste in the chart you created in Microsoft Excel.

This is just one way you can use Automation to make Visual FoxPro part of an enterprise-wide solution. By becoming familiar with the objects and methods available in programs that you typically use, you can imagine many more ways to make each program enhance the capabilities of the other. For details about Automation, see "Manipulating Objects with Automation" Adding OLE.

Extending Data Storage Capabilities in Visual FoxPro

The data table and indexing capabilities of Visual FoxPro are usually more than adequate to the requirements of an application, whether you're concerned about speed or database size. However, there are times when you might want to extend Visual FoxPro by using data that's stored in some other format. This might be true if:

• Your application needs access to legacy data that's created and maintained by an existing application. For example, suppose that, as part of your sales application, you need access to data being maintained by an accounting application that was written using a different program, perhaps even on a different platform.
• You want to optimize data access by using a database server, which can greatly speed data access, particularly in very large databases.
• You want to share data with other programs, and therefore want to store the data in a format that's accessible to all the programs.
• The data is best suited to the format of a particular program (such as a spreadsheet). This might be true, for example, if your application required only occasional access to data that was otherwise being maintained by the other program.

If the data you need is in the format of a spreadsheet, word processing document, or other Windows program, you can access it using Automation. For example, you might do this if your application required a collection of form letters. In that case, the letters might be stored as Microsoft Word documents, and your application would use Automation to invoke Word, open the appropriate letter, and insert or substitute text as necessary.

A more common approach to using non-Visual FoxPro data is to use an OLE DB provider to access it. OLE DB providers allow you to connect to data in the format of other programs — typically other database programs — and query or edit the data using standard SQL commands.

For example, you might decide that security and transaction processing capabilities are a vital part of your application, so you decide to store the data using Microsoft SQL Server. To access the data, you define a connection to SQL Server, using the SQL Server OLE DB provider. You can then run queries and other SQL commands using ADO, and retrieve the results into an ADO recordset for further processing in Visual FoxPro.

Other applications can access the same data and take advantage of the same features. For example, a Microsoft Excel worksheet can get its data from the same SQL Server database. Not only will the worksheet enjoy the same performance advantages that your application does, it can also take advantage of the security and transaction processing features of the server, which aren't otherwise available in a Microsoft Excel worksheet.

In some instances, you might want to go further and use SQL commands that are specific to the data source you're accessing with the OLE DB provider. For example, Microsoft SQL Server allows you to create and run stored procedures, which can manipulate data at the server (rather than in your application). To take advantage of stored procedures, you can send "native" SQL statements to the database server. Pass-through SQL commands also allow you to perform system administration tasks on the server, and in some instances they will execute faster than similar SQL statements executed in Visual FoxPro.

For more details about extending data storage capabilities in Visual FoxPro, refer to the documentation in the following table.

Upsizing Visual FoxPro Data

You can choose to keep your data in Visual FoxPro tables or on another platform such as a database server. Or you can do both: keep your data in Visual FoxPro tables while you're developing, or until your database grows large, and then move (or upsize) the data to another platform.

For example, you can prototype your application by keeping all the data in local Visual FoxPro tables. This gives you the flexibility to modify your tables, views, and indexes as you develop the application without the complexities of managing tables on a database server. You can keep sample data in the local tables so you can test your forms, reports, and other programs. When the database structure is complete, you can upsize your data to a database server and put the application into production.

Another way to work is to keep your data in Visual FoxPro tables only as long as is practical. When the database grows large, you can upsize it and take advantage of the optimized performance provided by a database server. The exact point at which it makes sense to upsize your database depends on many factors, including the complexity of the database, the performance of your local computer or network, and the demands of your application.

Finally, you can prototype your database in Visual FoxPro, and then upsize it in order to share the data with other applications that can also access a database server. Similarly, you can upsize the database in order to take advantage of the security and server-side transaction processing capabilities of the database server.

For details on upsizing databases, see Upsizing Visual FoxPro Databases.

See Also

Development for the Enterprise | Visual FoxPro as a Data Source | Object-Oriented Programming | Adding OLE | Accessing the Visual FoxPro API | Mail Merge Wizard | PivotTable Wizard