How to retrieve data from table using its key?

Accessing data in a Foxpro 6.0 table

• 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.

• Answer:

  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 )