The LA Fox Developer Newsletter

April2000

ADO Jumpstart (Con’t from page 4)

as well.

If you opened a VBA application:

1. .From the View menu, choose Toolbars.

2.From the Toolbars menu, choose Visual Basic.

3.On the Visual Basic toolbar, click Visual Basic Editor.

4.From the Tools menu, choose References.

5.Check the Microsoft Data Access Objects 2.x Library.

If you opened the Visual Basic IDE:

I .Create an empty project

2.From the Project menu, select References.

3.Check the Microsoft Data Access Objects 2.x Library.

Now, whether you are in the VBA Editor or the VB IDE

1.Press F2 to display the Object Browser.

2.ln the first combo box, select ADODB.

3.ln the second box, type ADVARCHAR

4. Press Search or Press Enter.

Clearly, the Object Browser is a powerful tool for the developer

who works with COM components. Not only are the defined

properties, events, and methods accessible in the Object

Browser, so also are the defined constants and their respective

values. Notice the value of adVarChar in the lower pane of the

Object Browser.

Opening, Sorting, and Filtering Data

One of the big advantages of using a development platform such

as Visual FoxPro is its local data engine. Not only does the

engine provide superior query performance, but it also provides

some very flexible capabilities when it comes to both working

with and presenting data. There isn’t a Visual FoxPro applica-

tion that fails to sort or filter data to some degree. In Visual

FoxPro, sorting is accomplished by creating a set of index tags

for a table. Filtering is accomplished by using the Set Filter

command. Fortunately, ADO has these capabilities as well.

You can see in the Field Attribute table that the availability of

features depends on the location in which the recordset is

created. It is clear that we must ensure that a client-side

recordset is created.

For example, create a Connection object to the TasTrade or

SQL Server Northwind database. The following code assumes

that the Connection object, oConnection, has been created

before you open the Recordset object.

First, we need to implement a few required #Defines:

#Define adUseClient 3

#Define adLockBatchOptimistic 4

#Define adCmdTable 2

For SQL Server:

With oRecordset

.Source = “Customers”

.ActiveConnection = oConnection

.CursorLocation = adUseClient

.LockType = adLockBatchOptimistic

.Open

EndWith

Or

oRecordset.Open(”Customers”,;

oConnection,;

adUseClient,;

adLockBatchOptimistic)

For Visual FoxPro:

With oRecordset

.ActiveCon nection = oConnection

.Source = “Customer”

.Cursorlype = adOpenStatic

.Locktype = adLockReadOnly

.CursorLocation = adUseClient

.Open(,,,,adCmdtable)

EndWith

Or

oRecordset.Open("Customer",;

oConnection,;

adUseClient,;

adLockBatchOptimistlc,;

adCmdTable)

Or

With oRecordset

.ActiveConnection = oConnection

.Source = “Select * From Customer”

.CursorType = adOpenStatic

.Locktype = adLockReadOnly

.CursorLocation = adUseClient

Open

EndWith

Or

oRecordset.Open(”Select * From Customer”,;

oConnection,;

adUseClient,;

adLockBatchOptimistic)

SQL Server and Visual FoxPro open data differently. Remember

that when using SQL Server, you are using the OLE DB Provider

for SQL Server. When you access data in Visual FoxPro, use

the OLE DB Provider for ODBC, since there is no native OLE

DB provider for Visual FoxPro.

The difference rests with the optional fifth argument of the Open

method. The SQL Server OLE DB Provider is designed to

recognize when you pass just a table name. With the ODBC

OLE DB Provider, you must specify how it should interpret the

Source property. By default, the ODBC OLE DB Provider

expects a SQL statement. When you pass a SQL statement,

there is no need to explicitly state how the provider should

(Con’t, page 6)

Page 5