Bug 70236 - FILEOPEN: Cannot connect to .accdb file in LibreOffice 4.1
Summary: FILEOPEN: Cannot connect to .accdb file in LibreOffice 4.1
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA target:4.5.0 target:4.3.7 target:...
Keywords: regression
: 69832 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-10-07 15:31 UTC by Ben Trewern
Modified: 2015-02-05 09:49 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
te be tested patch (948 bytes, patch)
2015-01-26 04:25 UTC, Lionel Elie Mamane
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ben Trewern 2013-10-07 15:31:10 UTC
Problem description: 

Steps to reproduce:
1. Open LibreOffice
2. Click on "Database"
3. Select "Connect to existing database" and select "Microsoft Access 2007" in the drop down box.
4. Click Next.
5. Select "Browse"

Current behavior:
There is no option to open an .accdb file in the list of file types.

Expected behavior:
There should be an option to open .accdb files in the list of file types.  This used to work in 4.0.5 and before.

This is still true in the latest 4.1.2.3 release.  Tested on Windows 8 and Windows 7 Home Premium.
Operating System: Windows 8
Version: 4.1.0.4 release
Comment 1 Urmas 2013-10-08 17:44:06 UTC
Confirming.
Comment 2 Ben Trewern 2013-11-04 13:00:41 UTC
After some more investigation you can open an .accdb file in multiple ways i.e. using an ADO or ODBC connection.  This I suppose can be used as a workaround but I am always dubious having another layer between the database and the front end.

Even opening old .odb files with LO 4.1.x does not allow connection to the .accdb file.  When I try I get:

"The connection to the data source "xxxxxxx" could not be established.

SQL Status: 3343
Error code: -2147467259

Unrecognized database format 'xxxx.accdb'."

This was tested with LO 4.1.3.2
Comment 3 Ben Trewern 2014-02-28 10:50:51 UTC
Just tested again in LO 4.2.1.1 and still only an option to open .mdb files.  This currently works correctly in OO 4.0.1 so sadly I've had to change over.
Comment 4 Buovjaga 2014-11-07 14:09:17 UTC
*** Bug 69832 has been marked as a duplicate of this bug. ***
Comment 5 Alex Thurgood 2015-01-03 17:41:29 UTC
Adding self to CC if not already on
Comment 6 Julien Nabet 2015-01-25 10:18:30 UTC
Ben: any update with last LO version (4.3.5)?

Jean-Pierre: I thought you might be interested in this one since you work on Access2Base
Comment 7 Jean-Pierre Ledure 2015-01-25 12:08:44 UTC
@Julien : I have .accdb databases at my disposal for testing purposes in several contexts, but I'm afraid I'm not the right person to dive in the code to see what is wrong.
Comment 8 Julien Nabet 2015-01-25 22:11:49 UTC
No problem, thank you Jean-Pierre for your feedback.

Lionel: I'm wondering if the problem could be in http://opengrok.libreoffice.org/xref/core/connectivity/registry/ado/org/openoffice/Office/DataAccess/Drivers.xcu#244

Shouldn't there be "accdb" in "value"? (see below)
    244     <node oor:name="sdbc:ado:access:Provider=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=*" oor:op="replace">
    245       <prop oor:name="ParentURLPattern">
    246         <value>sdbc:ado:access:PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=*</value>
    247       </prop>
    248       <prop oor:name="DriverTypeDisplayName" oor:type="xs:string">
    249         <value xml:lang="en-US">Microsoft Access 2007</value>
    250       </prop>
    251       <node oor:name="MetaData">
    252         <node oor:name="Extension" oor:op="replace">
    253           <prop oor:name="Value" oor:type="xs:string">
    254             <value></value>  <<<<<< HERE
    255           </prop>
    256         </node>
    257       </node>
    258     </node>
as there's "mdb" value line 234

(I can't test this guess since it's Windows only)
Comment 9 Lionel Elie Mamane 2015-01-26 03:12:02 UTC
(In reply to Julien Nabet from comment #8)
> No problem, thank you Jean-Pierre for your feedback.
> 
> Lionel: I'm wondering if the problem could be in
> http://opengrok.libreoffice.org/xref/core/connectivity/registry/ado/org/
> openoffice/Office/DataAccess/Drivers.xcu#244
> 
> Shouldn't there be "accdb" in "value"?
> as there's "mdb" value line 234

This makes sense, and is a good guess for the part of the problem "does not offer .accdb files in the file list", but OTOH it was already like that in 4.0.5.2, and I don't see how it could cause the part "opening an existing .odb file fails to connect".

I looked at the diff of ADO driver between 4.0.5.2 and 4.1.2.3, didn't see anything obviously wrong.

We don't have bibisect for Windows by any chance?

Could someone attach a .odb file that connects to a .accdb file, so that I can look what it looks like? I'm particularly interested in the SDBC url.
Comment 10 Lionel Elie Mamane 2015-01-26 03:22:57 UTC
(In reply to Ben Trewern from comment #2)
> After some more investigation you can open an .accdb file in multiple ways
> i.e. using an ADO or ODBC connection. This I suppose can be used as a
> workaround but I am always dubious having another layer between the database
> and the front end.

Well, the "Connect to an existing database" / "Microsoft Access 2007" way actually uses ADO under the hood, so if using ADO directly works, you are not actually adding any additional layer.

Can you positively confirm that "Connect to an existing database" / "ADO" works? Could you attach a .accdb file setup in this way that works? (You might have to make a few changes in the advanced database settings).

Also, can you still connect to .mdb files successfully?

The "Connect to an existing database" / "Microsoft Access 2007" procedure actually is "just" some automatic mapping of "oh, you want to connect to a Microsoft Access 2007 file? So, then let's automatically use this ADO driver with this configuration and these parameters. My guess is that this mapping is broken.

> Even opening old .odb files with LO 4.1.x does not allow connection to the
> .accdb file.  When I try I get:

> "The connection to the data source "xxxxxxx" could not be established.
 
> SQL Status: 3343
> Error code: -2147467259
 
> Unrecognized database format 'xxxx.accdb'."

This looks like an error from the ADO driver. Maybe we are using the wrong ADO driver, like e.g. using the one for .mdb files instead of the one for .accdb files?
Comment 11 Lionel Elie Mamane 2015-01-26 04:25:44 UTC
Created attachment 112801 [details]
te be tested patch

Try this patch. It could fix the "does not show .accdb files in the filelist" problem. I'm not too hopeful about the "connection fails" problem, but it is worth to be tested.
Comment 12 Lionel Elie Mamane 2015-01-26 04:32:19 UTC
Oh, well... I just accidentally pushed the patch to master... So just test a sufficiently new master (4.5.0.alpha) build.

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4735ad02167576036c9f3c9dffb3ccbd0a884db7
Comment 13 Lionel Elie Mamane 2015-01-27 17:17:28 UTC
My testing with 4.2.3.3 shows:

1) Connecting to (using) existing .odb files that connect to .accdb file
   still works.

2) Trying to create a new one indeed proposes .mdb files instead of
   accdb files.


My testing with 4.5.0.alpha0+ (Build ID: 19669b2c2d77ddf5d7a07a655ca21d0557e7d603)

1) Connecting to (using) existing .odb files that connect to .accdb file
   still works.

2) Trying to create a new one proposes .accdb files.


So I'm going to consider:

Problem 1 "cannot use existing .odb file" as WORKSFORME.

Problem 2 "cannot (easily) create new .odb file connecting to .accdb" as FIXED.

If anybody has problem 1 in any version (from a currently maintained branch), please open A NEW CLONE of this bug.

If anybody has problem 2 in ay version having the patch in (right now 4.5.0.alpha0+ of 27 January 2015 or later, hopefully soon 4.4.1 and 4.3.7), please reopen.
Comment 14 Lionel Elie Mamane 2015-01-27 17:23:57 UTC
(In reply to Lionel Elie Mamane from comment #13)

> Problem 1 "cannot use existing .odb file" as WORKSFORME.

> If anybody has problem 1 in any version (from a currently maintained
> branch), please open A NEW CLONE of this bug.

Before that, please make sure you either have Microsoft Access installed, or the (free-as-in-free-beer) Microsoft Access Database Engine 2010 Redistributable. The latter can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=13255.
Comment 15 Commit Notification 2015-01-27 19:59:21 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ce7017cdbe5af3f75cded806f69c1040857b557f&h=libreoffice-4-3

tdf#70236 propertly recognise full Access 2007 URL

It will be available in 4.3.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2015-01-27 20:00:54 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=73a1dd0ab850e089260e2c9af63f1998144bbebf&h=libreoffice-4-4

tdf#70236 propertly recognise full Access 2007 URL

It will be available in 4.4.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2015-01-28 11:33:03 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-3-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1daff1b1e7e5b3c8563e07a8bd0b77b7a3a90f50&h=libreoffice-4-3-6

tdf#70236 propertly recognise full Access 2007 URL

It will be available in 4.3.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 18 Marina Latini (SUSE) 2015-02-05 09:49:56 UTC
Verified on: 
* Version: 4.5.0.0.alpha0+
* Build ID: 1845b6af3991ca5521eef48aafe1d0489e2ff8f6
* TinderBox: Win-x86@42, Branch:master, Time: 2015-02-02_09:30:48
* Locale: it_IT
* OS: Win 7 Home Premium SP1 x86