I have a table in MS Access MDB file and want to convert it into
VFP table.
I use samle code from
http://support.microsoft.com/support/kb/articles/q260/4/05.ASPHowever, filed name in access table starts with a digit.
This causes this MS sample program to fail: accessing such field is
not possible. I want to add an underscore character to start of suht fields.
How this can be done ?
LOCAL lcMyFile, lnConnHandle, lnResult, lcSQLCommand, lnGetData, lcNewName
LOCAL ARRAY laWhatErr(1)
lcMyFile = GETFILE('MDB')
IF EMPTY(lcMyFile)
return
endif
lnConnHandle = ;
SQLSTRINGCONNECT('DRIVER=MICROSOFT ACCESS DRIVER (*.MDB);DBQ=' + ;
lcMyFile+'')
CREATE DATABASE accessmdb
IF lnConnHandle > 0
*!* Need to know what tables are contained in the MDB file
lnResult = SQLTABLES(lnConnHandle, 'TABLE')
IF lnResult > 0
*!* Create a new DBC named "MYNEW"
CREATE DATABASE mynew
*!* Select SQLResult and scan through the cursor.
SELECT SQLResult
SCAN
? recno(),recc(),ALLTRIM(SQLResult.table_name)
*!* Build a SQL SELECT statement to pass to SQLEXEC()
lcSQLCommand = ;
"SELECT * FROM [" + ALLTRIM(SQLResult.table_name) + "]"
lnGetData = SQLEXEC(lnConnHandle, lcSQLCommand, 'newdata')
IF lnGetData > 0
*!* SQLEXEC() was successful
lcNewName = chrt(STRTRAN(ALLTRIM(SQLResult.table_name), " ", "_"),'-','_')
SELECT newdata
*!* Copy Table Structure and Data to a DBF file
*!* VFP 3.0 - loses long field names
*!* COPY TO (lcNewName)
*!* ADD TABLE (lcNewName)
*!* VFP 5.0/6.0 - keeps long field names
COPY TO (lcNewName) DATABASE myNew
* !!!! bug: if a field name starts with a digit, dbf file is not created !!!!
ENDIF
ENDSCAN
ENDIF
*!* Done Getting Data, so Disconnect
=SQLDISCONNECT(lnConnHandle)
ENDIF
Andrus