Bug Hunting Session
Bug 36555 - Cannot View Tables of Connected Access Database files
Summary: Cannot View Tables of Connected Access Database files
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.4.4 release
Hardware: x86 (IA32) Windows (All)
: high blocker
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:3.4.5 target:3.5.0
Keywords: regression
: 38765 (view as bug list)
Depends on:
Blocks: mab3.4 43298
  Show dependency treegraph
 
Reported: 2011-04-24 17:12 UTC by Enio
Modified: 2015-04-30 17:49 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Enio 2011-04-24 17:12:05 UTC
The connection to the data source "db1" could not be established.

SQL Status: HY000

The connection to the external data source could not be established. No SDBC driver was found for the given URL.

A connection for the following URL was requested "sdbc:ado:access:PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=X:\Users\Enio\Documents\Library.mdb".
Comment 1 pierre-yves samyn 2011-06-28 10:55:14 UTC
Hello

I confirm the problem to open existing database.

I would add that the problem is the same for creating a new database...

Build Date & Platform: XP - LibO 3.4.1RC1

Steps to Reproduce:

1. File> New> Database
2. Select Connect to an existing database
3. Select Microsoft Access in the listbox
4. Click Next
5. Type the access file pathname (or use the Browse Button) 
6. Click Next
7. Do you want to register, select No (not relevant here)
8. Check "Open the database for editing (keep default value)
9. Click Finish and give the name for the .odb file
10. LibO opens the odb

Error message : 

The connection to the data source "xxx" could not be established. 
The connection to the external data source could not be established. No SDBC driver was found for the given URL.

Best regards
Comment 2 Jean-Michel COSTE 2011-06-29 02:17:11 UTC
No connection to data source for a mdb file (Access).
Comment 3 pierre-yves samyn 2011-07-01 02:27:51 UTC
(In reply to comment #1)
> I confirm the problem to open existing database.
> I would add that the problem is the same for creating a new database
> Build Date & Platform: XP - LibO 3.4.1RC1...

Same problems with XP & LibO 3.4.1RC3 buildid=340m1(Build:103)
Comment 4 m.a.riosv 2011-07-18 16:16:28 UTC
I also can confirm with:
LibreOffice 3.4.1.RC2 (OOo340m1) (build:201), Win7x64 Ultimate
"
The connection to the data source "DatosNomina" could not be established.
The connection to the external data source could not be established.
No SDBC driver was found for the given URL.
"
Works nice in LibreOffice 3.3.3.1 (tag)
Comment 5 m.a.riosv 2011-07-18 16:26:15 UTC
I forgot:
with Microsoft Access Database
Comment 6 Rainer Bielefeld Retired 2011-07-22 10:14:42 UTC
Regression due to Comment 5
Comment 7 Michael Meeks 2011-08-25 09:35:02 UTC
> 3. Select Microsoft Access in the listbox

Are we really shipping with the MS Access connector ? I don't have that in my listbox, nor is the patch that we had there applied - where did you get your 3.4.1 from ?
Comment 8 m.a.riosv 2011-08-27 07:39:21 UTC
(In reply to comment #7)
> > 3. Select Microsoft Access in the listbox
> 
> Are we really shipping with the MS Access connector ? I don't have that in my
> listbox, nor is the patch that we had there applied - where did you get your
> 3.4.1 from ?

Yes I can get in the listbox in LibreOffice 3.4.4.
But the problem remain.
Comment 9 m.a.riosv 2011-08-27 07:42:24 UTC
*** Bug 38765 has been marked as a duplicate of this bug. ***
Comment 10 Winfried Donkers 2011-10-06 03:11:02 UTC
I confirm the problem with version 3.4.3 (OOO340m1, build 302)

When trying to open a base document which links to a Microsoft Access database and when creating a new Base document which links to a (any) Microsoft Access database, I get the error message (translation from Dutch to English by me):
***
The connection with the external datasource cannot be established because no SDBC-driver for the given URL is found.
***

with the extra information:
***
A connection for the following URL was asked "sdbc:ado:access:PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=path_plus_filename.mdb"." 
***
Comment 11 Alex Thurgood 2011-10-06 03:26:05 UTC
(In reply to comment #7)
> > 3. Select Microsoft Access in the listbox
> 
> Are we really shipping with the MS Access connector ? I don't have that in my
> listbox, nor is the patch that we had there applied - where did you get your
> 3.4.1 from ?

Hi Michael,

Possibly linked to the removal/deactivation by François Tigeot of the Adabas compilation. I think that the ADO stuff was also deactivated at the same time by mistake, but from what I remember, this was later corrected/reverted.


We need someone to try this with a build from master on Windows, or else 3.4.3 (if the changes from master were back ported ??)


Alex
Comment 12 Alex Thurgood 2011-10-06 03:26:53 UTC
All :

Please test on 3.4.3, and additionally with a recent build for Windows from master (if one is available).


Alex
Comment 13 xb2 2011-10-11 08:22:03 UTC
I confirm the bug persist on LO 3.4.3 on a Windows system
Comment 14 Cor Nouws 2011-10-12 05:52:35 UTC
.
Comment 15 Thomas Thym 2011-10-20 01:11:55 UTC
I can confirm that bug on LibreOffice 3.4.3 OOO340m1 (Build:302), Windows XP.
We are migrating in our company from MSO to LO. We don't have many Access databases but from time to time we get some files from external partners. For us it is important that we could open those files.
Comment 16 m.a.riosv 2011-10-31 19:12:34 UTC
The bug remains in LibreOffice 3.4.4rc1
Comment 17 Jean-Michel COSTE 2011-11-02 10:04:09 UTC
And probably remains in 3.4.5, 3.4.6,....3.5.0.....?
Comment 18 Cor Nouws 2011-11-02 12:54:33 UTC
why is the NEEDINFO keyword here?
Comment 19 Alex Thurgood 2011-11-03 04:20:29 UTC
(In reply to comment #18)
> why is the NEEDINFO keyword here?

Good question. I'll remove it, the bug is confirmed up to at least 3.4.3.

Alex
Comment 20 Roosevelt Coopling 2011-11-07 11:16:07 UTC
Hello, 


I had the same problem until I realized that the LibreOffice Base was not installed.

After I installed it throught Ubuntu, everything came back to normal: reading database files from any source and keeping my mail merge up to date.

TIP: while using the mail merge in the Writer, press F4 key to have access to the merge fields using drag & drop.


Cheers



Roosevelt Coopling
www.coopling.com.br
Comment 21 Jean-Michel COSTE 2011-11-07 11:31:40 UTC
NO, NO, NO, NO  !
It does'nt work with Access DATABASE...
Nobody to fix it ?
Comment 22 Rainer Bielefeld Retired 2011-11-07 13:45:44 UTC
<http://wiki.documentfoundation.org/BugReport_Details#Version>
Comment 23 Alex Thurgood 2011-11-10 02:23:52 UTC
(In reply to comment #22)
> <http://wiki.documentfoundation.org/BugReport_Details#Version>

Hi Rainer,

What do you mean by this link ? The earliest version mentioned in the bug report is 3.4.0RC1. It has been present ever since, just that nobody was bothered about fixing Base bugs when 3.4.0 was released.

Alex
Comment 24 omicron 2011-11-16 03:11:33 UTC
Hi guys!!




Hi guys!!

We are also hitting the same problem with LibreOffice Base (3.4.4) while using it at 150 PC with Windows XP/Vista/7. We also have 60 PC with Debian/Ubuntu using the same version, but it is not possible to open any Access database there... It option is not implemented (...)


"SQL Status: HY000

The connection to the external data source could not be established. No SDBC
driver was found for the given URL."


We are aware that this can be workarounded by using the ODBC connector, but this is not an option since the process needs to be done by plain users with poor (or none) computer skills.

With such amount of machines to maintain, we are facing a big problem because at this point we must evaluate wehther going back to OpenOffice again or trying to downgrade to LibreOffice 3.3. Is there any plan to fix this at some point?

Thanks in advance!
Best regards!
Comment 25 Alex Thurgood 2011-11-17 02:39:43 UTC
I have added the NEEDINFO keyword because someone really needs to test this with a download from master on Windows to see if the problem hasn't already been fixed. It is pointless going on about fixing it in 3.4.x if no-one has tested a recent master build.


Alex
Comment 26 Alex Thurgood 2011-11-17 10:49:19 UTC
Hi all,

I maanged to get hold of a machine with Windows 7 Pro to test. I downloaded the latest available daily build from master from the 2008R2 buildbot, i.e. from 26/11/2011.

I can confrim the bug, at least when trying to read an Access 2007 file (accdb). I don't have any Access 03 (mdb) files around (last time I used Access was well over 10 years ago).

The wizard will let you choose the file, and seems to attempt the connection, but this then throws the error message that others have already provided.

So it is not fixed in master, and it really ought to be sorted out as it means that there is currently no point in anyone on Windows to upgrade from 3.3.3 to any of the 3.4.x versions if they require db connections to an Access db source.

Removing needinfo keyword.

Alex
Comment 27 Alex Thurgood 2011-11-17 10:58:06 UTC
Found an Access 03 file to test on.
Bug confimrmed.

Details of error message returned :
A connection for the following URL was requested "sdbc:ado:access:PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Users\userA\Downloads\zipcodes.mdb".


Changing subject line to extend to cover all direct connections to Access database types.

Alex
Comment 28 Lionel Elie Mamane 2011-11-20 03:21:22 UTC
There are two layers where this can go wrong:

1) No or non-functional ADO->SDBC driver

2) No Jet->OLE DB provider (driver)

ADO is an object wrapper around OLE DB, and Jet is the DB engine behind MS Access mdb files.

From the error message that people get, my guess is that their LibreOffice does not have the ADO->SDBC driver, and this needs to be fixed. In other words, the problem is point 1.

Could people having this problem please:

1) check for a file named "*ado*.dll" or something similar in their LibreOffice installation (whether the ADO<->SDBC driver is there).

2) check whether they can access any _other_ database through ADO in LibreOffice. See http://www.carlprothman.net/Default.aspx?tabid=87 for examples. This would establish that LibreOffice <-> ADO works and that the problem is MS Access/mdb-specific.

3) If you have basic Perl scripting skills, see http://www.perlmonks.org/?node_id=117713 for an example script for how to connect to a MS Access mdb file through ADO/OLE DB. Does that work? This would establish that the ADO <-> OLE DB <-> Jet <-> mdb works and that the problem is LibreOffice-specific.





Now, about point 2:

Note at http://msdn.microsoft.com/en-us/library/ms811968:

 The Microsoft Jet OLE DB Provider and (...) were removed from MDAC 2.6.

http://support.microsoft.com/kb/271908 contains instructions on how to install the Jet OLE DB provider on MS Windows 2003 and earlier. On the other hand, http://support.microsoft.com/kb/239114 suggests that Windows Vista and 2008 already have Jet preinstalled, and presumably the Jet OLE DB provider with it. Don't know about Windows 7.
Comment 29 Alex Thurgood 2011-11-20 05:58:35 UTC
(In reply to comment #28)

Hi Lionel,

> already have Jet preinstalled, and presumably the Jet OLE DB provider with it.
> Don't know about Windows 7.

On the Windows 7 Pro machine I have access to, MDAC includes the ADO stuff.

I can also see the file adolo.dll in 3.5 :
C:\Program Files (x86)\LibO-dev 3.5\program

However, in 3.4, I see adomi.dll :
C:\Program Files (x86)\LibreOffice 3.4\program


The question is : why are the names different ?

Alex
Comment 30 Lionel Elie Mamane 2011-11-20 06:17:53 UTC
So, I managed to get my hands on a MS Windows 7 machine.

(In reply to comment #28)
> Could people having this problem please:
 
> 2) check whether they can access any _other_ database through ADO in
> LibreOffice. See http://www.carlprothman.net/Default.aspx?tabid=87 for
> examples. This would establish that LibreOffice <-> ADO works and that the
> problem is MS Access/mdb-specific.

It seems to be indeed the whole of ADO<->LibreOffice that is broken. I can access MySQL through ODBC through ADO with OpenOffice 3.2 (with *very* partial feature support), but not with LibreOffice 3.4.4.

(In reply to comment #29)

> I can also see the file adolo.dll in 3.5 :
> C:\Program Files (x86)\LibO-dev 3.5\program
 
> However, in 3.4, I see adomi.dll :
> C:\Program Files (x86)\LibreOffice 3.4\program

> The question is : why are the names different ?

Let's compare with another sdbc driver: has fileme.dll also been renamed to filelo.dll in 3.5?
Comment 31 Lionel Elie Mamane 2011-11-20 06:18:42 UTC
(In reply to comment #30)
> Let's compare with another sdbc driver: has fileme.dll also been renamed to
> filelo.dll in 3.5?

I meant:

Has filemi.dll also been renamed to filelo.dll in 3.5?
Comment 32 Lionel Elie Mamane 2011-11-20 06:23:46 UTC
(In reply to comment #29)

> I can also see the file adolo.dll in 3.5 :
> C:\Program Files (x86)\LibO-dev 3.5\program

> However, in 3.4, I see adomi.dll :
> C:\Program Files (x86)\LibreOffice 3.4\program

> The question is : why are the names different ?

It seems coherent with what happens on GNU/Linux:

3.5 has libfilelo.so, but 3.4 has libfilelx.so. Probably the suffix was changed from a platform-specific suffix to a constant "lo"?

My guess is:

lo = constant for all platforms in LO >= 3.5
lx = GNU/Linux in LO <=3.4
mi = Microsoft Windows in LO <= 3.4
Comment 33 Lionel Elie Mamane 2011-11-20 06:34:43 UTC
I've poked a bit at "git log", here is an interesting commit which I think might have introduced this bug:

commit 9551bf5a1ee5c9275a26f8ec4021e0b7f5665176 in master = b9ac40c5e85941fb7896ce11c005106b69a89ec8 in "old" libs-core for libreoffice-3-4
Author: sb <sb@openoffice.org>
Date:   Fri Sep 10 13:10:07 2010 +0200

    sb129: #i113189# change UNO components to use passive registration

If someone with access to a windows build could revert the part of it that applies to connectivity/source/drivers/ado, rebuild and check whether this bug is still there (or if the error message changes)?
Comment 34 Lionel Elie Mamane 2011-11-20 07:14:48 UTC
Confirming that:

1) ADO -> SDBC driver is compiled and shipped
2) ADO -> SDBC driver fails to start

Compare the following Basic macros:

Sub ChkODBC
	Dim foo as Object
	foo = CreateUnoService("com.sun.star.comp.sdbc.ODBCDriver")
	If IsNull(foo) Then
		MsgBox "Got NULL"
	Else
		MsgBox "Success"
	End If
End Sub

Sub ChkADO
	Dim foo as Object
	foo = CreateUnoService("com.sun.star.comp.sdbc.ado.ODriver")
	If IsNull(foo) Then
		MsgBox "Got NULL"
	Else
		MsgBox "Success"
	End If
End Sub

ChkODBC succeeds, but the CreateUnoService fails for ChkADO. Error message:
 file:///c:/Program%20Friles/LibreOffice/%203.4/program/../program/adomi.dll: cannot get factory of demanded implementation: com.sun.star.comp.sdbc.ado.ODriver

So at least LO finds the right .dll and tries to use that right dll, but the dll itself errors out?
Comment 35 Lionel Elie Mamane 2011-11-20 08:19:41 UTC
(In reply to comment #33)
> I've poked a bit at "git log", here is an interesting commit which I think
> might have introduced this bug:

> commit 9551bf5a1ee5c9275a26f8ec4021e0b7f5665176 in master =
> b9ac40c5e85941fb7896ce11c005106b69a89ec8 in "old" libs-core for libreoffice-3-4
> Author: sb <sb@openoffice.org>
> Date:   Fri Sep 10 13:10:07 2010 +0200
> 
>     sb129: #i113189# change UNO components to use passive registration

Given my later investigations, that commit is probably not the culprit after all.
Comment 36 Alex Thurgood 2011-11-20 08:33:44 UTC
(In reply to comment #31)



> I meant:
> 
> Has filemi.dll also been renamed to filelo.dll in 3.5?


Hmm, yes, you could be right, I remember there being a discussion about that on the dev list a while back.


Alex
Comment 37 Alex Thurgood 2011-11-20 08:35:13 UTC
(In reply to comment #32)

> My guess is:
> 
> lo = constant for all platforms in LO >= 3.5
> lx = GNU/Linux in LO <=3.4
> mi = Microsoft Windows in LO <= 3.4

I'm not in front of the machine at the moment, so I can't check, but I think you are correct. The naming scheme was changed for 3.5 from memory.

Alex
Comment 38 Lionel Elie Mamane 2011-11-20 08:41:48 UTC
I think I've nailed it down. Pushing to master, waiting for a tinderbox to succeed and then I can test and apply for review to libreoffice-3-4
Comment 39 Alex Thurgood 2011-11-20 09:42:41 UTC
Hmm, wouldn't by any chance be linked to the commits by Caolan and others to change createFromAscii -> RTL_CONSTASCII_USTRINGPARAM ?


I see that a load of these went into the code from Jan to Aug, 2011.


Alex
Comment 40 Lionel Elie Mamane 2011-11-20 09:55:08 UTC
(In reply to comment #39)
> Hmm, wouldn't by any chance be linked to the commits by Caolan and others to
> change createFromAscii -> RTL_CONSTASCII_USTRINGPARAM ?

Precisely. IMHO Julien Nabet was overeager in 2102c0cf on 2010-11-18 08:35:47; he changed createFromAscii to RTL_CONSTASCII_USTRINGPARAM at a place (in Aservices.cxx) where the argument is not a compile-time constant! That's what the patch I'd like to test does. See http://cgit.freedesktop.org/libreoffice/core/commit/?id=0e9473eca6f559bc83eb061384b0db936e4ed8e1
Comment 41 Lionel Elie Mamane 2011-11-22 03:31:29 UTC
(In reply to comment #38)
> I think I've nailed it down. Pushing to master, waiting for a tinderbox to
> succeed and then I can test and apply for review to libreoffice-3-4

With the new MinGW daily build, I can connect to MySQL through ADO (through ODBC). Connecting to a MS Access (.mdb) database hangs LO, but that may be a MinGW-build-specific bug.

Waiting for another Windows tinderbox to give us a daily build.
Comment 42 Winfried Donkers 2011-11-22 04:35:06 UTC
With the new MinGW daily build (November 22, 2011, I can connect to MS Access databases. Congratulations, Lionel!
Comment 43 Lionel Elie Mamane 2011-11-22 06:05:35 UTC
(In reply to comment #42)
> With the new MinGW daily build (November 22, 2011, I can connect to MS Access
> databases. Congratulations, Lionel!

Oh, that's good news. Could you please also test the following:

New database
connect to existing database
take "ADO" in drop-down list
click "next"
it asks for an ADO connection string/URL, click "browse"

Do you get a half-decent UI that allows you to choose different OLE DB providers, set the main settings (username, location, ...)?
Comment 44 Julien Nabet 2011-11-22 07:21:44 UTC
(In reply to comment #40)
> (In reply to comment #39)
> > Hmm, wouldn't by any chance be linked to the commits by Caolan and others to
> > change createFromAscii -> RTL_CONSTASCII_USTRINGPARAM ?
> 
> Precisely. IMHO Julien Nabet was overeager in 2102c0cf on 2010-11-18 08:35:47;
> he changed createFromAscii to RTL_CONSTASCII_USTRINGPARAM at a place (in
> Aservices.cxx) where the argument is not a compile-time constant! That's what
> the patch I'd like to test does. See
> http://cgit.freedesktop.org/libreoffice/core/commit/?id=0e9473eca6f559bc83eb061384b0db936e4ed8e1

Hello Lionel,

I don't know what to say except sorry guys for all this :-((
Hope I haven't made other mistakes like this one. It was at the beginning I contributed.

Sorry again.

Julien.
Comment 45 Lionel Elie Mamane 2011-11-22 08:38:40 UTC
Michael Meeks has reviewed and committed my patch to the libreoffice-3-4 branch. This fix will be part of LibreOffice 3.4.5 and 3.5.0beta0.

(In reply to comment #44)
> (In reply to comment #40)
>> (In reply to comment #39)

>>> Hmm, wouldn't by any chance be linked to the commits by Caolan and others to
>>> change createFromAscii -> RTL_CONSTASCII_USTRINGPARAM ?

>> Precisely. IMHO Julien Nabet was overeager in 2102c0cf on 2010-11-18 08:35:47;
>> he changed createFromAscii to RTL_CONSTASCII_USTRINGPARAM at a place (in
>> Aservices.cxx) where the argument is not a compile-time constant!

> I don't know what to say except sorry guys for all this :-((

Oh well, only people that do nothing make no errors. And responsibility is at least shared with whoever reviewed your patch before committing it ;-) OTOH, it is very easy to miss a wrong replacement in a sea of "good" replacements (just like it is easy to miss a suitcase with a bomb after seeing 100 suitcases without a bomb: the concept is called "change blindness").

The interesting thing is that this would have been caught by compiling with "gcc -std=c++0x" or "gcc -std=gnu++0x" :) But as this was Windows-only code, well, nobody ever did that, I suppose.
Comment 46 Winfried Donkers 2011-11-22 22:48:21 UTC
(In reply to comment #43)

I tested the following 'connect to exiting database' options:
-Microsoft Access : Ok, including editing data;
-Microsoft Access 2007 : not tested, no files;
-ADO : when clicking browse-button, nothing happens, I can click the button multiple times;
-MySQL : via ODBC, browse gives a decent browse window to select a file;
-ODBC : browse gives a decent browse window to select a file;
-spreadsheet :  browse gives a decent browse window to select a file;
-dBase :  browse gives a decent browse window to select a file;
-text : browse gives a decent browse window to select a file;
-JDBC : not tested, driver not present on machine.
Except for ms access I hav not opened any file.

It was a bit strange to notice that the brwose window was not the same for the different options, e.g. spreadsheet and text produce different types of browse window.

If you (Lionel) have further questions, please ask and I'll try to answer them as soon as I can.
Winfried
Comment 47 Alex Thurgood 2011-11-23 01:59:33 UTC
(In reply to comment #46)


Hi Winfried,

> -ADO : when clicking browse-button, nothing happens, I can click the button
> multiple times;

Confirming - this is already reported as :

https://bugs.freedesktop.org/show_bug.cgi?id=32347

Note that I have opened a new bug report against .accdb files (MS Access 2007/2010) because the tables therein open read-only :

https://bugs.freedesktop.org/show_bug.cgi?id=43187


Alex
Comment 48 Thomas Thym 2011-12-06 23:01:17 UTC
Thanks to anybody involved for fixing that bug. You rock!