Still error with UDC Excel ODBC after all information on the Internet

[mwanders]

Hi Qlik forum,

I'm struggling to connect an Excelsheet for user directory purposes. I read a lot where others facing problems but still it won't work for me.

My customer have QlikSense Enterprise November 2018. No Microsoft package on server. Windows 64 bit but I need a 32 bit ODBC.

What have I done?

1. Uninstall my 2016 version and Installed the 2010 version Microsoft Access Engine:

2. Created a SYSTEM DSN with the 2010 version

3. Created a test app in Hub and make connection. It worked and is see data

4. Created an Excel ODBC user directory and names of sheet are matching

5. Getting the famous message "Exception when trying to connect to the data source. Edit the connection string in the user directory connector configuration "

6. I checked the log and see also this "Setting up UDC'EXCEL' of type Repository.UserDirectoryConnectors.ODBC.OdbcExcel unsuccessful ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified↵↓Exception when trying to connect to the data source. Edit the connection string in the user directory connector configuration"

7. Ok, I understand that Qlik can not find the data source name and no driver is specified. So I tried this and other message appear "The specified DSN contains an architecture mismatch between the Driver and Application"

Recap: I have a working Excel ODBC connection because i see the data in my app. But if i want to connect to an UDC, Qlik tells me that no DSN of driver is found or a mismatch in string. If I check ODBC then I see the driver in SYSTEM.

Can anybody help me please. I need this for my customer because some persons doesn't work through the the also existing Active Directory connection and need Qlik.

ps: I'm not a connection string specialist so maybe here lies the problem. I looked through some articles and it seems not that hard to manage this. But still it won't work for me 😉 

Thank you in advance!

Martijn Wanders

[Mauritz_SA · Accepted solution]

Hi Martijn

I am by no means an expert, but we had to use the 64-bit ODBC Data Source for it to work. The rest looks the same as ours. Our connection string doesn't have the ; at the end, but don't think that's the problem. Our string is:

DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\BIProject\Qlik_User_Management.xlsm

Good luck.

Regards,

Mauritz

[mwanders]

Hi Mauritz,

Thank you for your fast reply and solution. I can connect my Excelsheet now. I was focussed on the 32bit because i readf this in multiple messages. Have a nice day!