ODBC for MS Access / Excel 2016

[#1]

I have been using Qlik Sense desktop for a few months without any problems.

Due to being forced to upgrade to version 3 I am now having problems accessing my Access databases and excel files. The connections do not load in the data load editor and when viewing the connection no tables are shown.

I have decided to reformat my HDD and reinstall Windows 10, Office 2016 64bit and Qlik Sense Desktop 3.1

What do I need to install so I can set up the ODBC connections? I don;t want to have to do this again if i install the wrong data connectors.

Thanks for your assistance

[rohitk1609]

Hi Simon,

As you stated , Your Application was working fine before upgrading to 3.1, Its very strange . have your check your ODBC connection working fine by going to administrator tools => System DSN => Test Connection. If yes m then choose the same name connection here , if it is allowing you to access the database , Then can you provide in detail what error you are facing or what exactly your issue is.

Please Use OLEDB connection rather than ODBC for MS Access .

I saw your did a lot of work for this . Please try OLEDB connection and let me know if you still face any issue ?

[#3]

Hi Rohit, sorry for the late reply - i've been waiting for databases etc to update on dropbox.

I can not find where I can test the ODBC connections, where is Administrator tools? (Win 10)

As i said I have reformatted my HDD and reinstalled Win 10, Office 2016 365 and MS Access Database Engine 2010 (all 64bit).

Still will not work with Qlik Sense Desktop v3 (3.1) !

When I go to select the data to load under Data Connections I just get the screen below.

The following error occurred:

SQL##f - SqlState: S1000, ErrorCode: 4294966252, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name.

The error occurred here:

LIB CONNECT TO 'MasterDBinQlikfolder'

Data has not been loaded. Please correct the error and try loading again.

When I click on 'Load Data' I am getting this error: I created a new app and tried to connect it to another database with an ODBC connection but got the same blank screen as above.

Sorry for sounding like an amateur - what provider do I need to select in the create new OLE DB window to connect to a MS Access accdb database and can I also use this to connect to an excel spreadsheet?

Many thanks for your assistance, I'm desperate to get this working again.

[rohitk1609 · Accepted solution]

Hi Simon,

See I am sure you have installed the Driver for MSAccess with lets say name MYConnection

I am attaching a snapshot where you can see how I go to the administrator tools => ODBC Database Administrator 64 bit => System DSN => MYConnection and then select it and done . You will find a option test connection. if it say you OK it means your machine has connection  with the database.

We Create System DSN because it will be access by system not by any particular user.

then go to Qlik Sense and go to OLEDB and you will find the name MYConnection select it and you will have your database tables.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Rohit Kumar

[#5]

Hi Rohit,

Many thanks for your assistance. I have managed to connect using OLE DB and app is working fine.

I still could not find the test connection option in the ODBC Database Adminstrator - it doesn't matter now as the app works fine but just curious how i can test from Administrator tools.

Thanks again for your assistance.

[rohitk1609]

Hi Simon,

For the time being I don't have any database , but when you create any connection in administrator tools on the same time you gets the option of test connection. I will show you someday.

Many thanks