How to Modify a Table's Structure Programmatically in FoxPro

ID: Q135320

The information in this article applies to:

Microsoft FoxPro for Windows, version 2.6a

SUMMARY

This article shows by example how to change the structure of a data table programmatically.

MORE INFORMATION

Modifying the structure of a pre-existing data table in FoxPro is not possible in the run-time environment because the Modify Structure command is not supported. To modify a table structure, you must:

1. Copy the structure into an array or interim table.

2. Modify the particular element or field.

3. Create a table from this changed array or table.

Sample Code

Following is sample code that creates a table programmatically and copies its structure to an array. It then changes the length of one of the fields, and creates a table from the edited array.

* Create Table myTable

CREATE TABLE myTable;

  (firstname C(20),;
   lastname  C(20),;
   city   C(20))

INSERT INTO myTable VALUES("Eric","Cardenas","Sydney") LIST STRUCTURE LIST COPY TO myTemp

* Store structure into an array

=AFIELDS(arrTable)

Change length of first_name to 30

arrTable[1,3] = 30 setSAFETY = SET("SAFETY") SET SAFETY OFF

CREATE TABLE myTable;

  FROM ARRAY arrTable

APPEND FROM myTemp LIST STRUCTURE LIST SET SAFETY &setSAFETY

Using the COPY STRUCTURE EXTENDED Command

If you want to add or delete a field, it may be useful to us the COPY STRUCTURE EXTENDED command, and then work with the structure .DBF file. The COPY STRUCTURE EXTENDED command makes a table that has four fields: Field_name, Field_type, Field_len, and Field_dec. Each record of the table corresponds to a field in the original table.

Step-by-Step Example

1. Create a table named Test with the following format:

   field name    type     size
   ---------------------------
   cfield1        C        10
   cfield2        C        15
   nField1        N        4

2. Save the changes, and enter two or three records. The actual data is not

   important.

3. Create the following program:

   close all                           && clean up before starting
   use test                            && use the file just created
   copy structure extended to newstruc && copy its structure to a new table
   select 0                            && select...
   use newstruc                        && and use this new table
   locate for field_name = "CFIELD2"   && Find the record corresponding to
                                       && the second field

   * At this point, the record could be deleted, thereby removing the field
   * from the structure. For this example, just change its length. Changing
   * Field_len to a smaller number than in the original structure results
   * in a truncation of field data when you append from the original file.

   replace field_len with 20           && change the length from 15 to 20
   create newtest2 from newstruc       && create new table with the change
   append from test                    && append record from the original
   close all

   * uncomment the following code to remove the old table permanently and
   * rename the new table to the old tables name:

   * erase test.dbf                     && clean up after ourselves
   * erase newstuc.dbf
   * rename newtest2.dbf to test.dbf
   * erase newtest2.dbf

4. Run this program, and browse the Newstruc table. Notice that the field

   names are all capitalized. This is important. The LOCATE FOR command
   will not work if the sentinel value is not capitalized also.

5. Remove the comment asterisk from the last four lines of code if you want

   to rename the new file to the old file's name, and delete the temporary
   table created as well as the original table.

Additional reference words: FoxWin 2.60a structure modifying KBCategory: kbprg kbcode KBSubcategory: FxprgTable

Last Reviewed: August 22, 1995