Add field to Existing DB in FoxPro 2.6

use the table exclusively
type modi struc in the command box
the rest should be obvious

I've used two techniques, the first works for all field types except memo fields. Basically it uses the SQL Select capability to create new fields. Some sample code follows:

lcdbf = "customer"
SELECT 0
USE (lcdbf)
IF ! chkfldx("fax&quot
SELECT *, SPACE(13) AS fax, SPACE(3) AS country, ;
SPACE(7) AS user_id, {} AS LastUpdate, ;
SPACE(25) as email, 0000.00 as CustCred, ;
.F. as NewUser ;
FROM (lcdbf) ;
INTO TABLE xtemp.dbf
USE IN xtemp
USE IN (lcdbf)
ERASE (lcdbf+".DBF&quot
RENAME xtemp.dbf TO (lcdbf+".DBF&quot
ELSE
USE IN (m.lcdbf)
ENDIF
.....

FUNCTION chkfldx && Check for field already eXisting in DBF
PARAMETERS zcfield
PRIVATE lnii
zcfield = UPPER(ALLTRIM(zcfield))
FOR lnii = 1 to FCOUNT()
IF ALLTRIM(FIELD(lnii)) == zcfield
RETURN .T.
ENDIF
ENDFOR
RETURN .F.

Adding fields to Customer.DBF
fax C(13)
country C(3)
user_id C(7)
LastUpdate D
email C(25)
CustCred N(7,2)
NewUser L

Another way is to use AFIELDS() to get the field information, re-DIMENSION the array for the number of additional fields. Then fill in the additional field information, and use this with CREATE TABLE ... FROM ARRAY to create an empty table with the new structure. Finally use APPEND to transfer the data from the "old" table to the new one.

A variant of this technique is to use COPY STRUCTURE EXTENDED to create a table with the field information, add new records for the new fields and use CREATE <newtable> FROM <structTable> to make the new table. Finish again by transfering the data.

All techniques will likely require you to recreate the indexes (.CDXs) - especially if the new fields are indexed.

Rick

Dtaylor,
If I am understanding your delima correctly, you need the code to add the field for you, because it is created dynamically? But this is difficult, due to convolution...
Couple of questions:
1) What are you planning to &quot;Do&quot; with the field once it is added.
2) Does the tables structure get &quot;Re-created&quot; every time the program is run?
3) Can you break the &quot;Convoluted&quot; code down into smaller chunks. (That may require some work, but I would think will benifit you in the long run, as you may need to make other changes in the future.)

Thanks,
-Scott

Please let me know if this has helped! s-)

Hiya Visitor,

It all depends on what you're trying to accomplish.

If you are simply adding a field to a database using your FoxPro development environment, select the database and modify its structure using the <Setup> functionality from either the menu, or the view window (or, indeed, the command window by typing MODIFY STRUCTURE).

If you're looking for a way to 'upgrade' a database from within a program, here's what I do:
- I create an empty database with the extra field ; and put that in an 'upgrade' directory i send along witht he updated exe file.
- From an upgrade procedure, I first PACK the old database (otherwise the append will copy the DELETED records too, and happily mark them as not deleted *boggle*) and then APPEND ALL the records from the old database to the new empty database (handy if you need to do some REPLACE ALL instruction to set things up roight after setting up the new datafile).
- From the same procedure, I then close and rename the original, delete it, and rename the new database.
- In order to check if everything went okay, and to have a backup of the original data, you can of course always copy the original file before deleting.
- Don't forget to toss out the index files and make new ones.

A practical, and less convoluted way I think..

Best regards,

Jan Schenkel.

&quot;As we grow older, we grow both wiser and more foolish at the same time.&quot; (De Rochefoucald)

Hello all, I am still working on this... My problem is the table is created in this program based on a criteria from the screen selection... This is an extract program... I have found the location in the program where I need to make the change but I am not sure how to create a new table based on the fields from the temporary table from the program since based on the selection criteria the actual contents and structure of the database can change...
If I use AFIELDS() to get the total of fields and AFIELDS(tablestructure) to also get the structure, Use a COPY STRUCTURE EXTENDED command (Or re DIMENSION), How do I utilize this with the ability of adding two additional fields and how do I code the addition of the two fields...
I will be performing two other selects to fill these fields after I have APPENDED the contents of the temporary table to it...

Assume we want to add 4 new fields:
newchar C(6)
newnum N(7,2)
newlog L
newdate D
....
SELECT 0
USE oldtable
lnFields = AFIELDS(laFields)
USE && close oldtable

DIMENSION laFields[(lnFields+4), 4]
laFields[lnFields+1, 1] = &quot;NEWCHAR&quot;
laFields[lnFields+1, 2] = &quot;C&quot;
laFields[lnFields+1, 3] = 6
laFields[lnFields+1, 4] = 0 && doesn't really matter
laFields[lnFields+2, 1] = &quot;NEWNUM&quot;
laFields[lnFields+2, 2] = &quot;N&quot;
laFields[lnFields+2, 3] = 7
laFields[lnFields+2, 4] = 2
laFields[lnFields+3, 1] = &quot;NEWLOG&quot;
laFields[lnFields+3, 2] = &quot;L&quot;
laFields[lnFields+3, 3] = 1 && doesn't really matter
laFields[lnFields+3, 4] = 0 && doesn't really matter
laFields[lnFields+4, 1] = &quot;NEWDATE&quot;
laFields[lnFields+4, 2] = &quot;D&quot;
laFields[lnFields+4, 3] = 8 && doesn't really matter
laFields[lnFields+4, 4] = 0 && doesn't really matter

CREATE TABLE newtable FROM ARRAY laFields

APPEND FROM oldtable

** Whatever you what to add data to old records' new fields (default values) and add on any the &quot;new&quot; records.

** You'll probably want to delete the old table and rename the new one

Note: I haven't added the code to check if the &quot;new&quot; field already exists in the table, but using ASCAN() that shouldn't be hard.

Rick

I figured it out... Thanks
I used the Copy Structure Extended/Append
I didn't know that it actually dumps it into a table and you just add a row... I was trying to add a column...Once I started playing with it and viewed the tables it all came together...

Thanks a bunch...

hey Rick,

I used your last suggestion:

use table
copy structure extended newtable
use newtable
append blank
replace newtable.field1 with &quot;contents&quot;
replace newtable.field2 with &quot;contents&quot;
replace newtable.field3 with &quot;contents&quot;
replace newtable.field4 with &quot;contents&quot;
create temptbl from newtable
append from table
delete file table.dbf
rename temptbl.dbf to table.dbf
delete file newtable.dbf
close all
quit

I still have to go thru the db and fill the new fields...
Thanks Rick...