Example: Linking a FoxPro Database

The following example steps you through linking a FoxPro database to a Microsoft Jet database. The example follows the steps outlined in the previous section, “Creating a Link to an External Table.”

Note If you link a FoxPro 3.0 table to a Microsoft Jet database and then view the connection string in table Design view in Microsoft Access, the specifier in the connection string appears as FoxPro 2.0. Microsoft Access uses the FoxPro 2.0 specifier for both FoxPro 2.x and 3.0 tables.

The first step is to open a Microsoft Jet database. In the following line of code, strDbPath is the path to the database that will contain the linked table:

Dim dbs As Database, tdf As TableDef

Set dbs = OpenDatabase(strDbPath)

The second step is to create the table definition:

Set tdf = dbs.CreateTableDef("EmployeeSalesQ1")

The third step is to set the connection information:

tdf.Connect = "FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables\Sales"
tdf.SourceTableName = "EmpSales"

Note You can use the Connect property to specify the database name and the SourceTableName property to specify the table name for all IISAM drivers. For external data sources that store one table per file, the Connect property should contain the full directory or network path to the file, and the SourceTableName property should contain the data file name with no extension. For external data sources that store multiple tables in a file, the Connect property should contain the full directory or network path to the file including the file name and extension; the SourceTableName property should contain the table name.

The fourth and final step is to append the TableDef object to the TableDefs collection:

dbs.TableDefs.Append tdf

The following example establishes the connection, appends the linked table, and tests the data to make sure that it can be accessed. In this example, strDbPath is the path to the database that will contain the linked table:

Dim dbs As Database
Dim tdf As TableDef, qdf As QueryDef, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
' Create a table definition for the FoxPro table.
Set tdf = dbs.CreateTableDef("EmployeeSalesQ1")
With tdf
	' Provide source database type and name with Connect property.
	.Connect = "FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables\Sales"
	' Provide name of FoxPro table.
	.SourceTableName = "EmpSales"
End With

' Append TableDef object to the TableDefs collection to create the link.
dbs.TableDefs.Append tdf

' Run a simple query to ensure the data is accessible.
Set qdf = dbs.CreateQueryDef("")
qdf.SQL = "SELECT Sum(Sales) As TotalSales FROM " _
	& "EmployeeSalesQ1;"
Set rst = qdf.OpenRecordset()
MsgBox "Total Sales = " & Format(rst!TotalSales, "Currency")

See Also The FoxPro database discussed in this chapter can be found in the JetBook\Samples\FoxTables sample file folder on the companion CD-ROM.