Objective

1. Visual Foxpro andVisual BasicHow do they compare as Database DevelopmentEnvironments?Presented byHarold ChattawayOptimized Data Solutions http://www.optimized-solutions.com&John Bastow arrayWORKS limey@arrayworks.com

2. Objective • The objective of this comparison is to present Visual Foxpro and Visual Basic in an environment that will allow product development managers, MIS directors, and developers, to see these two products side by side, so that the database features of both products can be compared and contrasted. • This seminar will walk though the creation of a database application from beginning to end using a design specification that is language independent. • This process will allow all the various design surfaces and language elements to be explored in depth to see how each product achieves the goal of meeting the design spec supplied here. • We will also explore: • How the form controls of these two products differ • How each product handles OLE Automation • How each product can be used to create a simple Internet application. • How each product connects to SQL Server data for C/S applications.

3. Customer Requirements • This is a unique chance to examine first hand, the differences between two products in the Microsoft family of Visual Tools. It is often hard to evaluate products based on a printed review of that product. The format of this seminar, will allow for the first time, the opportunity to see how these two products approach a common business problem, the storing and retrieving of data in a way that is both low cost to develop, and easy to maintain.

4. Design Specification: Tables • The design specification for this seminar is as follows: • A customer requires a system that will maintain, retrieve, and produce printed reports for real estate transaction information that is collected in the state of Massachusetts. This data is collected from the towns and input into a set of tables that maintains information on the parcel of land or building. Some things it needs to maintain are room counts and lotsize It also must maintain a history of all sales and refinance activity on that parcel. • There will be a set of tables provided that represent commercial and residential real-estate transactions. There will be a master property table, and several related tables. One of the related tables holds all sales transactions on a parcel, and the other table holds all refinance transactions on a parcel. There is also a table holding all the towns in MA, and another tables that holds all the counties in MA. Additionally, there are two look up tables that hold all the building style descriptions, and all the building usage descriptions. • The master property table could eventually have 3 million records, and the sales and refinance tables could be around 3.5 million records. A design that takes into account the fastest possible retrieval of data is mandatory! • The structure of these tables are in the appendix.

5. Design Specifications: Forms • The form requirements are as follows: • There needs to be a form that allows for the viewing of the master property record along with some mechanism to view all related sales and refinance information in separate grids. The user should also be able to select a transaction and view its detailed information. • This screen also requires a set of navigation buttons to allow navigation of the master property table. • Each of the transaction tables, sales and refis, require a maintenance form that can be called up separately from the above form. Each of the lookup tables also need there own maintenance form. • There also needs to be a form that allows for simple ad-hoc queries on the Master Property table. The fields that need to be queried are: County, Town, street, and Street #. After the query is run, the result set should be displayed in a report or a grid for easy viewing.

6. Design Specifications: Menus • There should be a pull-down menu system to access the various components of the application.

7. Setting up the Environment... In VFP, one of the first things a developer would do is to map specific class libraries to data types. This is so any time a field from a database is dragged onto a form, the control that is used by default will be the one specified in this mapping dialog. With no coding, this allows a great deal of functionality to be applied to a form control through the mechanism of inheritance. Other configuration steps would be to specify what base form should be used and what class libraries and controls are to be used. In VB the first thing you would do is set up the project options and choose which ActiveX Controls and Servers you will be using. You do this from the Tools/Options, Tools/Controls and Tools/References dialogs.

8. Project Managers... In VB the project manager shows all objects in the same window. This includes the Forms, Modules and Class Modules. Since VB is not primarily a database environment, databases are not included as objects in the project. The project window in VB can also be integrated with Visual Sourcesafe or any other VB IDE enabled source code manager. The project manager in VFP is broken into several tabs. There is a tab for Data, Documents, class libraries, code, and other files. The data tab tracks databases, tables, local and remote views, remote connections, and stored procedures. The project manager is also fully integrated with Visual Souresafe for multi-team development. All tabs use an outline control to allow the user to drilldown into each of the categories.

9. Project Managers... • The project window is drag and drop enabled allowing you to add existing source files by dragging them from an explorer window to the project window. • Final executables are built from the main menu/toobar window's file window. VB can build EXEs that requires the VB runtime DLL. These EXEs can also act as outofproc OLE servers. VB can also build OLE DLLs. • The latest version of VB allows you to build projects as ActiveX Controls which can then be used in other VB programs, VFP, web pages or any other application that can use ActiveX Controls. • The VFP project manager, with its tabbed format, makes for a very clean interface to all project components. • The project manager can be used in its “un-docked” mode as you see on the previous page, or in its “docked” mode whereby the project manager is dragged to the top of the VFP work space where it can be docked like a toolbar. Once docked, the developer can click on a tab which will then expand that tab so all of its entries can be seen. If the developer needs to work with a particular tab for a while, it can be “torn off” and placed on the desktop in its expanded state. • The project manager also serves the role of building the final executable for the application. VFP can build either an APP file, which requires the VFP development version, or an EXE, which requires the runtime library. VFP can also build projects as OLE DLL’s. This allows other applications to use VFP as an OLE Server. This opens up VFP to be used as a data engine for MS Office or as a Server on the Internet.

10. Database Design Tools... The database designer in VFP is the central tool in creating all of the data components of an application. This is where all of the tables, views, remote connections, and stored procedures are created and maintained. Any object in the database designer can be selected and edited. VFP supports updateable local and remote views. Once defined, views are treated just like any native VFP table. VFP does not care or make any distinction between views and native tables. • VB provides an add-in Data Manager that allows you to create or modify an Access/Jet database. All of the objects in an Access/Jet database are contained in a single .mdb file. These objects are accessible through DAO (Data Access Objects.) • DAO exposes the Jet database engine through OLE. This starts with the DBEngine object that represents the Jet engine followed by the Workspace object that handles users and security and the database object. The database object includes tables, attached table connections for remote data, querydefs and referential integrity rules. DAO also handles recordset objects. The following example shows how to open a table: • Dim dbsVBData As Database • Dim rstVBData As Recordset • set dbsVBData = DBEngine.Workspaces(0).OpenDatabase(“C:\VBData.mdb”) • Set rstVBData = dbsVBData.TableDefs!tblVBData.OpenRecordset() • It should be noted that Access forms, reports and code modules are not accessible to VB through the Jet engine. In Access 7.0 they are accessible through OLE automation. • Although the VB Data Manager allows for complete access and modification of the DAO objects, Access itself provides a more user-friendly interface. • An Access database can contain relationships that are used to enforce referential integrity as well as cascading deletes and updates. Tables and fields can also contain predefined validation rules. Both referential integrity and validation rules are enforced by the Jet engine.

11. Database Design Tools... • QueryDefs contain predefined SQL statements. These can not only include select queries but also crosstab, delete, insert, update and make table queries. QueryDefs can contain parameters to allow queries to be generalized and used throughout an application. Following is an example of opening a recordset using a parameter query. • dim qryProperties As QueryDef • dim rstProperties As Recordset • set qryProperties = dbsProperty.QueryDefs!qryProperties • qryProperties!Town = “Waltham” • set rstProperties = qryProperties.OpenRecordset() • The VFP database designer is also where persistent relations are defined. Once the appropriate index tags have been defined, the index’s can be dragged and dropped between the tables to create persistent relationships. These relationships define what type of relation it is, either one to one or one to many. • Once relations are defined, this allows referential integrity rules to be defined. Through the use of a RI Builder, RI rules can be defined which are enforced at the database engine level through the use of INSERT, UPDATE, and DELETE triggers. • Since these rules are enforced at the engine level, they do not have to be recoded on forms.

12. Table Designers... The table editor in the Data Manager allows you to add, delete or edit fields, manage indexes and define keys. In the field editing dialog you can change the name, type, size, default value, whether it's a counter, and validation rules such as ranges and whether or not the field is required. • The table designer in VFP is where the structure of a table is defined. Field names, data type, width, and null support are defined for each field. Also, field formatting, field rules, default values, and field comments are defined here. One of the most important features here, is the ability to map a specific class library to a specific field. This overrides the mapping done through the field mapping shown in “Setting up the Environment”.

13. Table Designer... • The Index button allows for the creation, deletion and editing of indexes. Indexes can include one or more fields and can be set to allow or not allow duplicate values. • The Keys button allows you to define a primary key for the table. The primary key is tables primary index. It cannot contain null values or duplicates. It is the index that is used on the one side of one to many relationships when referential integrity is being enforced. • If a character field is holding a phone number for example, the developer can map a custom textbox class to this field that knows how to format a phone number. Or a custom combo box class which contains a list of all the States, can be mapped to a field that will hold the 2 character state abbreviation code. When this field is placed onto a form, the custom combo box will be used in place of a plain textbox. • The second tab on this dialog allows for the creation of indexes. The proper use of indexes is what gives Foxpro its amazing database performance. VFP supports primary and candidate keys. These index types enforce key uniqueness. Indexes can be on a single field or can be composed of multiple fields. • The third tab on this dialog allows for the definition of record level rules and table level triggers. VFP supports INSERT, UPDATE, and DELETE triggers. Update triggers provide an easy mechanism to create audit trails automatically. • Any table that is part of a database, also has the ability to use transaction processing. VFP supports BEGIN TRANSACTION, ROLLBACK, and END TRANSACTION. This feature allows for multi-table updates to be wrapped in a transaction for easy reversal should an error occur during the update process. Access databases do not support triggers. Access does support referential integrity. This allows for cascading updates and deletes. Thus if a record in a primary table is deleted it’s related records in all related records are deleted. If the primary key value in a primary table is changed the related field in it’s related records are changed to match. Transaction processing on open databases is handled in code by the DAO Workspace object.

14. Creating Classes... • In the current version of VB, classes do not have inheritance. • Class modules in VB are object definitions that can be used internally in the application and also as OLE automation interfaces into the application. In applications that require more complex rules than those that can be defined within the validation and referential integrity rules in DAO, classes can be used as an interface between the UI and the data. This can be separated even further by removing the classes themselves from the application and compiling them as a separate OLE server. Using Remote Automation these could even reside on a separate machine. • Instances of classes are created by using the New keword and assigning that instance to an object variable. • Class modules are added through the Insert menu. Classes are defined in code much as regular Code modules. However, any public module level variables are properties of that class and public subs and functions are methods of that class. • Option Explicit • Public lngStreetNum As Long • Public strStreet As String • Private strTown As String • Public Function Address() As String • Address = CStr(lngStreetNum) & " " & strStreet & Chr(13) & strTown • End Function • Before form design is started in Visual Foxpro, the developer should invest time into creating custom classes. Since VFP is an object-oriented language, MS has supplied a design surface solely for the purpose of creating re-usable class libraries. This tool is called the Visual Class Designer. The Visual Class Designer ,for instance, allows the developer to create a library of sub-classed form controls that become the foundation for all future forms. Dropping a VFP base class on a form as shipped by MS, does not allow the developer to take advantage of one of the most powerful characteristic of OOP, inheritance. As mentioned above, the developer should create a library that contains a sub-classed version of all the form controls. This provides the “blueprint” for an object. When a new behavior or appearance is needed for the object, the developer just has to change the baseclass. The next time this object is instantiated, it will automatically inherit the changes made to the baseclass.

15. Creating Classes… VFP Once in the Visual Class Designer, the developer can access the property sheet for that object to assign any properties or provide code for any method. If a custom property or method is needed, it can be created. Avery powerful OOP feature in VFP is the ability to select multiple controls on a form and then go to “File|Save as Class”. This feature becomes powerful in early development when it becomes apparent that forms will have objects in common. Save the group as a class, and then drop them back onto the appropriate forms. They now inherit there behavior from the new base class. • The previous screen shot, shows the first step in creating a new Visual Class. VFP ask for the name of the new class, what the object will be based on, and the class library to store the new class in. The new class can be based off a VFP baseclass or any other custom class previously defined. This allows the developer to create slightly different versions of the baseclass without the need to cut & paste

16. Forms Designers…VFP • The VFP forms designer is centered around two major aspects of the product, data and reusable class libraries. Before creating your first form, the developer should create a base form class that can be as the baseclass for all future forms. The creation of the base form class is accomplished through Visual Class Designer discussed on the prior set of slides. When used to create forms, the Class Designer has the same design surface as the forms designer. This difference being, with the Visual Class Designer, the form can be saved as a class. This gives tremendous development power. Any properties or methods that a developer would want to have in all forms can be put in this base form class. When other forms are created from it, they will inherit all of these properties and methods. This allows for all forms to have a consistent look and behavior without having to recreate it on a form by form basis. • Using the Tools|Options dialog, developers can tell VFP what base form class to use when creating new forms. • When the forms designer comes up, the first thing that would be done is to establish the data environment for the form. This is where the forms is told what tables to open up when the form is run. • The data environment (DE) contains a list box for each table associated with the form. Notice also how the relations between the tables are in place. These were taken from the data dictionary that was discussed earlier. With the DE in place, it now becomes a simple matter to drag and drop fields from here on to the forms designer. Each object in the DE also has its own property sheet. All table properties can be set visually through this sheet. Notice also the full suite of form controls that are available in the controls toolbar. The top right button is used to switch in any other custom class libraries that a developer may want to use, including the full line of ActiveX controls.

17. Form Controls…VFP This very powerful convenience allows the developer to create multiple sub-classed controls or allows the creation of a sub-classed grid simply by dragging the fields onto the form. • The two most common controls are the textbox and grid control. The textbox object in VFP, is created by simply dragging & dropping a field from the data environment or the data tab of the project manager. Assuming the field mapping was established as described earlier, the mapped class library is used instead of the default baseclass. Also a field label is created automatically. If multiple fields are selected and dragged to the form using the RIGHT mouse button and then released, the following menu appears: . The grid, when created, will be formatted with the grid headers coming from the data dictionary and all columns bound to the fields selected. If textbox’s are created instead, they also would be automatically bound to the appropriate fields in the table While VFP can make full use of any of the third party ActiveX controls, they are generally not required for the standard form controls. All of the standard form controls are native VFP controls, including the grid control. The native grid control supports inheritance, can be subclassed, and also acts as a container object for any other control. Drop-downs, checkbox’s, command buttons, can be added to a grid simply by clicking on the control in the controls toolbar , and then clicking in the column that will receive the control. Grids can even contain other grids. No code is required to bind a control to data. Some controls like the grid or combo-box, support a SQL command as the RowSource. The result set is used to populate the control.

18. Form Navigation...VFP • Form navigation in Visual Foxpro is usually accomplished through the use of a navigation class. This class acts as a container for a series of command buttons that perform navigation functions. By being in a class library, this navigation class can be dropped onto forms giving them consistent behavior. As can be seen below, in the class library ODS, there is a class called NAVSTAND (standard navigation). Associated with the click event of the NEXT command button is the code THISFORM.NEXT(). By simply using the command THISFORM.NEXT(), we leave the implementation of NEXT() to the currently active form. In the examples used here, all the forms are inherited from BASEFORM in the ODS class library. It is in this base form class that the default NEXT method is provided. If there is no overriding code for the NEXT method in the active form, Visual Foxpro will search up the object hierarchy until it encounters the code in the base form class. Once found, this will be executed. If the active form needs to handle the NEXT method completely differently, code can be placed in that particular form.This will then override the code in the baseform. If the default behavior is needed along with additional functionality, then the DODEFAULT() function can be used to call the code in the base form and then any additional code can follow. This approach gives maximum flexibility. All forms get a default behavior, but can be overridden at any time. A similar approach is taken for all other command buttons in the navigation class.

19. Form Designers… VB • VB forms are special types of VB classes. As with classes in the current version of VB it is not possible to base forms on other forms. However it is possible to build VB add-ins to create forms such as form wizards. • Since VB is not strictly a database development environment forms are not by default integrated with database tables or queries. They can be made data aware by adding one or more data controls to a form. • A form consists of it's visual window and it's associated code to handle events or define properties and methods. Visual form design consists of adding interface elements that are available in the toolbox. These toolbox controls include built in VB controls such as text boxes or labels, ActiveX controls, and insertable OLE objects such as Word or Excel documents. Controls can be added or removed from the toolbox through the Tools/Controls dialog. This can be done at any time during the development. VB will not allow any control in use to be removed from the toolbox. In VB forms/controls and data are not inherently linked together. There are two methods that can be used to do this. The easiest method is to add one or more data controls and bind data-aware controls to a data control. The other is to update the controls and data through VB code and the DAO objects. Although somewhat more time consuming it allows for much greater control over how data is updated and displayed. In code you can add, delete and edit data using the Edit, AddNew, Delete and Update methods of the Recordset Object The data control is an ActiveX Control that is bound to a recordset in a database. This can based on either a table or query. The database and recordset properties can be set at both run-time and design time. A single form can contain multiple data controls since the form itself is not bound to a particular data control, the controls themselves are linked to a data control. Simple controls such as text boxes or check boxes are linked to a single field in a single data control. Controls such as combo or list boxes can be linked to a field in a data control for reading and saving a value as well as an additional data control to populate the list of available values. Grid controls can be linked to a data control and display all of the available fields. More complex third party grids allow for some of the fields to contain combo boxes or other controls.

20. Form Navigation...VB • Form navigation in VB when using a data control can be done in one of two ways: • The first is to make the Data Control visible. The Data Control provides buttons that allow the user to navigate to the next or previous records as well as the first or last records. • I f you decide to use the Data Control to provide navigation but would like to add a little more functionality you can add code to handle the Data Control’s Reposition event which is fired each time the control is moved to a new record. • The second is to provide your own buttons and navigate using code to navigate through the data controls underlying recordset. The following code will move a form to the next record. • dtaDataControl.Recordset.MoveNext • dtaDataControl.UpdateControls

21. Report Writers…VFP • Visual Foxpro’s Report Writer is a fully integrated component of the Visual Foxpro development environment. Reports are kept under the Documents tab of the Project Manager. When a new report is created, a blank design surface appears. The report writer is broken into bands. There is a title, header, detail, page footer, and summary band to the report. The report writer also has its own data environment, similar to the forms designer. All tables that are needed for the report can be placed in the data environment. The data environment is not mandatory though. Any tables that have been opened up are able to be used in the report writer. • Since the report writer is an integral component, all Fox memory variables, functions, as well as the entire Foxpro language, is available to the report writer. User Defined Functions (UDF’s) can call functions utilizing any part of the Foxpro language. • Visual Foxpro also has a built in Label Designer. This functions almost exactly like the report writer except when started, it asks for a label definition. It has a very extensive list of pre-defined labels to choose from.

22. Report Writers…VB • VB ships with Crystal Reports, a report generator that includes an ActiveX Control for running reports in an application. There are a number of other third party reporting controls and add-ins. It is also possible to use the Access report generator by using Access as an OLE automation server. • Crystal Reports has it's own report designing environment. It allows you to drag and drop fields onto a report page. It also allows you to sort and group reports. You open Crystal Reports as a separate application to design a report. Once the report is designed you save the format to a file. To generate a report from a VB app you add an instance of the Crystal Reports ActiveX Control to your application. You can then set properties for the report file as well as the appropriate database and record source at either design or run-time. At run-time you can then generate a report.

23. OLE Automation... • Visual Basic 4 also added the ability to create OLE Servers. With the Enterprise addition it also allowed those servers to be accessed on another machine across a network using Remote Automation. The addition of the ability to create OLE servers allows VB to act as middleware between a database and a client program that encapsulates business rule. • VB OLE servers can also either be out of process EXE or an in-proccess OLE DLL The VB5 Control Creation Edition now also allows for the creation of ActiveX Controls, a special type of in-proc OLE DLLs. • One of the main enhancements to Visual Foxpro 5.0 is the ability to create OLE Servers. With OLE Servers, this now opens up the possibility for other applications to tap into the Visual Foxpro data engine. Visual Foxpro can create either in-process or out of process OLE Server. An in-process server runs in the same address space as the application that called it. An out of process server runs as its own process. With an out of process server, the resultant DLL file can be remotely deployed across a network so that other applications can easily access it.

24. OLE Automation... • To make a VB app an OLE Server, you make one or more class modules Public and Creatable using the Public and Instancing properties of the class modules. Once this is done any variables defined as Public are OLE properties and any subs and functions defined as Public are OLE Methods. Property Get, Let and Set functions can also be written that can provide validation checking when setting and returning property values. The following is example code from a class module. • Option Explicit • Public lngStreetNum As Long • Public strStreet As String • Private strTown As String • Public Function Address() As String • Address = CStr(lngStreetNum) & " " & strStreet & Chr(13) & strTown • End Function • Public Property Get Town() As String • Town = strTown • End Property • Public Property Let Town(strNewTown As String) • ' Check to see if valid town • ' If not raise an error • strTown = strNewTown • End Property • In order to make an application into an OLE Server, the developer simply needs to expose a class as being OLEPUBLIC. This can be done by utilizing the OLEPUBLIC clause when defining a class programmatically or by checking off the “OLE Public” checkbox in the Visual Class Designer. In order to build an OLE Server, just choose “Build OLE DLL” from the project Build Dialog box.. This creates the DLL file and automatically registers it in the Windows registry file. Once created, any other application that supports automation can gain access to the Server. For example, the following code is a macro from MS Excel: 1.Sub foxserver() 2.Dim otest As Object 3.Dim lncounter As Integer 4.Set ofox = CreateObject("shootout.oleinterface") 5.ctally = ofox.salesquery(ActiveSheet.Cells(1, 4).Value, ActiveSheet.Cells(2, 4).Value) 6.lncounter = 3 7.Do While lncounter < 102 8. ActiveSheet.Cells(lncounter, 1).Value = ofox.getstreet() 9. ofox.Next 10. lncounter = lncounter + 1 11. Loop 12. End Sub

25. OLE Automation... • In this example there are 3 variables defined. The 2 public variables are accessible through OLE as object properties. Since strTown has been declared private it is not accessible. However, Property Get and Let functions have been written to allow a client to set the private strTown property. In this case it can allow the class to verify that a town exists before it sets the property and raise an error if it does not. • VB can also be an OLE Client. Often it can be useful to break a large application into a number of OLE Servers, thus making a VB Client to a number of other VB Servers. This also makes it possible at a later date to rewrite the server components in another development language such as VC++ or VFP without having to rebuild the entire application. • This macro creates an instance of the DLL “shootout” using the CREATEOBJECT command on line 4. Line 5 calls the method “oleinterface” and passes as parameters the value of two cells on the active sheet. These two cells contain dollar amounts that represent price ranges of homes. This method uses these parameters as upper and lower bounds in a SQL Select statement. The result set is then scanned inside the DO WHILE loop and the contents of the table are inserted into the active spreadsheet. Once the data is in Excel, Excel can be used to perform further statistical analysis and graphing of the data. Since Visual Foxpro can query million record tables in under a second, this gives a very powerful data engine to other OLE clients. • Conversely, Visual Foxpro can be an OLE client and can control other OLE applications. For example, Visual Foxpro can start up an instance of Excel and tap into its library of financial and statistical functions. The following lines of code could be used from Visual Foxpro to have Excel calculate the depreciation of an asset using the fixed declining balance method: oExcel = createobject(“excel.application”) lndeprciate = oExcel.DB(cost, salvage, life, period, month) If you need to tap into the library of functions that Excel has, create the object reference but do not make it visible. Now Excel functions are available just as if they were built in Visual Foxpro functions.

26. SQL Server Connectivity... • Remote data can be access in one of two ways. An Access database can be created that has attached tables that are stored in remote databases. In this way a program can be identical whether it uses native Access data or remote data. The other way is to use RDO (Remote Data Objects). RDO is a thin OLE layer on top of ODBC. RDO is very similar to DAO and as such DAO and RDO applications do not differ significantly. However, RDO is much more efficient than DAO because it removes the Jet Database Engine layer. There is even a Remote Data Control that can be used interchangeably with the Data Control. • The RemoteData Control has Connection, DataSource and SQL properties that can be set at design or runtime to return remote data. • The RemoteData Control’s Resultset property is the RDO equivalent of DAO’s recordset and in fact has the same record navigation methods. • The Resultset can have any of the standard ODBC cursor types. • Records can be added or changed using AddNew, Edit and Update methods in the same way as they would using DAO. The following example changes the town name in the current record • rstProperty.Edit • rstProperty!Town = “Franklin” • rstProperty.Update • In Visual Foxpro, remote data is treated just like local data. Once an ODBC connection has been established to the remote table, all of the design surfaces that Visual Foxpro uses will treat this remote data as if it was local data. Generally, access to remote tables is provided through the use of views. Views of remote data create a Visual Foxpro cursor that Foxpro can than manipulate just like any other table. Visual Foxpro supports parameterized updateable views. These allow backend data to be updated by simply issuing a TABLEUPDATE() command. By utilizing parameterized views and progressive fetching, C/S performance can be improved significantly.

27. SQL Server Connectivity…VFP The previous screen show, demonstrates the Connection Designer. This tool uses an ODBC data source to make available to VFP the remote database. This allows remote tables to be referred to by a named connection. Access to remote data can also be accomplished by using SQL passthrough. The Remote view designer allows views to be defined visually. Once the connection to the remote source has been established, the remote tables appear as if they were VFP native tables to the view designer. Also, once defined, remote views can be opened in code with the standard “USE” command. ie: USE v_authors The next screen shot shows how in the remote view designer, VFP allows each field in the view to be mapped to a custom class, have field level validation, default values, input mask, and comments. VFP 5.0 now supports “offline views”. This allows snapshots of remote data to be downloaded, detached, updated, and posted back to the remote tables.

28. In Summary... Visual Foxpro Visual Basic • Visual Basic provides a full database development environment for creating both local and Client Server database applications • It can allow for the development of simple interfaces using the Data Control or complex systems using the full power of VB and DAO and RDO object models. • It can act both as an interface or as the OLE middleware in a 3 tier client server solution. • One of the most important characteristics of Visual Foxpro from a development viewpoint, is that there is really no distinction between the language, the tool set, and the data. Foxpro has always been a database development environment, and as such, has been highly optimized to handle data easily, quickly, and painlessly. It is a “data-centric” language. The data, controls, and all of the data manipulation commands are an integral part of the overall VFP development environment. • VFP’s advanced object-oriented language makes Rapid Application Development (RAD) truly possible. VFP’s support of inheritance, allows components to be assembled quickly while also allowing for easy maintenance. A standard look and functionality can be built into baseclass’s which other objects can than inherit. • VFP offers a highly scalable, stable, full-featured data engine that other applications such as MS Office can now tap into in the form of a data wharehouse server or as a component in a 3-tier C/S system. • If C/S is needed, VFP treats remote data just like its own native data, preserving the way in which applications are built. Remote fields and tables can also be dragged & dropped onto forms while at the same time making use of VFP’s object model and local data engine. • VFP’s ability to handle multi-million record tables with virtually no query speed sacrifice, greatly pushes out the C/S “horizon”. Many jobs thought to be the domain of C/S can be handled with VFP at a much lower initial cost and also much lower ongoing cost.

29. About... Optimized Data Solutions arrayWORKS • Optimized Data Solutions (ODS) principals, Harold Chattaway and Douglas Gray, have collectively 18 years experience designing and implementing database applications. ODS has: • Been a frequent speaker at the Boston Foxpro’s Users group • Has demoed VFP for MS at Client/Server World • Been a contributing author to several trade journals and books. • Presented a series of courses in the Boston area on VFP for beginning to advanced levels. • ODS specializes in utilizing Foxpro for creating custom database applications ranging in size from small to very large. We also provide expert services in training, Internet data publishing, C/S systems, and utilizing VFP as an OLE data server for Office products. • We can be reached by: • http://www.optimized-solutions.com • 508-345-7339 • haroldc@optimized-solutions.com • dgray@optimized-solutions.com • John Bastow is in independent consultant working under the name arrayWORKS. • He specializes in MS Access, Visual Basic and Office Integration solutions and has been working with Visual Basic since version 1.0. • He has a BS in Computer Science from Northeastern University with a concentration in DBMS. • John Bastow can be reached at 617-266-8865 or limey@arrayworks.com