none
VFP9 Connection to Excel on 64 bit system

    Question

  • Dear all,

    This is my connection string:

    nResult = SQLStringConnect("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DriverId=790; DBQ=" + excel_file + ";")

    It runs well on a 32 bit computer but fails on a 64 bit computer. Both computers have Office 2010 32 bit. What am I missing here?

    <g class="gr_ gr_98 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="98" id="98">Thanks</g> all.

    Benson

    Friday, April 21, 2017 9:05 AM

All replies

  • Looking for DriverID (an option of a connection string I didn't know yet), DriverId 790 only addresses Excel97-2003, as it seems (see explanation https://docs.microsoft.com/en-us/sql/odbc/microsoft/odbc-jet-sqlconfigdatasource-excel-driver)

    So where did you get that connection string from? It doesn't seem to connect to Excel 2010 and on your 32bit computer, you likely connect to an Excel 2003 driver.

    Notice drivers of previous excel versions can be installed without the corresponding Excel version installed.

    Try just removing that DriverID part, referring to connectionstrings.com that will work for Excel2007 and 2010.

    Bye, Olaf.


    Olaf Doschke - Freelancer

    Friday, April 21, 2017 2:03 PM
  • Olaf,

    Sorry. Kindly ignore the DriverID clause. I was just playing around with the string before I pasted it here. 

    It works without the DriverID but only on the 32 bit computer with Excel 2010. It refuses to work on 64 bit with Excel 2010.

    Thanks

    Benson

    Friday, April 21, 2017 2:27 PM
  • Check two points:

    1) Look what 32 bit drivers and data sources are available on your PC in C:\Windows\SYSWoW64\ODBCAD32.exe

    2) Try to connect from VFP Command Window by

    ? SQLSTRINGCONNECT()

    It will offer all available 32 bit data sources and you may select one. If it is Excel then it should ask for XLSX file.

    If you don't have 32 driver available download appropriate MS Access Data Engine.

    After you connect successfully you may look at the used connection string by issuing:

    ? SQLGETPROP(<<YourConnHandle>> , "ConnectString")

    Saturday, April 22, 2017 11:26 AM
    Moderator
  • I agree with Pavel.

    Also, did you check the error message feedback from your failing connections?

    After SQLStringConnect like any other SQL Passthrough function, you can find out more on an error via AERROR(laERROR) and inspecting the laERROR array createdfrom it (eg in debugger locals window). And result being no valid handle is showing there was an error.

    Most likely the error message is you don't have that driver installed, as VFP only uses 32bit drivers.

    Bye, Olaf.


    Olaf Doschke - Freelancer

    Saturday, April 22, 2017 3:05 PM
  • Thanks Pavel,

    I will check and revert.

    Monday, April 24, 2017 4:07 AM
  • Thanks Olaf,

    I will check and revert. I have been away from computers.

    Benson

    Monday, April 24, 2017 4:08 AM