Bug 43227 - FILEOPEN: UI to set workgroup (system DB) for opening MS Access (.mdb) files
Summary: FILEOPEN: UI to set workgroup (system DB) for opening MS Access (.mdb) files
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
Hardware: x86 (IA32) Windows (All)
: medium enhancement
Assignee: Not Assigned
Keywords: difficultyInteresting, easyHack, skillCpp, skillVcl, topicUI
Depends on:
Blocks: Database-MS_Access
  Show dependency treegraph
Reported: 2011-11-24 09:03 UTC by Lionel Elie Mamane
Modified: 2020-03-09 13:27 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2011-11-24 09:03:08 UTC
MS Access uses a file it calls "Workgroup" to define valid user accounts and passwords for its internal access control. The database engine underlying MS Access, called "Jet", that the "System DB".

In Access 2003, it can be set through the UI: menu tools / security / Workgroup manager, or with the "/workgroup" command-line switch. The story with Access 2007 is a bit more complicated, see http://support.microsoft.com/kb/918583 .

Our "connect to a Access file" wizard does not give users the possibility to set which workgroup shall be used, and uses the Jet default, which is not necessarily the same as the one MS Access uses. This leads to a frustrating "opening a .mdb file does not work" for the user, as the MS Access default has possibly been set by an Administrator and the user knows nothing about it.

Our "connect to Access file" UI should:

 1) Use the same workgroup as MS Access by default
 2) Allow it to be set differently
 3) Allow the user to set a Username and password. Default to "Admin" and no password.

Currently the only way to open a .mdb file with a specific workgroup is to choose "connect to ADO" instead of "connect to Access", and in the ADO "browse" UI, choose "Microsoft Jet 4.0 OLE DB Provider", go to the "All" and set property "Jet OLEDB:System database".

The one used by MS Access 2003 is in the registry at HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\Jet\4.0\Engines\SystemDB

Access 2007 is probably similar. There is no notion of workgroup for a .accdb file.

The file you need to change in LibreOffice is dbaccess/source/ui/dlg/ConnectionHelper.cxx, in function

   IMPL_LINK(OConnectionHelper, OnBrowseConnections, PushButton*, /*_pButton*/)

there is a big

        switch ( eType )

The case we are interested in is

  case  ::dbaccess::DST_MSACCESS:

There, you need to get no only a .mdb filename, but also the discussed workgroup file, which has extension .mdw.

You may also need to adapt ODbDataSourceAdministrationHelper::getConnectionURL() in file DbAdminImpl.cxx, as well as ODbTypeWizDialog::determineNextState in file dbwiz to activate a new state ADDITIONAL_PAGE_MSACCESS, which you have to create first.

Finally, the dialog itself is in file dbaccess/source/ui/dlg/dbadminsetup.src, section TabPage PAGE_DBWIZARD_MSACCESS.

You need to stick the path to the chosen workgroup (.mdw) file into the constructed ADO URL as ";Jet OLEDB:System database=THE_PATH". Have a look at sw/source/ui/dbui/dbmgr.cxx, function SwNewDBMgr::LoadAndRegisterDataSource. You may also need to adapt ODsnTypeCollection::extractHostNamePort in file dbaccess/source/core/misc/dsntypes.cxx so that it copes with (ignores) this extra bit.

Make sure you start from a code checkout of 24 november 2011 or later, as ADO is completely broken in older versions.
Comment 1 Björn Michaelsen 2013-10-04 18:47:55 UTC
adding LibreOffice developer list as CC to unresolved EasyHacks for better visibility.

see e.g. http://nabble.documentfoundation.org/minutes-of-ESC-call-td4076214.html for details
Comment 2 Alex Thurgood 2015-01-03 17:40:34 UTC Comment hidden (no-value)
Comment 3 Robinson Tryon (qubit) 2015-12-14 07:02:22 UTC Comment hidden (obsolete)
Comment 4 Robinson Tryon (qubit) 2016-02-18 14:51:55 UTC Comment hidden (obsolete)
Comment 5 Alex Thurgood 2017-10-20 14:19:45 UTC
@Lionel : is this now moot with the dropping of support by MS for access to 32bit mdb files on its 64bit systems ?
Comment 6 Xisco Faulí 2020-03-09 13:27:56 UTC
Please add keyword 'needsUXEval' and CC 'libreoffice-ux-advise@lists.freedesktop.org' if input from UX is needed.