I'm trying to access a Foxpro table from VB using ADO 2.0.  Does anyone know
how?  Specifically the Provider?

Thanks,
Scott

Here's an example I developed for VBA.  Should work for VB as well:

==============================================

Sub Open_VFPDBC_WITH_ADO()

'===========================================================================
========
   '  PURPOSE:    Document Example of opening a VFP Database (.DBC)
   '              using ActiveX Data Objects (ADO) from a MSFT Office
   '              app (VBA)
   '
   '  NOTE:       ADO is much more flexible, much faster, and more memory
efficient
   '              than using ODBCDirect connections
   '              This example was developed and run in Excel 97 VBA.
   '
   '  CONNECTION STRINGS
   '
   '     This example shows how to open the VFP DBC using:
   '        1.  ODBC System DSN
   '        2.  ODBC File DSN
   '        3.  Bypassing DSN's and using ODBC Driver statement
   '
   '     The 3rd option is very attractive because it allows the developer
to open
   '     the DB programmaticly without requiring the end user to setup any
ODBC DSNs.
   '
   '  REFERENCES:
   '
   '     1. My profound thanks to Rod Paddock for calling my attention to
using ADO
   '        and providinga clear example of how to use ADO:
               'Rod Paddock

               'Microsoft MVP / MCP
               'President Dash Point Software, Inc.
               'co-author Visual FoxPro 5/6 Enterprise Development
               'co-author VB 5/6 for Web Development

    '    2. MSDN Visual Stuido 6.0 Library Help Files
    '       This provides a good reference and examples.  Showed me how to
setup
    '       Options 2 and 3.  No help was available in the Excel VBA editor.
    '
    ' DEVELOPED BY
    '
    '    Jim Underwood (based on example from Rod Paddock)
    '    Apollo Information Systems, Inc.
    '    Houston, Tx

    '    9/18/98

'===========================================================================
==========

Dim oConn As ADODB.Connection
Dim oRecs As ADODB.Recordset

Dim lcConnect As String, lnOption As Integer

Set oConn = CreateObject("ADODB.Connection")

lnOption = InputBox("Choose Connection String (1,2, or 3)", "Test ADO", 3)

Select Case lnOption

   Case 1      '*** OPTION #1 -- SYSTEM DSN ***
   lcConnect = "TestData_Sys"

   Case 2      '*** OPTION #2 - FILE DSN ***
   lcConnect = "FileDSN=R:\MS_Office\Excel\VFP5_Data\Testdata.dsn"

   Case 3      '*** OPTION # 3 -- ODBC DRIVER ***
   lcConnect = "Driver={Microsoft Visual FoxPro Driver};" _
                & "UID=;" _
                & "PWD=;" _
                & "SourceType=DBC;" _
                & "SourceDB=R:\MS_Office\Excel\VFP5_Data\Testdata.dbc;" _
                & "Exclusive=No;" _
                & "BackgroundFetch=Yes;" _
                & "Collate=Machine"
End Select

'----------------------
'   OPEN THE VFP DBC
'----------------------

oConn.Open lcConnect, "", ""

'---------------------------------------------
'  OPEN A CURSOR (TABLE, VIEW, OR SQL SELECT)
'---------------------------------------------

Set oRecs = CreateObject("ADODB.Recordset")
oRecs.Open "Select * from customer", oConn, 3, 3

'------------------------------------
'  PROCESS THE RECORDS IN THE CURSOR
'------------------------------------
Do While Not oRecs.EOF

Debug.Print oRecs!Company

oRecs.MoveNext    '*** MOVE TO NEXT RECORD (SKIP) ***

Loop

'-------------------
'  RELEASE OBJECTS
'------------------

Set oConn = Nothing
Set oRecs = Nothing

End Sub

=============================================

-- HTH,
jmu

Jim Underwood
Apollo Information Systems, Inc.

Houston, TX

VFPODBC.DLL
-A

To my dismay, I just found out that Microsoft does not have a Foxpro/DBF OLE
DB Provider!!!!!  The only way you can connect using ADO is via ODBC
provider. However I was able to find out that "Sequiter" makes a Foxpro/DBF
OLE DB Provider. You can find this information on :

http://www.microsoft.com/data/oledb/products/product.htm

Scott,

What problem or limitation do you see with using ODBC provider for ADO
access to FoxPro?

In the example I posted previously, this worked very fast.

Best Regards,
jmu

--

Jim Underwood
Apollo Information Systems, Inc.

Houston, TX

Hi:

I am having the same kind of problem using ODBC provider for ADO Access to
Foxpro. I am developing a Visual Basic 6 application that needs to look
information from a large Foxpro 2.5 database (over 1 million records). I
found out that it takes more than 10 minutes!!! to look a record, say at the
690500th position. Even Visdata (that ships with Visual Basic) was not
faster. The big question is how can you make it faster without using a
third-party ocx (unless it's free -- no budget for now). Is it possible to
use the index defined in Foxpro? Is it possible to use the FOXPRO SEEK
command from Visual Basic?

Thanks
Fidelis Ekezue

Hi:

I am having the same kind of problem using ODBC provider for ADO Access to
Foxpro. I am developing a Visual Basic 6 application that needs to look
information from a large Foxpro 2.5 database (over 1 million records). I
found out that it takes more than 10 minutes!!! to look a record, say at the
690500th position. Even Visdata (that ships with Visual Basic) was not
faster. The big question is how can you make it faster without using a
third-party ocx (unless it's free -- no budget for now). Is it possible to
use the index defined in Foxpro? Is it possible to use the FOXPRO SEEK
command from Visual Basic?

Thanks
Fidelis Ekezue

Hi:

I am having the same kind of problem using ODBC provider for ADO Access to
Foxpro. I am developing a Visual Basic 6 application that needs to look
information from a large Foxpro 2.5 database (over 1 million records). I
found out that it takes more than 10 minutes!!! to look a record, say at the
690500th position. Even Visdata (that ships with Visual Basic) was not
faster. The big question is how can you make it faster without using a
third-party ocx (unless it's free -- no budget for now). Is it possible to
use the index defined in Foxpro? Is it possible to use the FOXPRO SEEK
command from Visual Basic?

Thanks
Fidelis Ekezue

Hi:

I am having the same kind of problem using ODBC provider for ADO Access to
Foxpro. I am developing a Visual Basic 6 application that needs to look
information from a large Foxpro 2.5 database (over 1 million records). I
found out that it takes more than 10 minutes!!! to look a record, say at the
690500th position. Even Visdata (that ships with Visual Basic) was not
faster. The big question is how can you make it faster without using a
third-party ocx (unless it's free -- no budget for now). Is it possible to
use the index defined in Foxpro? Is it possible to use the FOXPRO SEEK
command from Visual Basic?

Thanks
Fidelis Ekezue

Is the column you are retreiving indexed? Is the search expression in the
WHERE clause the same, EXACTLY the same, as the index expression. Is the
index column to the left of the = sign in the WHERE clause? Are you using =
or are you using LIKE?  There  are some basic rules to get Rushmore
optimzation to kick in.

Why no make a middle tier VFP DLL with the methods you need and use that
from VB?

-Anders

    Foxpro is a filebased database. Therefor it's slow, at least slower than
    Oracle or SQLserver.

    Using a very large database like you do, is slow. Try to move the
    data to an SQLserver if you have the budget.

    Seek functions are slower than queries. avoid them

        FB

When you use a good seek on a local drive, it is faster than any SQL-server,
ever. On network drivers a seek can be very very fast, even with large
tables. Since the fox engine wil search indexed, it will only  load data
that it needs for the indexed search (which will be mainly a part of your
CDX file). VFP does _not_ have to load the entire DBF file over the network.
Nothing beats a seek as long as you limit your network traffic which you do
when you only load one record.

So far so good. Now, what are your options:
- Use VFP automation, let VFP seek data for you (look at the DoCmd function)
and pass the data via the clipboard, arrays or custom properties to VB. Not
an elegant solution but workable and as flexible as you need.
- Use VFP-API functions. For this, you'll have to create an interface DLL in
C that can call Fox functions. This is fairly good documented in VFP docs.
Then, in VB, you can call any of those functions.

If you need more information to get started, let me know.

--
Robert van Geel