Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Special Edition Using Visual FoxPro 6

Special Edition Using Visual FoxPro 6 -- Ch 8 - Accessing Remote Data

Special Edition Using Visual FoxPro 6


Chapter 8

Accessing Remote Data




Introduction to Remote Data Access

Visual FoxPro has a very powerful database engine built into it. When you deal with data stored in the Visual FoxPro dbf tables, you are using what is called local data. Even though Visual FoxPro has this local data engine, in some situations the data you need to deal with is not stored in local tables. At these times, the remote data handling capabilities of Visual FoxPro 6 will save your day.

In this chapter, you will see that Visual FoxPro has a variety of options for dealing with remote data. You can use ODBC data sources, Connections, ADO, or RDO to get at data that is stored outside Visual FoxPro. The best method depends on a number of factors, which are discussed in this chapter.

There is no single correct method. Each method has it own strengths and weaknesses. This chapter covers the strengths and weaknesses of each method so that you have the information to make decisions for your particular projects.

What is Remote Data?

Remote data refers to any data that is not being accessed directly by Visual FoxPro. In most cases, remote data refers to data stored in products such as Microsoft SQL Server or Oracle or any of the many other database server products. However, database servers are not the only kind of remote data.

You might need to access data stored in a Microsoft Access database file. You can even access a Visual FoxPro database remotely.

In using local data, you usually put the tables you want in a form's data environment. Then the form's data environment will USE those tables for you when you run the form. With remote data, you cannot place the table directly into the data environment; you must handle accessing the data externally to the form by using remote views or SQL Pass Through.

Visual FoxPro 6 can use two different technologies to access remote data. The two technologies have a lot in common, as one uses the other internally. The two technologies available are Open Database Connectivity (ODBC) and Distributed Component Object Model (DCOM).

DCOM uses ODBC to access its data. In Visual FoxPro, you can either use ODBC directly, or you can leverage the bigger feature set of DCOM and use ODBC indirectly.

ODBC

As you saw in the preceding section, ODBC is an acronym for Open Database Connectivity. ODBC is a technology, built in to the Windows 95/98 and Windows NT environments, which enables two different database systems to share data with each other.

ODBC works based on the ODBC engine in Windows. The ODBC model is divided into three separate layers. One layer is the ODBC engine in Windows. The other two are referred to as ODBC drivers, one for the data consumer and the other for the data source.

The specifications of ODBC make it possible for a vendor, such as Microsoft, to provide an ODBC consumer driver without having specific knowledge of the data source that will be used. This is possible because the ODBC specifications deal only with the consumer talking to the ODBC engine.

Vendors of data sources can also provide ODBC source drivers without knowing what the consumer will be because the ODBC data source driver has to communicate only with the ODBC engine. The ODBC engine is a switchboard of sorts that receives requests from an ODBC consumer driver and passes them on to an ODBC data source driver.

Figure 8.1 is a block diagram of the ODBC process.

Figure 8.1:

Data Sources  Because ODBC is a service of Windows, before you can access any ODBC data sources, you must first set up the data source driver in Windows.

ODBC drivers are usually available with the data sources you have, or you can find third-party vendors that sell ODBC drivers for many products. The speed of data access through ODBC is a direct result of the drivers being used. If ultimate speed is a goal, you might be well served to evaluate the third-party drivers available.

The process of setting up an ODBC driver is not difficult, although you will need to know some information about the data source that the driver will access.

The ODBC Administrator  To set up an ODBC driver, you use the Windows ODBC Administrator. Figure 8.2 shows the 32-bit ODBC Administrator.

Figure 8.2 : The ODBC Administrator with the User DSN tab visible.

In the ODBC Administrator, choose the Add button. The dialog box you get will look similar to Figure 8.3.

Figure 8.3 : The ODBC driver selection dialog box in the ODBC Administrator.

The first step in setting up an ODBC data source is to select the ODBC driver that you want to use. The available drivers on your computer might be quite different from those you see in Figure 8.3 because the list shows the ODBC drivers that have been installed on the computer. You might need to use the installation disks from the data source vendor to install the appropriate ODBC drivers before starting this process.

For this example, use the Microsoft Access driver. Figure 8.4 shows the dialog box we get after selecting the Microsoft Access driver.

Figure 8.4 : The setup dialog box for the Microsoft Access ODBC driver.

The setup dialog box that you get depends on the ODBC driver you've chosen. For the example, you only have to choose the Select button under the Database prompt. This action brings up the dialog box shown in Figure 8.5.

Figure 8.5 : The database selection dialog box for the Microsoft Access ODBC driver.

In Figure 8.5, I selected an Access database that is installed on my computer. You might or might not have that same database installed on your computer.

After you select the database, click OK in the database dialog box and also click OK in the ODBC driver setup dialog box. Figure 8.6 shows how the ODBC Administrator looks now on my computer.

Figure 8.6 : The ODBC Administrator after I added our new data source.

That is all there is to creating a data source in ODBC. As an example of another ODBC driver setup dialog box, Figure 8.7 shows the one for Microsoft SQL Server.

Figure 8.7 : The first page of the Microsoft SQL Server ODBC Driver Setup Wizard.

The process of setting up the driver for Microsoft SQL Server involves a number of steps sufficient for the driver to use a wizard to assist you. Included in the steps are elements such as a login name and password, and whether you want the data source to display SQL Server dialog boxes when it attempts to connect to the SQL Server database. The issues that are addressed by the setup process will vary based on the requirements of the particular data server you are using.

Oracle's setup dialog box would be different from the SQL Server dialog box shown in Figure 8.7. It is beyond the scope of this book to present and explain every possible ODBC driver you might encounter. Look to the documentation of the product you are connecting to get the information you need to set up its ODBC driver.

Creating Remote Views in Visual FoxPro 6

Now that your data source is set up in ODBC, you can create a remote view in Visual FoxPro. To do so, open a database in the Database Designer, and right-click the designer. The shortcut menu you get is shown in Figure 8.8.

Figure 8.8 : The Database Designer shortcut menu.

Select the New Remote View option. If you see a dialog box asking whether you want the View Wizard or a new view, choose New View. Figure 8.9 shows the next dialog box.

Figure 8.9 : The data source and connection selection dialog box.

Using a Data Source

In Figure 8.9, notice that we have selected the Available Data Sources radio button to get the dialog box to show us the data source we have created with the ODBC Administrator. For now, don't concern yourself with connections; we will cover them later in this chapter.

At this point, select My Remote Test Data Source and click OK. You will see the Open dialog box shown in Figure 8.10.

Figure 8.10: The Open dialog box with the tables from the My Remote Test Data Source database.

From this point forward, creating a remote view is exactly the same as creating a local view. You are now in the Visual FoxPro View Designer. The only difference is that the data is coming from a remote source through ODBC.

You finish creating the view by doing the same things you did in Chapters 6, "Creating Basic Queries," and 7, "Advanced Queries and Views." You select the tables, set the joins, select the fields, set the filter criteria, and set all the other options you want for this view.

Figure 8.11 shows our database after we have created two remote views from the Northwind.mdb sample database that Microsoft supplies with Access. The two remote views are named rvcust and rvorders. We use the naming convention of prefixing local views with lv and remote views with rv.

Figure 8.11: The Database Designer with two remote views in it.

Now we are going to create a local view that joins the two remote views. To do so, right-click the Database Designer, and select New Local View. When the Add Table or View dialog box appears, change the Select option from Tables to Views, as is done in Figure 8.12.

Figure 8.12: The Add Table or View dialog box with the Views option selected.

You see the views that are in the database in the list. Select rvcust by double-clicking it; then select rvorders the same way. After you select the two views, choose Close to close the dialog box. Figure 8.13 shows the Join Condition dialog box, which you will see next.

Figure 8.13: The Join Condition dialog box with the join condition for the two remote views.

Notice that the dialog box contains a default join condition that is based on the customerid field in rvcust_b being joined to the customerid field in rvorders. Where did Visual FoxPro come up with this? VFP makes this assumption because the two fields share the same name and data type. Because these views get their data from outside Visual FoxPro, they have no persistent relations that VFP can use to determine a default join condition. Accept the join shown, and click OK.

Now simply do the same things you would for any other local view. Select fields, filter conditions, group by conditions, and order by conditions.

To see the results of the local view, right-click it and select Browse. Figure 8.14 shows the browse that we saw.

Figure 8.14: The browse of our local view.

As we just joined data from two remote views into a local view, you can also join data from a remote view and a local view or even from a remote view and a local table. These joins can be done in local views as long as the proper relationships exist in the data.

Using a Connection

The next method you will see for accessing remote data is using a connection. A connection is a predefined remote data source that has been stored in a Visual FoxPro database. You create connections by using the Connections option on the database shortcut menu (as shown earlier in Figure 8.8). Choosing this option brings up the dialog box in Figure 8.15.

Figure 8.15: The Visual FoxPro Connections dialog box used to select a connection for a remote view.

In this dialog box, you should select New to access a dialog box like the one in Figure 8.16.

Figure 8.16: The Visual FoxPro Connection Designer.

The first area to investigate in the Connection Designer is in the upper-left corner directly under Specify Data Source. There, you'll find two option buttons: Data Source, Userid, Password and Connection String. These options will control how you go about defining the connection. First, we will use the Data Source, Userid, Password option.

With this method, the next thing you need to do is select a data source from the Data Source combo box below the option buttons. The data sources listed in that combo box are those that have previously been set up using the ODBC Administrator described earlier in this chapter.

If you change the Specify Data Source option to Connection String, the set of text boxes for Data Source, Userid, Password, and Database are replaced with one large text box. In the large text box, you type the connection string exactly as it should be sent to the database server you are going to connect to.

The three text boxes beside the Data Source combo box are used to enter a user ID, password, and the name of the database you want to use, respectively. Some remote data sources require that a login is accomplished before the data can be accessed. In these cases, the user ID and password are used to log in to the database server. The values you use here, in the Connection Designer, will be given to you by the database administrator in charge of your database software. Both the user ID and the password can be supplied at a later time when you try to use the connection.

Just below the data source information is a section titled Display ODBC Login Prompts. There, you'll find three choices: When Login Information Is Not Specified, Always, and Never. These options correspond to showing the login dialog box from ODBC when the connection did not specify a user ID and password, always showing the login dialog box, and never showing the login dialog box. The option you choose here depends on the design of your data access for your application.

Some applications are designed with a user ID and password for the application set up in the database server. In these applications, you should probably choose Never, as you would supply the user ID and password in the connection definition.

In other designs, each user of the system might have his or her own user ID and password within the database server. In these cases, you might or might not want the login dialog boxes to appear, depending on whether you are storing the user IDs and passwords and supplying them at the time you connect to the server.

You might want to use the first option to handle situations in which your system doesn't have login information for a user. The first option will display the login dialog box if, and only if, you don't supply the user ID and password when you try to connect.

Table 8.1 describes the options available to you in the Data Processing section in the lower left of the Connection Designer dialog box.

Table 8.1  The Data Processing Options of the Connection Designer
Option
Description
Asynchronous ExecutionEnables the data for this connection to be fetched in the background. This setting can be very valuable when a remote view might need to fetch a large number of records from the data source and you want your application to continue while the fetching goes on in the background. Unchecking this option will cause any remote view to completely get all its data before your application code will continue.
Display WarningsDetermines whether ODBC warning dialog boxes will be displayed when using this connection. You would set this option off if you are using error handling in your application to deal with the warning conditions that might arise during a remote connection.
Batch ProcessingDetermines if the view will return all records together or if it will receive records a few at a time. With Batch Processing on, your view will not get any records until the data source has finished processing your SELECT command and is ready to send you all the records. Turning Batch mode off will allow the data source to return partial results as it gets them ready for you.
Automatic TransactionsTurns on or off automatic transactions. Some database servers can use automatic transactions when processing table updates, deletes, and inserts. If you are connecting to a data source that can use automatic transactions, this option either turns them on or off. You will most likely want this option turned on unless you are using SQL Pass Through and not remote views. (SQL Pass Through is discussed later in this chapter.)
Packet SizeControls the size of the data packets sent back and forth on the connection. You can use this setting to optimize the connection for performance based on the probable size of the various queries that might hit the connection. Increasing the packet size will cause more data to be sent in each packet. Decreasing it will reduce the packet size. What size is best depends on a number of issues-among them, what is the average size of a record or set of records being retrieved over this connection. If the data size is large, then increase the packet size so the connection uses fewer packets. If the data size is small, then reduce the packet size so the connection uses the bandwidth of the network more efficiently.

The last set of options in the Connection Designer consists of the Timeout Intervals in the lower-right corner of the dialog box. Table 8.2 describes these options.

Table 8.2  The Timeout Intervals in the Connection Designer
Option
Description
Connection (sec):The time in seconds before Visual FoxPro will report a connection timeout error.
Idle (min):The Idle timeout period in minutes after which Visual FoxPro will break the connection. The connection is not broken if the connection is in manual transaction mode and a transaction is in process.
Query (sec):The time to wait, in seconds, before reporting a general timeout error.
Wait Time (ms):The time, in milliseconds, that Visual FoxPro will wait before checking to see whether the SQL statement has completed.

By setting all these options, you can customize the connection that will be used for your remote views. After you have completed these settings, you can click OK. In the next dialog box you see, you can give the connection a name. We named ours MyTest.

If you now right-click the Database Designer and select New Remote View, you will see that your connection is listed in the Select Connection or Data Source dialog box. You can select the connection and then go on to the View Designer to define the view.

When you use one of these remote views, Visual FoxPro will make a connection through ODBC to the database server's ODBC driver and execute the SELECT statement defined by the view. The data retrieved will be in a Visual FoxPro cursor, and you can work with the contents of that cursor as you choose.

Remote views are capable of everything that local views are. You can update them, you can use the Top-N features, and so on.

In the next section, you will see how you can directly manage everything that is going on with remote data access by using SQL Pass Through.

Using SQL Pass Through Functions

In Visual FoxPro, SQL Pass Through is a set of functions that allow you to send commands directly through ODBC. The code in Listing 8.1, which comes from the program SQLPT.prg, uses SQL Pass Through to access the data source you saw earlier in this chapter.


Listing 8.1  08code01-Using SQL Pass Through to Access the My Remote Test Data Source
LOCAL lnHandle
lnHandle = SQLConnect("My remote test")
IF lnHandle > -1
     SQLExec(lnHandle,"SELECT * FROM Customers","Results")
     SELECT Results
     BROWSE
     SQLDisconnect(lnHandle)
ENDIF

In Listing 8.1, you can see the use of the SQLConnect() function to establish a link to the data source. The handle to this connection is saved in the variable lnHandle. The IF checks to see whether the connection was successful. If the handle is a negative number, it indicates that the connection was not successful.

If the connection was successful, the SQLExec() function is used to pass a SELECT statement to the data source, and the results of that SELECT are stored in a cursor named Results. Finally, the Results cursor is selected and browsed. When the browse is closed, the SQLDisconnect() is issued to drop the connection to the data source.

In Listing 8.1, you see three SQL Pass Through functions being used: SQLConnect(), SQLExec(), and SQLDisconnect(). Many more SQL Pass Through functions than these three are available. For example, the SQLStringConnect() function uses a connection string rather than a data source. Table 8.3 lists all the SQL Pass Through functions and describes their use.

Table 8.3  The SQL Pass Through Functions
Function
Description
SQLConnect()Creates a connection to an ODBC data source. Takes one or three arguments. If using a connection stored in the current Visual FoxPro database, a single argument of the connection name can be used. When not using a connection name, the three arguments are the Data source name, the user ID, and the password. SQLConnect returns a connection handle or -2 to indicate that the connection was unsuccessful.
SQLStringConnect()Similar to SQLConnect(), but it uses a single argument of a connection string. Like SQLConnect, it returns a connection handle or -2 for failure.
SQLDisconnect()Accepts a single argument of the connection handle to disconnect. Shuts down a connection. Returns 1 if successful, -1 if an error occurred with the connection, and -2 if an error occurred with an environment error. SQLDisconnect will cause an error if you try to disconnect from a connection that has a transaction in process.
SQLCancel()Requests cancellation of an executing SQL command. Return types are the same as SQLDisconnect. The single argument is the handle for the connection to cancel.
SQLExec()Sends a SQL command to the data source for the connection. Takes three arguments: the first is the connection handle, the second is the command to be executed, and the third is the name of the Visual FoxPro cursor to hold the results. The return value is 1 for a successful execution and -1 if an error occurred.
SQLMoreResults()Returns more results from a command executed with SQLExec() when in non-batch mode. This function accepts the connection handle as an argument and returns 0 if the command is still executing, 1 if the command is finished executing, and 2 if it finds no more results sets to get. A return of -1 indicates an error on the connection, and -2 indicates an error in the environment.
SQLPrepare()Prepares a SQL command for execution with SQLExec(). SQLPrepare() takes three arguments: the connection handle, the SQL command to prepare, and the name for the results cursor. This function can be used to prepare a parameterized view in the data source. All parameters must exist before issuing the SQLPrepare() function call. To use the prepared command, issue the SQLExec() and supply only the connection handle.
SQLCommit()Commits a transaction on the data source. Takes one argument of the connection handle. Returns 1 if the commit was successful and returns -1 if an error occurred. You can use AERROR() to get information about the error that occurred.
SQLRollback()Cancels a transaction in the data source. Takes the same argument and returns the same values as SQLCommit().
SQLColumns()Stores the field names in a Visual FoxPro cursor. Takes three arguments: the connection handle, the table name from which to return fields, and either FOXPRO or NATIVE to specify the format of the field information. FOXPRO is the default format. The resulting cursor has four fields in it for Field_Name, Field_Type, Field_Len, and Field_Dec. When you're using the NATIVE format, the fields of the cursor will vary depending on the data source for the connection. Returns 1 when finished successfully, 0 while still executing, -1 for a connection error, and -2 for an environment error. Consider this example: SQLTables(lnHandle, "'VIEW', 'SYSTEM TABLE'", "Results"). In this example, we are getting a list of all Views and System Tables from the data source.
SQLGetProp()Takes two arguments: the connection handle and the setting you want returned. Used to read the properties of the remote connection.
SQLSetProp()Takes three arguments: the connection handle, the setting you want to change, and the new value for the setting. The settings that can be changed using SQLSetProp() are listed in the Visual FoxPro help file.

The big question you should be asking now is, "When should I use SQL Pass Through instead of remote views?" In answering this question, you need to know about the data source and its capabilities and the way in which you plan to use the data from that source.

The advantages of SQL Pass Through are as follows:

The disadvantages of SQL Pass Through are as follows:

Based on the advantages and disadvantages of SQL Pass Through, you should be prepared to decide when to use the technology and when to use remote views. Although SQL Pass Through gives you more complete control over the access of data in a remote data source, it also requires that you manage the connections.

When comparing SQL Pass Through to an equivalent remote view, you will find no noticeable difference in speed. In fact, Microsoft has said that remote views might be the faster of the two approaches in most cases. However, using the SQLPrepare() function to establish a remote parameterized view that is precompiled on the server can cause a performance improvement with SQL Pass Through.

SQL Pass Through also enables you to take advantage of certain features of the data server that remote views cannot access. They include data definition, administration of the server, and other features that are unique to the particular data server you are connecting to.

Using DCOM

DCOM is an acronym for Distributed Component Object Model. DCOM is the Microsoft technology that enables you to create an object that is running on another machine as an ActiveX, or COM, object and then access the properties and methods of that object.

ADO Versus RDO

COM, which is an acronym for Component Object Model, is the predecessor of DCOM. COM objects are created on the same machine that uses them, whereas DCOM objects might be created on a different machine.

The data access technologies that COM and DCOM use are named Active Data Object (ADO) and Remote Data Object (RDO), respectively. RDO, which is an expansion of ADO, enables access to data that is stored on another machine. Both ADO and RDO can be used in Visual FoxPro 6 for accessing data.

In this section, we will build a form that uses RDO to access the data in our My Remote Test data source. To the form, we add a property named oRDS to hold an object reference to the RDO. We also add properties for Company and Contact with assign methods. Listing 8.2 shows the form's Init event.


Listing 8.2  08CODE02-The Form's Init Method Code
This.oRDS = CreateObject("RDS.DataControl")
If Type("This.oRDS") <> "O"
     MessageBox("oRDS not created")
Else
     With This.oRDS
         .Connect = "dsn=My Remote Test"
         .SQL = "Select * from customers"
         .ExecuteOptions = 1
         .FetchOptions = 2
         .Refresh
     EndWith
     This.ShowValues()
EndIf

This code starts by creating an instance of the RDS.DataControl class, which is the class for Remote Data Sets. The IF checks to see whether the RDS was successfully created. If it was, then the code in the ELSE is executed.

The ELSE code sets some properties of the RDS object. The properties being set are Connect, which stores the name of the data source to connect to; SQL, which holds the SELECT command to be run; ExecuteOptions, which controls how the SQL results will be processed; and FetchOptions, which controls the fetching method used. All these properties are described later in this section.

Notice the call to the form's ShowValues method. The ShowValues method was added to the form, and the code for it is shown in Listing 8.3.


Listing 8.3  08CODE03-The Form's ShowValues Method Code
With This
     .txtCompany.Value = ;
     .oRDS.Recordset.Fields("CompanyName").Value
     .txtContact.Value = ;
     .oRDS.Recordset.Fields("ContactName").Value
EndWith
This.Refresh

This method requests data from the RDS by referring to its Recordset.Fields(#).Value property and storing the fields value in the Value property of the text boxes. We are only using two of the fields in the recordset: CompanyName and ContactName.

The Company property of the form is used to set the CompanyName for the current record in the RDS record set. The assign method for the Company property is shown in Listing 8.4.


Listing 8.4  08CODE04-The Company_assign Method Code
LPARAMETERS vNewVal
*To do: Modify this routine for the Assign method
THIS.COMPANY = m.vNewVal
This.oRDS.Recordset.Fields("CompanyName").Value = ALLTRIM(vNewVal)

You can see that the code here is storing the value to the Company property of the form and updating the RDS CompanyName field with the value as well. The RDS field is updated by referring to the Recordset.Fields("CompanyName").Value property.

The navigation buttons in the form have code in them to tell the RDS to go to a new record. Listing 8.5 shows the code from the Next button's Click event.


Listing 8.5  08CODE05-The Next Button's Click Event Code
ThisForm.oRDS.RecordSet.MoveNext
If ThisForm.oRDS.RecordSet.Eof
     ThisForm.oRDS.RecordSet.MoveLast
EndIf
ThisForm.ShowValues

This code calls the RDS RecordSet.MoveNext method and then checks to see whether the end of file was encountered. If end of file was encountered, it moves to the last record. The code finishes by calling the form's ShowValues method, just like before. The code in the other navigation buttons is similar except for the record movement itself.

The Save button has one line of code in it:

ThisForm.oRDS.SubmitChanges()

This line calls the RDS SubmitChanges() method, which submits the changes to the data source.

To better understand the power of RDO, review Table 8.4 to learn the RDS properties and Table 8.5 to learn the RDS methods.

Table 8.4  The RDS Properties
Property
Description
ConnectConnects to a data source. Takes three arguments: data source name, user ID, and user password.
ExecuteOptionsSets the synchronous or asynchronous mode for executing the SELECT command. A value of 1 is synchronous, and a value of 2 is asynchronous.
FetchOptionsSets the type of asynchronous fetching. Accepts three values: 1 to fetch up front, 2 to fetch in the background, and 3 to fetch asynchronously.
FilterColumnSets or returns the column for the filter criterion. The actual criterion is specified in the FilterCriterion property.
FilterCriterionSets or returns the filter criterion as a string. The values available are <, <=, >, >=, =, and <>.
FilterValueSets or returns the filter string. Holds the value with which to apply FilterCriterion.
InternetTimeoutSets the number of milliseconds to wait before timing out a request.
ReadyStateRepresents the progress of RDS.DataControl as it fetches its results. Can be one of three values: 2, the query is still loaded and no records have been fetched; 3, the initial set of rows has been fetched, and the remaining rows are still being fetched; or 4, all rows have been fetched.
RecordSet and SourceRecordSet Sets the RecordSet returned by the SQL SELECT command. You can manipulate the data in the recordset by manipulating the RecordSet property of the RDS.
ServerSets or returns the Internet Information Server name and protocol for the data source.
SortColumnSets or returns the column used for sorting the records in the recordset.
SortDirectionSets or returns a logical value of .T. for ascending sort or .F. for descending sort.
SQLSets or returns the SQL SELECT command used to retrieve the recordset.
URLSets or returns the location of a file that stores the persistent or saved recordset.

Table 8.5 contains the methods of the RDS.DataControl.

Table 8.5  The Methods of the RDS.DataControl
Method
Description
CancelCancels the currently running asynchronous fetch.
CancelUpdateCancels all updates made to the recordset, restoring the recordset to its state at the last refresh.
CreateRecordSetCreates an empty recordset.
MoveFirstMoves to the first record in the current recordset.
MoveLastMoves to the last record in the current recordset.
MoveNextMoves to the next record in the current recordset.
MovePreviousMoves to the previous record in the current recordset.
RefreshRequeries the record source and updates the recordset.
ResetExecutes the filter and sort criteria for the recordset on the client side. Takes one argument of logical: .T. uses the current filtered rowset; and .F. uses the original rowset, discarding any previous filter options.
SubmitChangesUpdates the data source from the current cached recordset.

Using these properties and methods, you can manage the data returned in an RDS.DataControl.

User Interface Concerns with Remote Data

Any discussion would be incomplete if we were to describe remote data access and not describe the impact remote data access has on user interface design. The issue in user interface design related to remote data access is that a remote data source might have a very large number of records in it.

Imagine a Microsoft SQL Server database that has 10,000,000 customer records in it. Retrieving all the customer records with remote data access is not practical or desirable. Instead, your user interface should provide a method for the users to specify which customer records they want and then only get that number of records from the data source.

This type of interface design precludes this old style:

USE Customer
BROWSE

Instead, you need to provide controls for the users to specify criteria for selecting the customer records desired and then use a parameterized remote view or an RDS.DataControl with a filtered SELECT statement to retrieve the records.


© Copyright, Sams Publishing. All rights reserved.