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

[mediaeval]

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.

 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?

 

[starl]

https://www.connectionstrings.com/microsoft-text-odbc-driver/

<code>Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=c:\txtFilesFolder\;
 Extensions=asc,csv,tab,txt;

</code>

 

[mediaeval]

When I use this code, with my own Dbq setting, it gives me the same error as before,

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

 

[starl]

Are you trying to make a single app for both 32 & 64? The connections are different.
Have you tried manually creating a connection in Excel, to ensure you have the correct drivers?

 

[mediaeval]

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:

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

Here's the 64-bit 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!

 

[mediaeval]

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!

 

[ParamRay]

Compiler directives:

#If Win64 Then
...
#End If

 

[ParamRay]

.