ODBC Connection string - Include password

[tamilarasu]

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.

[qlikviewwizard]

Hi TamilArasu,

Did you change the Open Mode and Try?

[tamilarasu]

Hi QW,

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

[qlikviewwizard]

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

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

[tamilarasu]

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.

[qlikviewwizard · Accepted solution]

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 ------

[JoaquinLazaro]

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

[tamilarasu]

Hi qlikviewwizard (Dont' know your real name),

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

[tamilarasu]

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.

[#10]

Liking the enthusiasm!