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.

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

* Create a new table interactively
CREATE TABLE 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

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.xlsx" TYPE XL8

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

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

1. Always backup before structure changes:

COPY TO CustomerBackup WITH CDX

2. Use transactions for data safety:

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

3. 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 Browse Window

• Ctrl+Y - Delete current record
• Ctrl+R - Recall deleted record
• Ctrl+N - Append new record
• Ctrl+T - Toggle deleted records display
• Tab - Move to next field
• Shift+Tab - Move to 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.