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 application 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