dBase

dBase and FoxPro

Driver: Microsoft OLE DB Provider for Visual FoxPro 9.0 (Recommended)
2010 Office System (ODBC) Driver: Access Database Engine (32-bit)

SQL Language: Visual FoxPro SQL Commands and Functions

The Office System (ODBC) Driver provides limited support.

Column Types

Foxpro Column Type Information.

foxpro column types
FoxPro provider types

Qualifiers can be added to the provider type. For example,

D DEFAULT (DATE( ))

will specify that a field defaults to the date the record was appended.

If ISAUTOINCREMENT flag was set, the type

I AUTOINC

is used.

Free DBF tables and DBC databases

If you specify a DBF file, or just a folder that contains DBF files, the dBASE data source uses free tables. If you specify a DBC file, the dBASE data source connects as a database. Free tables have limited support compared to a DBC database. Full key and index support requires a DBC database. If a DBC is not used (you are using free tables instead), table names are restricted to 8 characters.

If KEYCOLUMN flag is also set, the type

I AUTOINC PRIMARY KEY

is used. When Natural Order is selected as the Update Key in the Data Link dialog,

WHERE RECNO( )= nRow

is used.

Indexes and columns with names longer than 10 characters can be truncated automatically. Subsequent updates will require that the .OL also contain shortened column names, otherwise the columns will not match. To automatically recover the truncated names, use Reload All.

Sample Migration SQL

Load Customers from Nwind.mdb, as in the Crash Course Tutorial . Replace the connection string with a link to a FoxPro database, for example Provider=VFPOLEDB.1;Data Source=C:\DATA\FLASK.DBC . A Write Data command would create a table using:

CREATE TABLE [Customers] (
CustomerID C(5) NOT NULL,
CompanyName C(40) NOT NULL,
ContactName C(30),
ContactTitle C(30),
Address C(60),
City C(15), Region
C(15), PostalCode
C(10), Country
C(15), Phone
C(24),
Fax C(24)
)

and create keys and indexes using

ALTER TABLE [Customers] ADD PRIMARY KEY CustomerID
USE [Customers]; INDEX ON CompanyName TAG [CompanyNam]; USE
USE [Customers]; INDEX ON City TAG [City]; USE
USE [Customers]; INDEX ON Region TAG [Region]; USE
USE [Customers]; INDEX ON PostalCode TAG [PostalCode]; USE

to create indexes and a primary key. After creating the table, Write Data Preview (SQL) would produce

-- updating Customers (customerid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax)
UPDATE [Customers] SET address='87 Duval St.'+CHR(13)+CHR(10)+'Suite 5' WHERE customerid='LETSS'
-- update complete (1 of 91 rows had changes)

after Polk were changed to Duval in cell 45,5 (LETSS, address). Note that the cell contains a newline, CHR(13)+CHR(10).

DML (INSERT, UPDATE) commands have a binary literal (i.e. 0hBEEF) length restriction of 255.

Sample Test Migration: CategoriesFoxpro.sql (text).