Access Database connection failed

[Anonymous]

Hi,

          I couldn't bring my access data to my Qlikview. I ma using Qlikview 12 Personal Edition 64-Bit. I selected "Force 32-Bit" and tried to connect to "Access Database". But still I couldn't connect. I  need help on this and I couldn't find any solution in Qlikview community also. Attached screen shot and also access database for your reference

[Anonymous]

I would be Happy if I could get a solution for the above problem. I guess this will help many people in Qlikview community. Thanks in advance.

[ziadm · Accepted solution]

Hi

I have the connection string works on the file provided.. Please check if its OLEDB driver problem

This is using Microsoft.ACE.OLEDB.12.0

Try Connection string (need to change the file path )

*************************************************************

OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\zmohammad_c\Downloads\Example1.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False];

[ziadm]

You need to use Ms office 12 OLEDB Driver for accdb

[Anonymous]

Hi Ziad,

          Thanks for your Quick Help. It is working fine and also I am giving some step by step process on how to connect ot Access Database as this would help many people. I was mad on this problem for past 3 days. I have attached the working Access Database & Qlikview File.

To connect to Access Database 2002 format (Example1.mdb)

Step 1: Select "OLE DB" in the Database tab

step 2: "Check "force 32 Bit"

step 3: Click "Connect"

step 4: In the "Data Link Properties", Select "Microsoft Jet 4.0 OLE DB Provider"

step 5: Click "Next"

step 6: You will be directed to "Connection tab"

step 7: Select the Database in 2002 format (.mdb format)

step 8: Go to "Advanced tab", uncheck "Share Deny None" & Check "ReadWrite"

step 9: Go to "Connection tab" and click "Test Connection"

step 10: Will get message box like "Test Connection succeeded"

step 11: Click "ok"

String generated is below

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=E:\Qlikview\Qlikview Working Files\Example1.mdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

step 12: Now click "select" and choose the appropriate table

SQL SELECT *

FROM Example1;

To connect to Access Database 2010 format (Example1.accdb)

Step 1: Select "OLE DB" in the Database tab

step 2: "Check "force 32 Bit"

step 3: Click "Connect"

step 4: In the "Data Link Properties", Select "Microsoft Office 12.0 Access Database Engine OLEDB Provider"

step 5: Click "Next"

step 6: You will be directed to "Connection tab"

step 7: Copy and paste the full path of the Access Database ("E:\Qlikview\Qlikview Working Files \Example1.accdb")

step 8: Go to "Advanced tab", uncheck "Share Deny None" & Check "ReadWrite"

step 9: Go to "Connection tab" and click "Test Connection"

step 10: Will get message box like "Test Connection succeeded"

step 11: Click "ok"

String generated is below

OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\Qlikview\Qlikview Working Files\Example1.accdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False];

step 12: Now click "select" and choose the appropriate table

SQL SELECT *

FROM Example1; !

[Anonymous]

Correct Zaid, Thanks for your Quick replay and also I added the step by step process on how to connect with both the format access database. Thanks for your help and no words to appreciate it. U r answer is correct and that helps me a lot to take the script future...