How to Modify a Table's Structure Programmatically in FoxPro

• 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))

* 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

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.