Forum Discussion
Connecting to an encrypted .accdb file
Windows 10 v21H1, Access v2107, Delphi 10.4
Hello everyone,
I am trying to build a UI to an encrypted access .accdb database in Delphi 10.4, so need to connect to it programmatically. I understand from numerous internet postings that this is not possible unless legacy encryption is selected but, even then, I do not seem to be able to find the appropriate connection string. I am using the OLEDB 16.0 provider.
Any help greatly appreciated.
I seem to have stumbled on a solution! Building the connection string to my legacy-encrypted DB in Delphi gives me the following, some of which is probably redundant (placeholders for other users to populate denoted by <text>):
Provider=Microsoft.ACE.OLEDB.16.0;User ID=Admin;Data Source=<full path to DB file>;Mode=Share Deny None;Jet OLEDB:System database=<full path to System.mdw file>;Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=<password - no quote marks>;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=<as before>;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=True;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;
The secret seems to be that you must either have the login prompt facility turned off and connect automatically or, if it is on, leave both username and password blank when connecting.
I hope this helps someone. It's been haunting me for weeks!
Check out this link http://www.sqlstrings.com/MS-Access-connection-strings.htm
- Thank you but these all relate to OLEDB 4.0. Having tried them previously I am pretty sure that they don't work with encrypted .accdb files but I'll give them another go.
Perhaps this more extensive link will help.
Tis is the best site for most types of connection string
It includes code for OLEDB 12.0 connection to encrypted databases.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database Password=MyDbPassword;Perhaps you can use/adapt that as that site also seems to have nothing for OLEDB 16.0.
- Good to know I'm not alone, lorangray! Quite a few internet posts out there from people who claim to have succeeded but nothing recent and nothing that works for me.
I seem to have stumbled on a solution! Building the connection string to my legacy-encrypted DB in Delphi gives me the following, some of which is probably redundant (placeholders for other users to populate denoted by <text>):
Provider=Microsoft.ACE.OLEDB.16.0;User ID=Admin;Data Source=<full path to DB file>;Mode=Share Deny None;Jet OLEDB:System database=<full path to System.mdw file>;Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=<password - no quote marks>;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=<as before>;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=True;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;
The secret seems to be that you must either have the login prompt facility turned off and connect automatically or, if it is on, leave both username and password blank when connecting.
I hope this helps someone. It's been haunting me for weeks!
