Borislav -
That worked beautifully. I would have thought more was wrong. Thank you.
I've include the code below should others be in need of a method to import data from external tables to tables in the current database when fields are exactly the same and when additional update fields have been added to the end of the field list. It includes ZAP to quickly remove data from tables in the current database, so people need to be aware of the dangers involved. In prior code I notify users that all data in current tables will be lost and give them an option to backup data prior to starting the process. Also, it doesn't work for long field names and some of the code is CODEMINE.
Thanks again Borislav!
- Mike
LPARAMETERS importdir, curtable, oldtable
LOCAL old,cur,afieldname,afieldtype,afieldsize,zfieldtoadd,impadd
LOCAL zoldtable,zcurtable,impadd
impadd = importdir + oldtable + '.DBF'
IF ! FILE(impadd)
THIS.omessage.INFORM('Table ' + impadd + ' does not exits. ' + ;
'Continuing to import other tables.')
RETURN
ELSE
IF ! USED(curtable)
SELECT 0
USE (curtable) SHARED
ENDIF
IF ! USED(oldtable)
SELECT 0
USE (impadd) SHARED
ENDIF
old = FCOUNT(oldtable)
cur = FCOUNT(curtable)
zoldtable = "'" + oldtable + "'"
zcurtable = "'" + curtable + "'"
DO CASE
CASE old < cur
IF USED(curtable)
SELECT (curtable)
USE
ENDIF
SET SAFETY OFF
USE (curtable) IN 0 EXCLUSIVE
SELECT (curtable)
IF THIS.Zappend = .F.
ZAP
ENDIF USED(curtable)
SET SAFETY ON
IF USED(oldtable)
SELECT (oldtable)
USE
ENDIF USED(oldtable)
SELECT 0
USE (impadd) EXCLUSIVE
SELECT (curtable)
= AFIELDS(aMyStruc, (curtable))
DO WHILE old < cur
old = old + 1
afieldname = aMyStruc(old,1)
afieldtype = aMyStruc(old,2)
afieldsize = aMyStruc(old,3)
afielddeci = aMyStruc(old,4)
IF afielddeci # 0
zfieldtoadd = afieldname + [ ] + afieldtype + [(] + ;
TRANSFORM(afieldsize) + [,]+TRANSFORM(afielddeci) + [)]
ELSE
zfieldtoadd = afieldname + [ ] + afieldtype + [(] + ;
TRANSFORM(afieldsize) + [)]
ENDIF
SELECT (oldtable)
ALTER TABLE (oldtable) ADD COLUMN &zfieldtoadd
ENDDO
SELECT (curtable)
INSERT INTO (curtable) SELECT * FROM (oldtable)
CASE old > cur
THISFORM.omessage.INFORM('The number of import fields ' + ;
'exceeds number of fields in '+ curtable +'. Import failed.' + ;
' Data may have been corrupted. Import backup.')
RETURN
CASE old = cur
IF USED(curtable)
SELECT (curtable)
USE
ENDIF
SET SAFETY OFF
USE (curtable) IN 0 EXCLUSIVE
SELECT (curtable)
IF THIS.Zappend = .F.
ZAP
ENDIF USED(curtable)
SET SAFETY ON
SELECT (curtable)
INSERT INTO (curtable) SELECT * FROM (oldtable)
ENDCASE
THIS.cmdatamanager.updateall((curtable))
SELECT (curtable)
USE
USE (curtable) IN 0 SHARED
SELECT (oldtable)
USE
ENDIF