Could you please tell me how to access data in a Visual Foxpro 6.0
table using VB6.  I need to know how to connect to the table
"customers.dbf" and retrieve all columns in the table using ADO. ie.
something equivalent to : Select * From tblCustomers.

I assume that you know how to use ADO under VB, and are just looking
for a way to access Foxpro6 tables with it. You can access FoxPro
tables under VB, using ADO, with the Microsoft Visual Foxpro ODBC
drivers.
 You will have to make sure this driver is installed on your client
computer. This driver is no longer shipped with MDAC versions greater
than 2.5!! If you want to install this driver with MDAC 2.6 or
greater, you must install MS Jet 4.0 Service Pack 3 Release . You can
download all the required files at :
   - Microsoft Universal Data Access Download Page
     ( http://www.microsoft.com/data/download.htm )
For more detailed information installation instructions, please read
this MS KnowledgeBase article :
  - INFO: MDAC Version 2.6 and Later Do Not Contain Jet or Desktop
ODBC Drivers
    ( http://support.microsoft.com/default.aspx?scid=KB;en-us;q271908
)

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

In the following example code, we will make use of the following
facts.
Database directory    : c:\database\
Database Content file : foxdb.dbc
Database Table file   : customers.dbf  
Table Name            : tblCustomers
You can just replace the above values with your own directory and file
names, and the code will work for you.

=========

The ConnectionString that we will use has a 'SourceDB' property. If
you are using a database container (*.dbc) then you will have to
specify the .dbc file to use here.So the connectionstring appears like
this :
"Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBC;" & _
"SourceDB=c:\database\foxdb.dbc;" & _
"Exclusive=No" 
 
However, if you are using a free table directory,ie, there is no .dbc
file , only a directory full of .dbf files, you will have to specify
the directory here. The connectionstring becomes :
        "Driver={Microsoft Visual FoxPro Driver};" & _
        "SourceType=DBF;" & _
        "SourceDB=c:\database\;" & _
        "Exclusive=No" 

If you want to open the table exclusively, just set 'Exclusive' to
'Yes' in the above string.

========================
Now for the code to open the table and read it :

'Open a new connection
   Dim cnn As New ADODB.Connection

   ' assign connection string. I am using the free tables version
   ' but you should replace it with the one you want
   cnn.connectionstring = "Driver={Microsoft Visual FoxPro Driver};" &
_
                           "SourceType=DBF;" & _
                           "SourceDB=c:\database\;" & _
                           "Exclusive=No" 

   cnn.open


' Declare and create a new recordset
    Dim RS as New ADODB.recordset
   
    ' Assign the open connection to the recordset
      rs.activeconnection = cnn

     'Now you can use the recordset object to access data from the
     'table, execute SQL queries etc.
      rs.open "SELECT * FROM tblCustomers"

      ' Process table data here
       < insert your code >


' Clean up
  set rs = nothing
  set cnn = nothing

================ END CODE SEGMENT ==================
And thats it! You can now open and use foxpro tables just like any
other database.
Hope this helped.
If you need any clarifications, just ask!
:)

RELATED LINKS
=============

 - MSDN : Visual FoxPro ODBC Driver
   ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/vfpodbcvisualfoxproodbcdriveroverview.asp
)
   Contains instructions on setting up and using the Microsoft Visual
Foxpro ODBC driver.

 - MS KB Article 165492 - HOWTO: Use ADO with a Visual Foxpro Database
   ( http://support.microsoft.com/default.aspx?scid=KB;en-us;q165492 )
   Provides VBScript code

 - Able Consulting : ADO Connection String Samples
   ( http://www.able-consulting.com/ADO_Conn.htm#ODBCDriverForVisualFoxPro
)

---

Request for Answer Clarification by tiewire-ga on 20 Nov 2002 10:20 PST

Thanks for getting back to me so quickly.  I finally got a chance to
go through the code and see if I could get it to work, but I haven't
been successful yet.

Here are some more details that should be helpful. 

I'm using Visual Studio.Net and ADO.Net. - Sory, I should have been
more specific earlier.  However, the logic that you have provided
should still work so I'm a little puzzled.  I downloaded and installed
the JET 4.0 SP3, as you suggested, then I created a connection object
and attempted to build a connection string, but the Visual FoxPro
Driver is not listed in the provider listing.  The Visual FoxPro ODBC
driver is installed on my system, so I'm not sure what is going on.

My OS is Win XP Pro.

Thanks for your help.  Please let me know if there is any other
information that I can provide that might be helpful.

---

Clarification of Answer by theta-ga on 20 Nov 2002 11:59 PST

Well, since you have Visual Studio.Net installed, you can just go
ahead and use the Visual Foxpro OLE DB Driver which ships with it. For
more info on this Driver, check out this MSDN Library Article :
  MSDN Library : Visual FoxPro > OLE DB Provider for Visual FoxPro
  ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/dggrfoledbproviderforvisualfoxpro.asp
)

To use this driver, you will have to change the connection string
specified in the above code to something like this :
   "Provider=vfpoledb;" & _ 
   "Data Source=C:\database\foxdb.dbc;" & _ 
   "Mode=ReadWrite|Share Deny None;" & _ 
   "Collating Sequence=MACHINE;" & _ 
   "Password=''" 

The following MSDN Article provides more info :
   MSDN :Using the Visual FoxPro OLE DB Provider to Access Data
   ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/dggrfoledbproviderforvisualfoxpro.asp
)

I hope this will solve all your problems.
Happy Coding!
:)

The answer was complete, detailed, and included links to helpful
sites.  Best of all, responses were received within hours of
submission.  -Thanks for the help!