>Hi all,
>
>I'm working on migrating a system from alpha4 to vfp.
>
>In order to do this I need to import a number of tables and create primary keys for them before I start breaking them up and normalizing the whole thing.
>
>I created a form to make things easier, but for some reason it doesn't work reliably.
>
>The process, in a nutshell, is this:
>
>1. I have a table called basestru.dbf, which I created with copy structure extended to basestru from a table that only had on integer field called iid. This will be the primary key in the new tables.
>
>2. I open the table I want to import and issue a copy structure extended to \tempstru. (now tempstru holds the structure of the table).
>
>3. I append all the records from \tempstru to basestru so now basestru holds the iid field + all the fields in the source table.
>
>4. Then I create the destination table by issuing a CREATE newtable FROM basestru. (this creates the new table, which has the IID field and then all the fields from the source table.
>
>5. Finally, I open the newtable and append all from sourcetable.
>
>Works everytime from the command window, but when I added the code below to a button on a form, it works sometimes. Other times, I get a dest table that only has the iid field. Other times I get a dest table that has the same fields as the previous iteration. What am I missing here?
>
>Thanks!
>
>Alex
>
>* this is the code on my form.
>if file("\tempstru.dbf")
> messagebox("Deleting temp structure table")
> delete file \tempstru.db*
>endif
>
>* Check if dbf is already in database. We don't wan't to
>* overwrite anything.
>if indbc( juststem( thisform.destfile.value), "table")
> messagebox( alltrim( thisform.destfile.value) + " already exists in database")
> return .f.
>endif
>
>* Select source dbf
>if used( juststem( thisform.sourcefile.value))
> select ( juststem( thisform.sourcefile.value))
>else
> use (thisform.sourcefile.value) in 0
Instead, try:
SELECT 0
USE (thisform.sourcefile.value) ALIAS SYS(2015)
>endif
>
>* Create extended structure dbf from source file
>* Save it in temp file.
>copy structure extended to \tempstru
>
Add:
USE
>
>* Basestru.dbf holds the extended structure of a table
>* with just an integer field called "iid".
>* Now let's append the records from tempstru to basestru
>* and create the new table
>
>if used("basestru")
> select basestru
>else
> use data\basestru in 0
Instead, try:
SELECT 0
USE data\basestru
>endif
>
>* Here we add the fields from tempstru.dbf to basestru.dbf
>append from \tempstru
>
The following block is now not needed:
>if used ("basestru")
> select basestru
>else
> use data\basestru in 0
>endif
>
since you're already on and using the file
>* Create the actual destination file (it will now have
>* the iid field).
>create (thisform.destfile.value) from basestru
>
>* Make sure dest file is open and selected.
>if used( juststem( thisform.destfile.value))
> select ( juststem( thisform.destfile.value))
>else
> use (thisform.destfile.value) in 0
Again:
SELECT 0
USE (thisform.destfile.value)
>endif
>
>* Append all the data from the source file to the dest file.
>append from (thisform.sourcefile.value)
>
>* Generate primary keys for dest table.
>replace all iid with recno()
>
>* Close the dest table.
>if used( juststem( thisform.destfile.value))
> use in (thisform.destfile.value)
>endif
>
>* Close the source table.
>if used( juststem( thisform.sourcefile.value))
> use in (thisform.sourcefile.value)
>endif
>
>* Clean up basestru so it's ready for the next import.
>* (it should have only one record - for the iid field).
>if used( "basestru")
> select basestru
>else
> use data\basestru in 0
>endif
>
>delete all for recno() > 1
>pack
>use in basestru
This whole thing could be written in a far more straightforward fashion:
USE (thisform.sourcefile.value) AGAIN ALIAS Source IN 0
SELECT RECNO()+000000000 AS IID, * FROM Source INTO TABLE (thisform.destfile.value)