Thanks, Art. I am renewing my VMP subscription for one more year as I think it might help me in my migration a bit as well. I'm running that code as just a command window do ??? but am trying to build a migration tool. This is my first go at it and after much more research over the weekend, I think I mainly needed to add a tableupdate() to the code. It is pretty rough at the present and I've even rethought my normalization over the weekend to de-normalize a bit and just have a juvenile defendant table instead of making them a child of person. I'll use the person table for all other parties because few of the juveniles have work phones, fax numbers, etc. and they have unique numbers in the system that other parties don't have.

As you can tell, I'm just getting started and back to writing VFP code for the first time since we worked together after Drew died.

Hope this answers your question.

Nice to hear from you, Art.
Hi Dick,

Welcome back!

From where are you running the code below?

Hi All,

Long time away. I'm doing a migration of my VFP applications to MS SQL and tyring to accomplish better normalization in the process. Here's what I'm doing. My connection to the MS SQL database is working correctly and I get the c_juvperson view. However, my replacements are not being successful. Can anyone offer a suggestion as to what I need to correct? It has been a long, long time since I did any VFP programming and I'm quite stale (what else at 66). :-) I'm getting a message that no update tables are specifided at the INSERT command point.

hConn = 0
lnStatus = 0
intjuvPersonPK=0
*!* Open juvtemp1.dbc database and juvtmp1.dbf table using index nameuniq (unique name index for normalization)
OPEN DATABASE Q:\DATABASE\JUVENILE\Data\juvtemp1.dbc
USE juvtemp1!juvtmp1 INDEX nameuniq
*!* Create connection to MS SQL database Juvenile11
hConn= SQLSTRINGCONNECT("DSN=SQL2008ODBC;Database=Juvenile11;UID=dickw;Trusted_Connection=Yes;APP=Microsoft Visual FoxPro;WSID=DA2DEVX")
IF hConn < 0
WAIT WINDOW "Connection Was Not Made"
CANCEL
ELSE
=SQLEXEC(hConn,'SELECT * FROM juvPerson', "c_juvperson")
=CURSORSETPROP("Tables", "juvperson")
*!* Create updateable cursor for table Person
** The next property must include every remote field matched with the
** view cursor field.
=CURSORSETPROP("UpdateNameList", "personpk c_juvperson.personpk, lastname c_juvperson.lastname, middlename c_juvperson.middlename, firstname c_juvperson.firstname, dateofbirth c_juvperson.dateofbirth, gender c_juvperson.gender, socialsecuritynumber c_juvperson.socialsecuritynumber")
=CURSORSETPROP("KeyFieldList", "personpk")
** The next property specifies which fields can be updated.
=CURSORSETPROP("UpdatableFieldList", "personpk, lastname, firstname, middlename, dateofbirth, gender, socialsecuritynumber")
** The next property enables you to send updates.
=CURSORSETPROP("SendUpdates", .T.)
ENDIF
*!* Select the juvtemp1!juvtmp1 table and go to the first record in the index
SELECT juvtmp1
*!* Skip loop through the records to the end of the file
DO WHILE .NOT. EOF()
chrfirstname=""
chrmiddlename=""
* Check to see if the record contains a blank - if so skip record
IF LEN(ALLTRIM(lastname)) = 0
SKIP
LOOP
ENDIF
* increment PK value
intJuvPersonPK = intJuvPersonPK + 1
* Scatter the record
SCATTER MEMVAR
* Insert new record in MS SQL remote Juvenile!Person autoincrementing the PK
* Parse the current firstname field value to separate into first name and middle name (rest of string)
IF ATC(" ", ALLTRIM(m.firstname),1) > 0
chrfirstname=SUBSTR(m.firstname,1,ATC(" ", ALLTRIM(m.firstname),1)-1)
chrmiddlename= SUBSTR(firstname,ATC(" ", ALLTRIM(firstname),1)+1,50)
ELSE
chrfirstname = m.firstname
chrmiddlename = ""
ENDIF
* Insert values into updateable ??? cursor
INSERT INTO c_juvperson (personpk, lastname, firstname, middlename,dateofbirth, gender, socialsecuritynumber) VALUES (intJuvPersonPK, m.lastname, chrfirstname, chrmiddlename, m.dob, m.sex, m.social_sec)
SKIP
LOOP
ENDDO
SELECT c_juvperson
BROWSE
SQLCOMMIT(hConn)
*During development of this program browse the Juvenile!Person and juvtmp1.dbf tables
* to verify proper migration
* Close all and clear all
* SQLDISCONNECT(hConn)
* CLEAR ALL
* CLOSE ALL
MESSAGEBOX("juvmigperson completed",0,"Juvmigperson.prg Success")

Thanks,

Dick Wade
Office of Criminal District Attorney
Hidalgo County, Texas