I have some old Access 2 files that Doug was kind enough to show me how to access via SQLSTRINGCONNECT() and SQLEXEC(). Through this guidance I'm able to get the cursors for the tables in the Access .mdb file.

From these cursors, I would like to create new tables in an existing (Visual Maxframe Professional) created .DBC. Once there, I would like to modify the newly created tables to eliminate .NULL. values in the empty fields and add a PK of my own choosing. Then I would like to zap these tables I just modified and append the data from the cursor so that I have properly created truly surrogate primary keys. This is all for the purpose of bringing the most contemporary data from this old Access database into my application so that the normal CRUD operations and Word Mailmerge can be performed.

I have tested this outside of Stonefield and the approach I took seemed to work until I tried to open the tables from within the .DBC or with SDT. Below is the procedure I wrote that works outside of STD and appears to work (I can browse the tables, etc.) but I get an Index error on the .DBC file when I try to open them in the project and I can't modify the tables or add values to the extended properties in Stonefield Database Tools. I would like to be able to do the same as the process in this procedure, but within the features offered by Stonefield Database Tools.

How can I accomplish this?

********************************************************
Procedure tableimport
********************************************************
* import records from Access 2 database CaseMgt.mdb for each table needed
* to already created tables in CV.DBC. Recieves cTableName and cPKFieldID as parameters
* from main program.
LPARAMETERS tcPassedTableName, tcPassedPKFieldID
lnRetVal = .F.
WAIT WINDOW "Creating, modifying and populating &tcPassedTableName" NOWAIT
* get the cursor for the passed CaseMgt.mdb table
lnRetVal = SQLEXEC(lnHandle, "Select * FROM &tcPassedTableName", "C_&tcPassedTableName", aCount&tcPassedTableName)
* test to see if table already exists in Database CV.DBC. If so, delete it.
IF INDBC("&tcPassedTableName", "Table")
REMOVE TABLE &tcPassedTableName DELETE
ENDIF
* copy cursor structure and data to database CV.DBC
COPY TO &tcPassedTableName DATABASE CV
* open newly created table and select to make sure we're where we want to be
USE &tcPassedTableName IN 0
SELECT &tcPassedTableName
* delete all records, close table and pack to remove all records
DELETE ALL
CLOSE TABLE
PACK DBF &tcPassedTableName
* open table again for modification and data import
USE &tcPassedTableName IN 0
SELECT &tcPassedTableName
* add the VMP necessary Primary Key
ALTER TABLE &tcPassedTableName ADD &tcPassedPKFieldID I AUTOINC PRIMARY KEY
* get data back from cursor to append with new Primary Key
APPEND FROM DBF("C_&tcPassedTableName")
* change NULL fields to NON NULL
iFieldCount = FCOUNT()
iFieldNumber = 1
FOR iFieldNumber = 1 TO iFieldCount
ALTER TABLE &tcPassedTableName ALTER (FIELD(iFieldNumber)) NOT NULL
ENDFOR