>I’m converting an Access database application into a VFP6.0 application. I need to convert the Access data to a VFP database container containing several VFP tables. I already have the VFP application running and the VFP tables created. I now need to write the conversion program that will read the Access data into the VFP tables. I am wondering what is the best route to take on this. I’m thinking that I would dump the Access data into an Excel Spreadsheet and then use the APPEND FROM command to pull that data into a Foxpro table? I would then use this Foxpro table (and the various Foxpro data manipulation commands) to import the data to the VFP database that supports the new VFP application. This is basically the route I use to use when importing data into Foxpro 2.X applications. Is this the best route to take in VFP6.0? Is there a better option? Perhaps Access to XML and then XML to the Foxpro tables?
>
>Thanks, David

I know I'm entering this thread late, but if you need to pull note fields (which will not convert over to Excel) you can use ODBC via the SQLSTRINGCONNECT() function in VFP. The following code lists the tables,views,etc. in an access database, and included is a function to pull data from a table in an Access database into a VFP cursor, OpenAccessTable()

* list Access tables in a cursor sql_Tables
* uses GSQLDATASRC as a private variable to hold the name of the 
*   Access database.
FUNCTION GenTableList()
LPARA tcType

PRIVATE GSQLDATASRC

IF VARTYPE(tcType) # "C" OR EMPTY(tcType)
  tcType = ""  && valid types include "TABLE", "VIEW", "SYNONYM"
ENDIF

IF ListAccessTables("sql_Tables",tcType) AND USED("sql_Tables")
  SELECT sql_Tables
  BROWSE NORMAL
  USE IN sql_Tables
ENDIF


FUNCTION ListAccessTables
LPARA tcAlias, tcWhatKind
LOCAL lcDataFile, laTemp[1], lnDataSrc, lnResult, lcTableName

IF VARTYPE(tcAlias) # "C"
	tcAlias = "sqlTables"
ENDIF
IF VARTYPE(tcWhatKind) # "C"
	tcWhatKind = "TABLE"
ENDIF

IF VARTYPE(gSqlDataSrc) # "C"
	gSqlDataSrc = GETFILE("Access database(*.mdb):MDB", "database:" , "Open", 0, "Where is Access Data?")
ENDIF
lnResult = 1
IF LEN(gSqlDataSrc) > 0 AND 1=ADIR(laTemp, gSqlDataSrc)
	&& set default to be synchronous You can set this for the particular data source after connecting to it
	lnResult = SQLSETPROP(0, "Asynch", .F.)  
	lnDataSrc = SQLSTRINGCONNECT("DSN=MS Access Database;DBQ=" + gSqlDataSrc + ;
		";DriverId=281;FIL=MDB;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;")
	IF lnDataSrc <= 0
		= MESSAGEBOX('Cannot connect to Data Source', 16, 'SQL Connect Error')
		RETURN .F.
	ELSE
		WAIT WINDOW NOWAIT 'Data Source Connection made'
		Result = SQLTABLES(lnDataSrc, tcWhatKind, tcAlias) && get a list of tables in the data source
		=SQLDISCONNECT(lnDataSrc)
	ENDIF
ENDIF
RETURN (lnResult > 0)
ENDFUNC

FUNCTION OpenAccessData()
LPARA tcTableName, tcAlias, tcWhereClause
LOCAL lcDataFile, laTemp[1], lnDataSrc, lnResult, lcTableName

IF VARTYPE(gSqlDataSrc) # "C"
	RELEASE gSqlDataSrc
	PRIVATE gSqlDataSrc

	gSqlDataSrc = GETFILE("Access database(*.mdb):MDB", "TrustDat:" , "Open", 0, "Where is TrustDat.mdb?")
ENDIF
lnResult = 1
IF LEN(gSqlDataSrc) > 0 AND 1=ADIR(laTemp, gSqlDataSrc)
	&& set default to be synchronous You can set this for the particular data source after connecting to it
	lnResult = SQLSETPROP(0, "Asynch", .F.)  
	lnDataSrc = SQLSTRINGCONNECT("DSN=MS Access Database;DBQ=" + gSqlDataSrc + ;
		";DriverId=281;FIL=MDB;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;")
	IF lnDataSrc <= 0
    = MESSAGEBOX('Cannot connect to Data Source', 16, 'SQL Connect Error')
		RETURN .F.
	ELSE
		WAIT WINDOW NOWAIT 'Data Source Connection made'
		IF VARTYPE(tcTableName) = "C" AND NOT EMPTY(tcTableName)
			IF VARTYPE(tcAlias) # "C" OR EMPTY(tcAlias)
				tcAlias = tcTableName
			ENDIF
			IF VARTYPE(tcWhereClause) # "C"
				tcWhereClause = ""
			ENDIF
*        lnResult = SQLTABLES(lnDataSrc, "TABLE", "sqlTables") && get a list of tables in the data source
			lnResult = SQLTABLES(lnDataSrc, "", "sqlTables") && get a list of tables in the data source
			LOCA FOR ATC(tcTableName,NVL(TABLE_NAME,"")) > 0
			IF NOT FOUND()
				= MESSAGEBOX(tcTableName+' Not Found in '+CHR(13)+gSqlDataSrc, 16, 'Table Not Found')
			ELSE
				IF " " $ tcTableName OR "-" $ tcTableName
					tcTableName = ["]+tcTableName+["]
				ENDIF
				SET TALK WIND
				SET TALK ON
				lnResult = SQLEXEC(lnDataSrc, 'SELECT * FROM '+tcTableName+' NOUPDATE '+;
					tcWhereClause,tcAlias)
				SET TALK OFF
IF lnResult <= 0
  ACTIVATE SCREEN
  =AERROR(laTemp)
  DISPLAY MEMORY LIKE laTemp
*    _CLIPTEXT = ""
*    FOR EACH luSomething IN laTemp
*      _CLIPTEXT = _CLIPTEXT + CHR(13)+CHR(10) + TRANSFORM(luSomething)
*    NEXT
ENDIF
			ENDIF
			USE IN sqlTables
		ENDIF
		=SQLDISCONNECT(lnDataSrc)
	ENDIF
ENDIF
RETURN (lnResult > 0)
ENDFUNC

-Lou.