TestComplete x64 - ODBC Driver Connectivity Issue

[SaravanaKumar_N]

Hi,

I want to make a connection to Excel file and get the data, but I could not find a solution for TC x64 bit. So I am approaching you all.

I get the error message - " [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

TC - 12.41 64 bit

OS - Windows Server 2008 R2 64 bit

MS Excel - 32bit

I did follow the steps given here about installing - 64-bit version of Microsoft Office 12.0 Access Database Engine.

Also renamed the regedit "mso.dll" to "mso1.dll"

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/excel-storages.html

Connection string used ...

var ConnStr = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" + filePath + ";ReadOnly=1;";
adoConn.Connection = Sys.OleObject("ADODB.Connection");
adoConn.Connection.ConnectionString = ConnStr;
adoConn.Connection.Open();

If I use the TC - 12.41 32 bit version in the same box, I am able to connect and get the data from Excel file.

Not sure what I am missing here, any advice would be helpful for us.

Thanks

[tristaanogre]

If you're using an ODBC driver, you need to make sure that the ODBC driver you selected is for 64-bit.  The 64 bit access installation that you added is for ADO connections that use the JET database driver.

[SaravanaKumar_N]

@tristaanogre

Thanks for your reply. Is it about - Office System Driver: Data Connectivity Components

https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

Tried this as well, didn't help.

If not, would it be possible for you to share the driver download link please?

I can search for it, just want to be sure that I am not doing anything wrong.

[tristaanogre]

No, I don't think that's it.  That's the same data access components mentioned in the previous article.

In your OS, you should have ODBC data sources for both 64-bit and 32-bit.  You're using the one for 32-bit in the 64-bit TestComplete.  That's why you're getting the problem.

Note, however, that you don't HAVE to run your tests in 64-bit.  TestComplete works, functionally, exactly the same regardless of bitness.  If you can't find the proper ODBC data source to use, you should be fine running as 32-bit.

[SaravanaKumar_N]

Got you, its about the system ODBC Data Source Administrator.

Yes, I have added the Microsoft Excel Driver under both User DSN / System DSN - in both 32bit ODBC and 64bit ODBC.

We are able to work along with 32bit TC, just trying to resolve this outstanding issue as we got some free time 🙂 Thanks for your help @tristaanogre

[tristaanogre · Accepted solution]

You need to make sure, then, that in your connection string, you're referencing the 64-bit ODBC driver... I'm assuming it has a slightly different name.  Do that, and you can use the 64-bit TestComplete

[SaravanaKumar_N]

You are absolutely right. Have not noticed that till now, just updated the connection string 

from

"DRIVER={Microsoft Excel Driver (*.xls)};"

to

"DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" and it worked.

I have no idea how the previous one worked all these days when the actual name in the ODBC is

"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"

Even now the old previous connection string works for TC 32bit.

Anyway thanks a lot @tristaanogre

[tristaanogre]

Good to know that it's fixed!

One thing you might want to consider doing is a detection of whether or not you're running the 32-bit or 64-bit TC and, depending on that answer, set the connection string appropriately.

[SaravanaKumar_N]

@tristaanogre, "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" works for both 32/64 bit TC. That's what we have in both 32/64 ODBC.

Its just some miracle that "DRIVER={Microsoft Excel Driver (*.xls)};" works for 32-bit.