Transcript shootout

Visual Foxpro
and
Visual Basic
How do they compare as
Database Development
Environments?
Presented by
Harold Chattaway
Optimized Data Solutions
http://www.optimized-solutions.com
&
John Bastow
arrayWORKS
[email protected]
1
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.
2
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.
3
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 realestate 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.
4
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.
5
Design Specifications: Menus

There should be a pull-down menu system to access the
various components of the application.
6
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.
Project Managers...
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.
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.
Project Managers...



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.
•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.
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:\VB
Data.mdb”)
Set rstVBData =
dbsVBData.TableDefs!tblVBData.OpenRecordse
t()
•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.
Database Design Tools...



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.
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()
Table Designers...

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”.
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.
Table Designer...




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
•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.
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.
Creating Classes...

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.
•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
Creating Classes… VFP

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
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.
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.
Form Controls…VFP

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:
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 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.
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.
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.
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
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.
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.
OLE Automation...

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 inprocess or out of process OLE Server. An inprocess 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.


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.
OLE Automation...

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

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
OLE Automation...


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.


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.
SQL Server Connectivity...

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.





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
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.
In Summary...
Visual Foxpro





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 “datacentric” 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.
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.
About...
Optimized Data Solutions
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
[email protected]
[email protected]
arrayWORKS




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
[email protected]
29