ADODB connection error when process is called from 64-bit Excel

  • I am testing a subroutine against both 32-bit Excel 2010 and 64-bit Excel 2013. I have a cn.Open statement as follows that works for 32-bit Excel 2010, but that does not work for 64-bit Excel 2013.


    Code
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & "Dbq=" & sFolder & ";" & "Extensions=asc,csv,tab,txt;"


    When called from 64-bit Excel, it returns the error, “[ODBC Driver Manager] Data source name not found and no default driver specified”


    I’ve googled this extensively and cannot find the correct code to use for 64-bit Excel 2013. Can anyone help?

  • Re: ADODB connection error when process is called from 64-bit Excel


    Use Microsoft Access Text Driver rather than just Microsoft Text Driver.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: ADODB connection error when process is called from 64-bit Excel


    When I make that change, it reports the same error message as before, namely


    Quote

    Run-time error '-2147467259 (80004005)':
    [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified

  • Re: ADODB connection error when process is called from 64-bit Excel


    What options do you see listed when you try to create an external data text query on the 64 bit machine?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: ADODB connection error when process is called from 64-bit Excel


    I don't understand what you mean...? Do you mean the Itelllisense after the Connection, "cn."? I've been doing everything in code. Also, both machines are 64-bit machines, it's the versions of Excel that are 32-bit and 64-bit.

  • Re: ADODB connection error when process is called from 64-bit Excel


    Hi Rory


    In the 64-bit Excel 2013, I click on the Data tab on the ribbon, and in the 'Get External Data' group is a 'From Text' option. Is this what you mean...? Clicking on this brings up the 'Import Text File' dialog, and when i choose a file, it brings me into the old-style Text Import Wizard that has (I think) been in every version of Exel since Excel 98, or perhaps earlier.

  • Re: ADODB connection error when process is called from 64-bit Excel


    Apologies - due to a small mishap, I have lost access to my only system with 64bit Office on it. I will try to recreate a new one either today or tomorrow and do some testing.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: ADODB connection error when process is called from 64-bit Excel


    I've disovered the reason why i've been getting an error with the 64-bit code, and it's very subtle. Here's the 32-bit code:


    Code
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & "Dbq=" & sFolder & ";" & "Extensions=asc,csv,tab,txt;"


    Here's the 64-bit code:

    Code
    cn.Open "Driver=Microsoft Access Text Driver (*.txt, *.csv); & "Dbq=" sFolder & ";" & "Extensions=asc,csv,tab,txt;"


    Aside from the word Access in the 64-bit version, there is one other difference...can you spot it?


    OK...it is that, in the 64-bit version, the text in the brackets, *.txt and *.csv are now separated by a comma, and not by a semi-colon, as in the 32-bit version. Agggghhhhhhh!

  • Re: ADODB connection error when process is called from 64-bit Excel


    One other question - is it possible to determine in code whether the calling Excel is 32-bit or 64-bit, so I could place both those versions of the code in there? Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!