Hi Andrus,

You need to make the code you posted a bit less "brute-force-ish" if you want to do this.

Try to see if you can get the field names of the access table through code, and instead of having the program create a "select * from yadayada" statement, build the sql statement field by field and whenever you find a field that starts with a number, make your add ' as "x" + fieldname'

That way the select will create a cursor with field names that work within a dbf structure.

Alex


>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.ASP
>
>However, 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

Low-carb diet not working? Try the Low-food diet instead!