Microsoft KB Archive/135320

{| = How to Modify a Table's Structure Programmatically in FoxPro =
 * width="100%"|

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
 * }