I am running Windows 10 Home; Libre Office 5.3.7.2 x64 and Java 1.8.0.151, through Firefox 57.0.2, all of which are 64 bit as far as I know. I have looked at any number of queries and, after trying many of the solutions during the past week, I have at last managed to get LO (Options, Advanced) to display a JRE. However, whenever I try to open a table, I am still told that a connection to the data cannot be established. It is suggested that the necessary data provider is not installed.
Who or what is the data provider which has apparently disappeared as a result of my upgrade?
It occurs to me that I have not explained how my database is configured (because that hasn’t changed - perhaps it should). The underlying database is an Access database called REFS2.mdb. It was created in Ability years ago, converted to Access and, until recently, accessed through an LO file called REFS3.odb. I can open REFS3, but when I try to open a table, I am told that the data source REFS3 cannot be accessed. On the opening Base screen, under ‘Open existing database’ (greyed out) it says REFS3; under ‘connect to an existing database’ it says JDBC. Under the latter heading, I can tell it to alter this to a Microsoft Access database; I then get invited to save it as a new database (with, I assume, a new name), but the result is still the same (the data source - now with the new name- is not accessible). Does this info help to identify the problem and the solution?
Just in case: can you make sure that your Java is indeed 64-bit? Use Configure Java in Windows Run menu (press Windows key, and start typing Java). There, you can look into Java tab, and click View to see installed instances (for user and for system) and their architecture (you need it to have at least one x86_64, which should be then enabled in LibreOffice).
Hi Mike
Over the last couple of weeks, I have checked that java 1.8.0.51 is indeed marked as x86_64 and is shown as being enabled in the database, on numerous occasions. Actually there are two JREs shown - 151 from Oracle (which I an trying to use) and 152 from Azul. Both are x86_64. But, basically, apart from the two JREs being now shown in LO advanced options, I am no nearer being able to use my databases than I was two weeks ago!
Chris
It appears you have set something incorrectly in Options, Advanced. Class path is used to connect to databases other than embedded HSQL. It is important to know which DB your base file is set for. Verify what if anything is in class path under advanced. Also, make sure the selection for JRE selected is the “Oracle” one. Not sure about ‘Azul’ - never seen that one.
Many thanks Ratslinger, for your long answer below. I hope the amendments I have made to the original question will enable you to suggest a solution!
Chris
@chrisg have you seen comments below my answer? Need much more info!
Are you seeing that you have messages? RED envelope next to your name - click it.
@chrisg Not sure what you are changing in your question but the additional information is of very little help. Only thing of value was knowing this is an Access DB. Please, again look at the comments below my answer. You still have not provided the necessary information to proceed. Is there something that is not clear?
Hello,
Libre Base in most cases requires JRE (or JDK) in order to operate. This, however, has nothing to do with the type of database which is to be used in conjunction with Base.
As a default, LO provides HSQLDB v1.8 Embedded (and experimental FireBird Embedded) as the database. Embedded database is a database contained entirely within the Base .odb file. This method, while portable, is not a favorable one. It has been widely know to lose data (especially with volume or embedded pictures) and should be avoided.
The next step up is a split database - one where the actual database data resides outside of the .odb file. Most implementations of this method rely upon one or more macros to modify settings in an .odb file (content.xml) to assist in pointing to where the database (hsqldb.jar file) and its’ data reside. In some implementations this is not done. When this occurs, Base needs to know where the hsqldb.jar resides. This is where the Class Path comes in. It contains the location of this file. However, this (specifically for HSQLDB) is very dangerous.
Let’s say you have created a Base file using the embedded DB in a vanilla setup; that is nothing in Class Path. When Base starts, it sees this is a HSQLDB Embedded setup and opens it as such using the default DB. All is fine. Now you add a class path to a different HSQLDB - maybe v2.4. Now using this same Base file, it is opened by the database set in Class Path. In doing so, the internal .odb settings for the database are modified to reflect the v2.4 database being used. Unfortunately, this Base file actually works upon this initial opening. Now you are done & close the Base file. If you try to open it again you will receive an error - The connection could not be established. The database was created by a newer version of LibreOffice. This is because when trying to connect to the database the settings are all incorrect for the actual database needed! Your Base file is now corrupt. Repair is difficult.
There are many variations on Class Path including re-directing this location in the ‘content.xml’ file previously mentioned. So this, finally, may bring us about to the error you are receiving.
When using another database such as MySQL, PostgreSQL and others, you need a connection from Base to that database. This is a connector which allows communication between the two. It is somewhat specific because each database has its’ own structure and sometimes SQL differences. If this connector is NOT a part of LO (for example PostgreSQL is) then you need to provide the connector and tell LO where it is. This is where Class Path is needed. It points to this connector to be used. You can have multiple Class Paths set with each for a different DB connector type. If your Base file is set for MySQL JDBC connection and it there is no setting in Class Path you will get the error you have mentioned. Base has no way to communicate with the specified database.
The connection ‘type’ for Base is seen in the lower left corner of the opening Base screen.
Your exact problem cannot be addressed without this information. It may state “JDBC”, “MySQL(Native)”, “ODBC” or something else. But this is the starting point.
Edit 12/27/2017:
Unfortunately, after many attempts, needed information has still not been provided. Based upon the statement in the question, the Base .odb is connected to an Access DB on a Windows system. As I gave up on Windows a few years back and use only Linux now, in order to proceed any further I had to dig out an old Windows machine. The LO was v5.3.0.3 but that should have no bearing on the situation. First, from any LO menu Tools->Options then LibreOffice->Advanced:
Next Class Path (from clicked button) is empty:
Then no Parameters are set:
Now that can all be closed. The Base file is next opened. Here is the main screen:
This is a good connection as the tables section is already displayed. Notice in lower left corner that this is a Microsoft Access connection. To its’ right (bottom center of main screen) is the DB it is connected to: C:\BaseFiles\AccessTest.mdb. If this is NOT where the actual .mdb is located, you can change it. From the menu select Edit->Database->Properties... to get this screen:
Here you can change where the actual .mdb is currently located. Once changes are made, save the .odb, close it then open it again. Should be able to access tables.
This is all I can do with the information at hand. It should not have even come to this extent!
First, let’s try & keep the teminology straight otherwise mass confusion will result. When you state “On the opening Base screen…” should mean the screen when you first open REFS3.odb but in the context you presented it it about creating a NEW Base file/connection. The two are vastly different.
Your problem seems to be as I stated in my answer. The connection between Base & the database (yours being Access) is missing. Need more info.
What has changed since “REFS3.odb” was last opened successfully? Where is the Access REFS2.mdb? Has its’ location changed? New LO? Changes in OS? Open “REFS3.odb”. What is stated in lower corner of this screen? What is stated on Bottom center of this screen? From the menu, select Edit->Database->Properties... What is displayed on the dialog? May be more but this is a start.
REFS2.mdb is in the same folder as REFS2,odb (as it always has been). Yes, LO has been updated (and uninstalled and reinstalled), but both it and Java are 64bit. No change in OS. When I open REFS3, it says Microsoft Access at bottom left of the screen. After ‘Edit, Database, Properties’ I get a screen showing ‘Advanced Properties’ with a box in the middle with the address of REFS2 in it, but if I ‘Test Connection’ I get the same old box telling me that the connection cannot be created.
What I do notice is that, although the reference to REFS2 is shown on the opening REFS3 screen, the same old box tells me that the connection to REFS3 cannot be created. Surely it should say REFS2?
But if I try it, it tells me that REFS2.odb does not exist (which, of course, is true.
I filed a Bug Report on 8 Dec 2017 and had quite a long correspondence with Jacques Guilleron, but with no solution. Libre Office is touted as being a simple and user friendly system, but a system which can suddenly stop working with no solution in two weeks does not seem to be either! But many thanks for your valiant, if unsuccessful efforts on my behalf
You are very stingy with information. Knowing something like this upfront could have saved me HOURS of wasted time! This actually SHORT correspondence only discusses JRE and your reference to the 32-bit folders. Even that minimal conversation is confusing in as much as you don’t seem to know what LO or JRE bitwise is actually installed. If you cannot be specific on the problem & what is installed it is unlikely to get help.
According to Bug #114361, you state you installed LO 64-bit LO & Java JRE but later in the post you are searching c:\\program files (x86)\java\ which is the 32-bit directory because you stated Java Control Panel tells me that I have a JRE at c:\\program files (x86)\java. This is a problem. 64-bit Java should be in C:\Program Files\Java\ as even the image in my answer (all based upon 64-bit) shows.
So the bottom line is, IF your Advanced screen looks like the one I posted (version doesn’t matter) AND your Class Path is empty as shown AND your parameters are empty as shown THEN you need to file a BUG REPORT regarding the inability to connect to an ACCESS DB. This is NOT the same as the one filed. It may be you should close #114361 IF in fact you have resolved the JRE issue. Finally, Firefox (which you mention in various places) has NOTHING to do with all of this.