How to Create and Update Databases and Tables in Visual FoxPro

Bob Fox • July 13, 2022

Updated June 11, 2026

feature

Visual FoxPro provides powerful database management capabilities through both its command window and visual interface. This guide covers essential database and table operations with practical examples.

New to VFP? If you haven't installed it yet, start with How to Set Up a New Visual FoxPro Installation.

Free Tables vs. Database Tables

Visual FoxPro has two kinds of tables, and the difference matters for several commands below:

Free tables are standalone .dbf files that don't belong to a database.
Database tables belong to a database container (.dbc) and unlock features free tables don't have — PRIMARY KEY constraints, DEFAULT values, field- and row-level rules, long field names, and persistent relationships.

A few file extensions you'll run into along the way:

Extension	What it is
`.dbc` / `.dct` / `.dcx`	Database container, its memo, and its index
`.dbf`	A table
`.fpt`	Memo and general field data for a table
`.cdx`	Structural compound index for a table

Setting the Working Directory

Before creating or modifying databases, always set your default directory:

* Set the default directory
SET DEFAULT TO C:\MyProject\Data

Creating a New Database

Using the Command Window:

* Create a new database
CREATE DATABASE MyDatabase

* Create database with specific path
CREATE DATABASE "C:\MyProject\Data\MyDatabase.dbc"

Using the Menu:

Go to File → New
Select Database
Choose location and enter database name
Click Save

Creating Tables

Method 1: Command Window with Structure Definition

* Open the database first
OPEN DATABASE MyDatabase

* Open the Table Designer to build a table interactively
* (CREATE without the TABLE keyword opens the designer)
CREATE Customers

* Or create with structure in one command
CREATE TABLE Products ;
    (ProductID I PRIMARY KEY, ;
     ProductName C(50), ;
     Price N(10,2), ;
     InStock L, ;
     LastUpdate D)

Method 2: Using SQL CREATE TABLE

* Create table using SQL syntax
CREATE TABLE Employees ;
    (EmpID Integer AUTOINC PRIMARY KEY, ;
     FirstName Varchar(30), ;
     LastName Varchar(30), ;
     HireDate Date, ;
     Salary Currency, ;
     Active Logical DEFAULT .T.)

Method 3: Visual Table Designer

Go to File → New → Table
Choose New Table
Use the Table Designer to add fields visually

Common Field Types

Type	Name	Notes
`C(n)`	Character	Fixed-length text, up to 254 characters
`V(n)`	Varchar	Variable-length text (VFP 9+)
`M`	Memo	Unlimited text, stored in the `.fpt`
`I`	Integer	4-byte whole number
`N(w,d)`	Numeric	Number with width `w` and `d` decimals
`B`	Double	Floating-point number
`Y`	Currency	Money values, four decimal places
`D`	Date	Date only
`T`	DateTime	Date and time
`L`	Logical	`.T.` / `.F.`
`G`	General	OLE / binary objects

Add AUTOINC to an Integer field to auto-number it. Remember that PRIMARY KEY, DEFAULT, and field rules only work on tables stored inside a database (.dbc).

Opening and Modifying Existing Databases

Open Database:

* Open database in current directory
OPEN DATABASE MyDatabase

* Open database with full path
OPEN DATABASE "C:\MyProject\Data\MyDatabase.dbc"

* Open database exclusively
OPEN DATABASE MyDatabase EXCLUSIVE

Modify Database Structure:

* Open Database Designer
MODIFY DATABASE

* Or specify database name
MODIFY DATABASE MyDatabase

Working with Tables

Open and Modify Table Structure:

* Open a table
USE Customers

* Open exclusively for structure changes
USE Customers EXCLUSIVE

* Modify table structure
MODIFY STRUCTURE

* Or use shorter command
MODI STRU

Important: Exclusive Access Required

* Close all tables before modifying
CLOSE TABLES ALL

* Open exclusively for modifications
USE Customers EXCLUSIVE
MODIFY STRUCTURE

Adding and Importing Data

Adding Records:

* Add a blank record
APPEND BLANK

* Add record and edit immediately
APPEND

* Add from another table
APPEND FROM OldCustomers FOR Active = .T.

* Import from different formats
APPEND FROM "data.csv" TYPE CSV
APPEND FROM "data.xls" TYPE XL8   && XL8 = Excel 97-2003 (.xls)

VFP can't import the newer Excel .xlsx format directly — convert it to .xls or .csv first.

Browse and Edit Data:

* Open browse window
BROWSE

* Browse with specific fields
BROWSE FIELDS FirstName, LastName, Email

* Browse with condition
BROWSE FOR State = "CA"

* Browse in edit mode
BROWSE NOEDIT  && Read-only
BROWSE NORMAL   && Full edit

Useful Table Commands

Data Manipulation:

* Replace values
REPLACE ALL Price WITH Price * 1.10 FOR Category = "Electronics"

* Delete records (mark for deletion)
DELETE FOR InStock = .F.

* Recall deleted records
RECALL ALL

* Permanently remove deleted records
PACK

* Copy table structure
COPY STRUCTURE TO NewTable

* Copy with data
COPY TO BackupTable WITH CDX

DELETE only marks records for deletion — they stay in the table until you PACK. Turn on SET DELETED ON so marked records are hidden from BROWSE, queries, and most commands.

Index Management:

* Create index
INDEX ON LastName TAG LastName

* Create compound index
INDEX ON UPPER(LastName + FirstName) TAG FullName

* Reindex all indexes
REINDEX

Best Practices

Always backup before structure changes:

COPY TO CustomerBackup WITH CDX

Use transactions for data safety:

BEGIN TRANSACTION
* Make your changes
UPDATE Customers SET Active = .F. WHERE LastOrder < DATE()-365
END TRANSACTION

Transactions only protect tables that belong to a database (.dbc) — changes to free tables aren't logged.

Close tables when done:

USE IN Customers  && Close specific table
CLOSE TABLES ALL  && Close all tables
CLOSE DATABASES ALL  && Close all databases

Common Hotkeys in the Browse Window

Ctrl+Y - Append a new record
Ctrl+T - Toggle the deletion mark on the current record (deletes it, or recalls it if already marked)
Ctrl+Home / Ctrl+End - Jump to the first / last record
Tab / Shift+Tab - Move to the next / previous field

Troubleshooting Tips

If you get "File is in use" error, ensure exclusive access:

SET EXCLUSIVE ON
USE Customers

To see current database and tables:

? DBC()  && Shows current database
LIST STATUS  && Shows open tables

To repair corrupted tables:

USE Customers EXCLUSIVE
PACK
REINDEX

This comprehensive approach to database and table management in Visual FoxPro will help you efficiently organize and maintain your data.

Want to go deeper? Browse the Top 7 Visual FoxPro Resources for communities, books, and tools to keep learning.