Connecting to an encrypted .accdb file

[Dicky_g141]

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.

[#2]

@Dicky_g141 

Check out this link http://www.sqlstrings.com/MS-Access-connection-strings.htm 

[#3]

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.

[#4]

Perhaps this more extensive link will help.

MS Access Connection Strings - ADO SQL Database Connection Strings - SQL Server, MS Access, MySQL, O...

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.

[#5]

Thanks again but I have already tried this in various permutations. I always get the error message:
"Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

[#6]

Hmm. Not sure I understand that.
Workgroup information files (MDW) were part of user level security (ULS) which was a feature deprecated with the advent of the .ACCDB file format in A2007.
Is this an old .MDB file with ULS that you converted to .ACCDB? If so, perhaps it still contains references to the MDW file even though it no longer works. Have you tried importing all objects into a new blank database or decompiling to remove all existing compilation code?

[#7]

I am also facing the same problem...

[#8]

As I mentioned in my original post, there is lots of evidence on the internet that it is not possible to connect to a .accdb database with default encryption so I had to set up my brand-new test dB with legacy encryption. This may be what causes the reference to the .mdw file which does exist on my machine in c:\users\myname\AppData\roaming\Microsoft\Access. I have tried referencing this in the connection string and also adding user=Admin (which appears in the .mdw) but none of my efforts have been successful.

[#9]

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.

[#10 · Accepted solution]

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!

[#11]

Congratulations. As you say, hopefully someone will find that useful