Ask Your Question
0

mdb files not loading to base

asked 2018-02-09 23:00:09 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

run window 10 64 bit Libre office V6.0.0.3 have Java 8 update 161 and Java 8 update 162 (64 bit)

error 1

the Connection to the data source " name here" counl not be estabilished. the connection could not be created may be the necessary data provider is not installed

click more on the box you get two error both the connection to the data soure "New database1" could not be estabilished

click ok the libreoffice base open and not data load

error 2 if i cleck on the MDB file it opens in Libre write

question 1 data provider is not installed what is it and what file do i have to have?

question 2 is error 2 part of error 1

edit retag flag offensive close merge delete

Comments

Hello @taybee,

First please do not post as Wiki. It helps no one.

This is second post today with this problem. As LO 6.x has a number of Bugs in Base, it may be the cause of the problem. Have you tried this with v5.4.x? Might be best to report as BUG - click to report to Bugzilla .

Ratslinger gravatar imageRatslinger ( 2018-02-09 23:20:13 +0200 )edit

hi uninstall V6 install V5 same error as above

taybee gravatar imagetaybee ( 2018-02-10 00:56:48 +0200 )edit

Will need to dig out old Windows system. Give me about 30-60 minutes. My connection on Linux works fine but it uses a different connection method than Windows.

Ratslinger gravatar imageRatslinger ( 2018-02-10 01:01:42 +0200 )edit
1

Try it with a 32-bit Version of LibreOffice.

Regina gravatar imageRegina ( 2018-02-10 01:13:39 +0200 )edit

Confirming @Regina comment. My Windows is old 32 bit with LO5.3.x & Java all being 32-bit. Access connection works fine. Just need the proper location of the .mdb.

Ratslinger gravatar imageRatslinger ( 2018-02-10 01:38:11 +0200 )edit

how run a v5 32 bit system Error 1 is fix how but error 2 is the same on this version also

taybee gravatar imagetaybee ( 2018-02-10 02:47:50 +0200 )edit

@taybee Sorry I missed that point. You cannot open an .mdb file directly in LO. You can only connect to it through Base and then only for tables & queries. Forms & macros are non-transferable.

Ratslinger gravatar imageRatslinger ( 2018-02-10 02:54:05 +0200 )edit

@Ratslinger thanks for your help and the info on Base will close this off how

taybee gravatar imagetaybee ( 2018-02-10 03:08:09 +0200 )edit

Your welcome & thanks to @Regina. @taybee if you want to close there is selection ( Xclose) lower right of your question. Then you can select reason in dropdown.

Ratslinger gravatar imageRatslinger ( 2018-02-10 03:13:47 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-02-12 04:33:44 +0200

Ratslinger gravatar image

Hello, @taybee,

I have re-opened this question because I have discovered a way to connect LO Base 64-bit with Access in Windows 10 (guessing other versions should work).

Not having used Windows for years (with rare exception to test on an old 32-bit system) I never got to test this process. Was able to do so on a Win 10 Home 64-bit system using LO v5.3.x 64-bit.

On my Linux Mint18.3 system I use a connector UCanAccess (website here) to connect to Access DB's. Turns out this same connector works on Windows 10 also. Although stated as so, have not personally tried with LO until now. Have tested with UCanAccess v4.0.3 and v3.0.4 and both work.

First download and unzip the connector files into a folder. Now open any LO file & go to Tools->Options then LibreOffice->Advanced. Select Class Path button then Add Archive button. Then point to the ucanload.jar file in the unzipped download files. It is in the loader sub-directory. Then Open button. Class path will look similar to:

image description

depending upon where you actually put the unzipped files, Then select OK button.

Next you need to add a parameter. Still on the Advanced page, select the Parameters... button. There, in the Java start parameter line enter -DUCANACCESS_HOME=C:\YOUR_FOLDER_LOCATION\UCanAccess-4.0.3-bin then click the ADD button with a result similar to:

image description

Now you are ready to create a connection to the Access file from Base. Open Base & select Connect to an existing database with the dropdown selection at JDBC then click Next. In the Datasource URL enter (after pre-existing jdbc:) ucanaccess://C:\YOUR_ACCESS_FILE_LOCATION. In JDBC driver class: enter net.ucanaccess.jdbc.UcanloadDriver. Test Class button should result in successful driver load. If error, recheck Class Path setting in Advanced. Click Next button. Can test connection here. If error, check for proper file location (note: // before file location is important).

image description

Above screen is slightly different because it was taken directly from the Base file. Entries are the same. Click Finish button and save.

Your tables should now be accessible.

edit flag offensive delete link more

Comments

I have spent all day following these instructions (and correcting the wrong entries of my fat fingers), and I have succeeded in opening one of my old databases in LO at last, so the instructions do work. However, in spite of entering its location into 'Datasource URL', whenever I try to access the database I really need to be able to get at, I am told that it does not exist. I must be doing something silly. What can it be?

chrisg gravatar imagechrisg ( 2018-02-13 18:15:42 +0200 )edit

@chrisg Is it possible the database you can't access is not one of those supported as mentioned on the website:

  Supported Access formats: 2000, 2003, 2007, 2010.

This copied from first line under 'Features:'

Ratslinger gravatar imageRatslinger ( 2018-02-13 18:26:26 +0200 )edit

I think the problem lies right at the end. When one pushes 'Finish' you are asked to save the file. The only option is to save it as an .odb file (which already exists). If I select this option, and to overwrite the existing file, I am told it cannot be written. So what now? The earlier database which worked is, as far as I know,of the same vintage. Both are .mdb files. The one that does not work was in fact created by copying the earlier one. How does one tell which version the file is?

chrisg gravatar imagechrisg ( 2018-02-13 18:38:41 +0200 )edit

Not sure what you are attempting. Understand I am currently testing with Linux Mint as only had short access to Win system.

Have Access2000 mdb. Copied it. Created new Base file to access copied mdb. Entered all setup info using copied mdb. On 'Finish' selected old odb & had it overwrite it. No Problem. Newly overwritten odb now accesses the copied mdb.

Also tried just modifying the existing odb to point to different mdb using Edit->Database->Properties... and again no problem.

Ratslinger gravatar imageRatslinger ( 2018-02-13 19:13:39 +0200 )edit

BTW I don't believe there is any easy way to tell the version just from the file. It seems you need Access or some other tools from MS. Since this is for Windows it is outside of my realm. Have tried to use Wine on Linux several times but never any success getting programs to install and run.

Ratslinger gravatar imageRatslinger ( 2018-02-14 04:00:42 +0200 )edit

Tried again. I can now open the file. When I try to open tables, I get a message saying that warnings were encountered, to the extent that the user either lacks privilege or the file (whose name starts @@ end includes REFS3 - which I did not think was involved), but when I press OK, I can then open the tables, but no queries or reports are visible. I guess I can update the tables but not make use of the report which I need.

chrisg gravatar imagechrisg ( 2018-02-22 23:55:44 +0200 )edit

Sorry, I should have added that I have now updated LO to 5.4.5.1 x64

chrisg gravatar imagechrisg ( 2018-02-22 23:57:53 +0200 )edit

Also have 5.4.5.1 installed. Table warnings can be the result of many things. If you mean Access queries & reports, it has always been the case that only Tables and possibly queries (don't know exact circumstances) are accessible. Forms and Reports originally created in Access are not. Reading back over the original question (December 2017) I see some references to REFS3. But exactly what you have and what you have done to date is sketchy. Queries, reports, which odb has what?

Ratslinger gravatar imageRatslinger ( 2018-02-23 00:11:09 +0200 )edit

@chrisg Start a new question with what your current problems are. Include all the dealings & procedures you have gone through thus far. This post was actually created by someone else & has been answered.

Ratslinger gravatar imageRatslinger ( 2018-02-23 06:19:03 +0200 )edit

@Ratslinger - How is it possible that this incredibly detailed "how-to" has only one (1) upvote (mine!)??? This is brilliant. It deserves a medal, not a "badge"! :) Thank you!

David gravatar imageDavid ( 2018-04-19 15:18:41 +0200 )edit
1

answered 2018-02-14 13:18:20 +0200

RandallH gravatar image

@Ratslinger : Thanks for the detailed steps to set up UCanAccess connector and the LO base Advanced settings. Very precise and accurate. Worked great in MS10 x64, a MSAccess2003 MDB and with LO 5.4.5.1 (x64). Connected to 12 tables with over 100,000 records. Next step is to copy tables to new odb and see how the speed is.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-09 23:00:09 +0200

Seen: 365 times

Last updated: Feb 14