topic Re: ODBC Connection string - Include password in QlikView

ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 04:44:42 GMT

I am trying to access data from .accdb database file but the file is protected with password. I have used OLEDB connection to connect the database but the pop up window is asking to enter the password every time whenever I reload the data.

OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Tamilarasu.Nagaraj\Desktop\Test\PTV2.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="lptrack";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];

Can anyone explain the reason and solution please.

Thank you.

Re: ODBC Connection string - Include password

qlikviewwizard — Thu, 21 May 2015 04:49:02 GMT

Hi TamilArasu,

Did you change the Open Mode and Try?

Re: ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 04:56:09 GMT

Hi QW,

I tried it in open mode but still getting the pop up.

Re: ODBC Connection string - Include password

qlikviewwizard — Thu, 21 May 2015 05:12:52 GMT

Did you create ODBC connection? Hope this will help you.

Path: Control Panel\All Control Panel Items\Administrative Tools

Re: ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 05:16:20 GMT

Yes, I have tried but its too slow in reading the data. OLEDB connection string is fast compared to ODBC. Can you correct the OLEDB connection string please.

Re: ODBC Connection string - Include password

qlikviewwizard — Thu, 21 May 2015 05:52:33 GMT

Hi TamilArasu,

Try like this.

OLEDB , select Force 32 Bit

Click on Select

Select Microsoft OLEDB Provider for ODBC Drivers

In Connection Tab.

Leave Use Data Source Name

Select the "Use Connection String"

Click on Build

DSN Name will appears. Click on New to create new DSN name.

Select the Microsoft Access Driver(*mdb,*accdb)

Click Next accdb DB file.

Click Next

Click Finish

Click OK

Now select the Database Name from the left pane.

Click OK

Click OK Now DSN created.

In Connection Tab.

Give any username: UserTest password:UserTest For example

Select any table and Add.

It is working fine for me.

Please check the connection string sample.

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=UserTest;Extended Properties="DBQ=C:\Users\User123\Downloads\ASampleDatabase (1).accdb;DefaultDir=C:\Users\User123\Downloads;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\User123\Downloads\ASampleDatabase (1).accdb.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";Initial Catalog=C:\Users\User123\Downloads\ASampleDatabase (1).accdb];

//-------- Start Multiple Select Statements ------

SQL SELECT *

FROM `C:\Users\User123\Downloads\ASampleDatabase (1).accdb`.`Asset Items`;

//-------- End Multiple Select Statements ------

Re: ODBC Connection string - Include password

Joaquin_Lazaro — Thu, 21 May 2015 06:26:02 GMT

Hi Tamil:

Try this and close your access database before connect:

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=USER;Data Source=C:\YOURDB.mdb;Mode=Share Deny None;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];

Good luck

Joaquín

Re: ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 06:47:33 GMT

Hi qlikviewwizard (Dont' know your real name),

a lot. Working fine now. Have a nice day.

Re: ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 06:49:49 GMT

HI Joaqin,

When I use mdb file, your solution is working fine. For accdb format, I am getting the pop up windo to enter the password and provider string. Anyways, my issue has been solved. You too have a nice day.

Re: ODBC Connection string - Include password

Thu, 21 May 2015 06:55:05 GMT

Liking the enthusiasm!

Re: ODBC Connection string - Include password

Joaquin_Lazaro — Thu, 21 May 2015 07:03:19 GMT

Thank you, The same to you.

Read this is you still have the password pop up ... Connect to .accdb

Maybe you need some diferent OLEDB drivers, don't forget close access db before load and tick the open shared metod in the accdb properties!!!

Joaquín

Re: ODBC Connection string - Include password

qlikviewwizard — Thu, 21 May 2015 07:53:54 GMT

Thank you TamilArasu

Nanri. Same to you.

Re: ODBC Connection string - Include password

tamilarasu — Thu, 21 May 2015 08:06:42 GMT

Yes Joaquin

I can not find the Microsoft.ACE.OLEDB.12.0 provider driver in my system. I will check and download.