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

Answers

  • 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")

    • Marked as answer by BenNairobi Thursday, May 04, 2017 1:31 PM
    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

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")

    • Marked as answer by BenNairobi Thursday, May 04, 2017 1:31 PM
    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
  • Hi BenNairobi,

    Do you check and revert, Do you resolve the issue? if so, could you please mark the helpful reply as answer, it will be beneficial to other communities who have the similar issue.

    If not, please feel free let us know.

    Best regards,

    Cole Wu


    MSDN Community Support&lt;br/&gt; Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &lt;a href=&quot;mailto:MSDNFSF@microsoft.com&quot;&gt;MSDNFSF@microsoft.com&lt;/a&gt;.

    Thursday, May 04, 2017 7:43 AM
  • The whole idea with the connection is initially very weak. With Excel you can work in Visual FoxPro much easier and safer using OLE Automation. Approximately so it is possible:
    ObjMSExcel = getobject ("", "Excel.Application")
    If vartype (objMSExcel) # "O"
        ObjMSExcel = newobject ("Excel.Application")
    Endif
    And then you can use the object code, only slightly changing it in accordance with the FoxPro syntax.
    I recommend leaving this ODBC and learning to work with OLE Automation.

    In this case, you will no longer be bothered by problems with bit depth and the rest. The main thing is that the used properties and methods of the object Excel. Attachment are present in the versions of MS Office installed on your computers.

    Friday, May 12, 2017 10:23 AM
  • Hi fox-dgv,

    Thanks for your response.

    Initially, that was my approach. However, when dealing with many excel sheets there was, seemingly, speed issue and hanging. Due to this I stopped.

    Benson


    Ben

    Friday, May 12, 2017 11:41 AM