CODEWITHSUNDEEP
foxpro
Amy
Amy

Reputation: 23

Foxpro failing to connect to ODBC, showing dialogue box

I am working on a Foxpro 9 program. I have a function to connect to Excel and bring back the column information. Running in Foxpro, this works as expected. However, when running a built executable, a Windows 'Select Data Source' dialogue box is displayed, offering File or Machine data source options. Choosing the right one doesn't work either, but in any case I don't want the dialogue.

This is running in 64bit Windows 8, but I have also tested in 32 bit. The program file with the Excel functions is built into the executable. Can anyone tell me why the built program is running differently?

RELEASE aCols
DIMENSION aCols(1)
AWorkSheetColumns(@aCols, m.tmpFile, m.tmpSheet, "DSN=Microsoft Excel Driver")

**********************************
FUNCTION AWorkSheetColumns(taArray, tcXLSFile, tcSheet, tnDSN)
**********************************
**********************************
* PARAMETER Information
* taArray := an array sent in by reference to fill with the specified worksheet's column information
* tcXLSFile := a string specifying an excel file (*.xls, *.xlsx, *.xlsm, *.xlsb) on disk
* tcSheet := a string specifying the worksheet or table to use when retrieving column information
*
* RETURN Information
* returns numeric, the number of columns found in the worksheet/table
**********************************
LOCAL lnSQL, laErr[1], lnResult, lnReturn, lcSQLAlias, loExc
m.lnReturn = 0
IF !EMPTY(m.tnDSN)
    m.lnSQL = SQLSTRINGCONNECT(m.tnDSN+ ";" ;
        +"DBQ="+FULLPATH(m.tcXLSFile)+";")
ELSE
    m.lnSQL = SQLSTRINGCONNECT("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
        +"DBQ="+FULLPATH(m.tcXLSFile)+";")
ENDIF
*!* Alternate using DSN that comes with Office install (MSDASQL = OLEDB wrapper for ODBC)
*!*     m.lnSQL = SQLSTRINGCONNECT("Provider=MSDASQL.1;" ;
*!*         +"Persist Security Info=False;" ;
*!*         +"DSN=Excel Files;" ;
*!*         +"DBQ="+FULLPATH(m.tcXLSFile)+";" ;
*!*         +"DriverId=790;" ;
*!*         +"MaxBufferSize=2048;" ;
*!*         +"PageTimeout=5;")

*!* Try a few other drivers that may be on the user's machine
IF m.lnSQL < 0
    IF UPPER(ALLTRIM(JUSTEXT(m.tcXLSFile))) == "XLS" && can we try using the older driver?
        m.lnSQL = SQLSTRINGCONNECT("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
            + "DBQ="+FULLPATH(m.tcXLSFile)+";")
        IF m.lnSQL < 0
            m.lnSQL = SQLSTRINGCONNECT("Driver={Microsoft Excel Driver (*.xls)};" ;
                + "DBQ="+FULLPATH(m.tcXLSFile)+";")
        ENDIF
    ELSE
        m.lnSQL = SQLSTRINGCONNECT("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
            + "DBQ="+FULLPATH(m.tcXLSFile)+";")
    ENDIF
    IF m.lnSQL < 0
        AERROR(m.laErr)
        ERROR m.laErr[2]
    ENDIF
ENDIF

m.lcSQLAlias = SYS(2015)
m.lnResult = SQLEXEC(m.lnSQL,[SELECT * FROM "] + m.tcSheet + [$" Where 1=0], m.lcSQLAlias)

IF m.lnSQL > 0
    SQLDISCONNECT(m.lnSQL)
ENDIF

IF m.lnResult < 0
    AERROR(m.laErr)
    ERROR m.laErr[2]
ENDIF

IF USED(m.lcSQLAlias)
    TRY
        m.lnReturn = AFIELDS(m.taArray, m.lcSQLAlias)
    CATCH TO m.loExc
        THROW
    FINALLY
        USE IN SELECT(m.lcSQLAlias)
    ENDTRY
ENDIF

RETURN m.lnReturn
ENDFUNC

Upvotes: 0

Views: 726

Answers (1)

Amy
Amy

Reputation: 23

Resolved this issue by setting 

SQLSETPROP(0, "DispLogin", 3)

before I connected.

Upvotes: 2

Related Questions