Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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.
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];
You need to use Ms office 12 OLEDB Driver for accdb
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; !
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...