Command to add a field to the beginning of a database

Build your list of AccuZIP6 commands by requesting commands and sharing commands with other users

Moderators: Chrissy, Steve, Terry, Donna, Dave, Eric, MartyH, Kristen, Geoff, Vince

Post Reply
User avatar
Eric
Moderator
Posts: 1909
Joined: Tue Sep 19, 2006 3:29 pm
Contact:

Command to add a field to the beginning of a database

Post by Eric » Mon Nov 01, 2010 7:24 am

This command adds a field to your database, and inserts it at the beginning of the table structure.

Code: Select all

*********************************************
comNewFieldName="newfield"
comNewFieldSize=10
comNewFieldType="C"

*********************************************
*Get current database path
comDatabasePath=DBF()
*Construct New Field
comFieldString=comNewFieldName+" "+comNewFieldType+"("+allt(STR(comNewFieldSize))+")"
*Create temporary working file
comTempDbf1=FORCEEXT("t"+SUBSTR(SYS(2015),2),'dbf')
comTempDbf2=FORCEEXT("t"+SUBSTR(SYS(2015),2),'dbf')
*Make copy of current database structure
COPY STRUCTURE EXTENDED TO (comTempDbf1)
*Open structure database
USE (comTempDbf1) ALIAS "_stucture"
*Add new field
ALTER TABLE "_stucture" ADD COLUMN recnum N(3)
REPLACE ALL recnum WITH RECNO()
*Add blank record to structure database
APPEND BLANK
REPLACE field_name WITH comNewFieldName, field_type WITH comNewFieldType, field_len WITH comNewFieldSize
*Write new structure with new field at top
SORT TO (comTempDbf2) ON recnum FIELDS EXCEPT recnum
USE (comTempDbf2) ALIAS "_stucture"
SELECT * FROM "_stucture" INTO ARRAY _newdatabase
USE
*Create new database
CREATE TABLE (FORCEEXT(comDatabasePath,"")+"t.dbf") FROM ARRAY _newdatabase
*Append records from original database
APPEND FROM (comDatabasePath)
*Close new database
USE
*Rename original database with unique name
RENAME (comDatabasePath) TO (FORCEEXT(comDatabasePath,"")+SYS(2015)+".dbf")
RENAME (FORCEEXT(comDatabasePath,"")+"t.dbf") TO (comDatabasePath)
*Cleanup
DELETE FILE(comTempDbf1)
DELETE FILE(comTempDbf1)
*Open new database as original database name
USE (comDatabasePath)
Chat with an AccuZIP6 technician weekdays from 7-4 PST with AccuZIP6 Live Online Support

Post Reply