[Tutorial] MS Access and OOo Base

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

[Tutorial] MS Access and OOo Base

Post by r4zoli »

You can not open MSA .mdb or accdb files with Base, but you could connect to it, only on MS operation sytems.
(Base can not be connected to other MSA file types such as mde, mdt, accdt, accde etc.)

On linux you can use mdb-tools for connection to mdb files, but it can lost data, and the driver development is ceased.

On OS X you can try same mdb-tools, same warning as in linux case can loss data.

New>File> Database select third option connect to existing database, from drop down list select "Microsoft Access" for mdb, or "Microsoft Access 2007" for accdb files and finish.

You can reach only tables, queries, the forms, reports and coding not usable, needs to create from scratch.

More detailed description on OOo wiki:
MSA-Base Faq
Connecting to Microsoft Access

Useful posts you can find this forum if you use search facility with access keyword.
The two most useful:
Base Vs MS Access ?
[Solved] I can't get an Access file to open with Base
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Tutorial] MS Access and OOo Base

Post by Arineckaig »

(Base can not be connected to other MSA file types such as mde, mdt, accdt, accde etc.)
Strangely enough I have found that Base does appear to connect to Access 97 .mde back-end files using WinXP but without the MS Access program.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] MS Access and OOo Base

Post by Villeroy »

Arineckaig wrote:
(Base can not be connected to other MSA file types such as mde, mdt, accdt, accde etc.)
Strangely enough I have found that Base does appear to connect to Access 97 .mde back-end files using WinXP but without the MS Access program.
Why not? A full mdb contains the backend database (tables, indices, relations) together with the frontend of forms, queries, reports, macros and Base connects to the database through the Microsoft driver, ignoring the whole frontend.
The mde file contains only the backend part which is relevant for the connection. No tool other than Access can handle the frontend parts.
Last edited by Villeroy on Mon Feb 18, 2013 7:59 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Tutorial] MS Access and OOo Base

Post by RPG »

Hello

Maybe this script can be useful for other people. It was working for me but I did only use it twice for testing.
The comment is in the script.

This script needs the MDB tools. You can this install most of the time with the installer what is standard with your distro. I don't know how you can this install for each distro.

For more information about the MDB tools see here.

Romke

Use this file first for extracting data

Code: Select all

#!/bin/bash
# this script is only working with linux.
# the purpose is that this scrip export all files from the mdb database as CSV
# The option I use
#a) Only data files are exported.
#b) Date are exported in year month day style for using in HSQLDB style
#c) Numbers are exported with a point as decimal separator
#
#The script take care all MDB files are exported as a CSV.
#For each MDB file there is made a new MAP.
#
#I have select this method there it was the most easy way to import data in CALC.
#When you still have MS Acces use MS Acces for exporting dat to a CSV. I think this is the best method.


# start this script up from the place where the MDB files are. Then the files are stored in the same map.
# It is not important where this script is.

# Set the field separator to one line
IFS=$'\n'

for database in $(ls -1 *.mdb )
    do
        DirNaam="${database%*.mdb}" # Strip MDB from the file name and make the map name
        test -d "${DirNaam}" || mkdir "${DirNaam}" 
    
        for tabel in $(mdb-tables -1 "${database}")
            do
            #echo "Ik werk aan deze : $database  $tabel"
            echo "Processing this : $database  $tabel"
# There are three lines in this script select the line you need
# American   month day year
# Europe       day month year
# HSQLDB   year  month  day
#             mdb-export -D "%m-%d-%y" "${database}" "$tabel" >"${DirNaam}/${tabel}.csv"
#             mdb-export -D "%d-%m-%y" "${database}" "$tabel" >"${DirNaam}/${tabel}.csv"
             mdb-export -D "%Y-%m-%d" "${database}" "$tabel" >"${DirNaam}/${tabel}.csv"
            done
    done
This move files with only one to a map emptyfile

Code: Select all

#!/bin/bash
# Walk trough a map 
# test then if all files have one line
# When they have one line then move the file to a map for empty files
# You can give your own name
emptyfiles=EmptyFiles
# make a map when the name does not exist
test -d $emptyfiles || mkdir $emptyfiles

# Now test all the files who ens on .csv 
# When there is only one line it means the table is empty.
for fileName in *.csv
	 do
		test  "$(wc -l   "$fileName")"  = "1 $fileName"   &&  mv   "$fileName" "$emptyfiles/."
	done


This is for extracting table definition

Code: Select all

#!/bin/bash
# This extract the table defination from the database.
#When you use sybase then it give less problems for HSQLDB.
# I have not test it if you can use it.
# Supported values are access,
#              sybase, oracle, and postgres. If not specified the generated
#              DDL will be in access forma


# this script is only working with linux.
# It is not important where this script but I think a good place is 
# a) not in the map where the file are
# b) store them in a separate map named bin

# Set the field separator to one line
# we assume that we are working in a map what does have the name of the database.
# We have only to add the extension to then
# we add "../" in front and then it can work
database=../"${PWD/*\/}.mdb"
for datafile  in *.csv
    do
        # now extract table information 
        mdb-schema "${database}" sybase >"${datafile/csv/ddl}"
    done
Explanation

Code: Select all

This files are handling a mdb database. It use the mdbtools.

step 1 : Extracting data

ExtractData : extract the files from the database 
I think the best method is store the mdb files you have in a separate map with only mdb files.
then run the script ExtractData

Step 2 : Moving empty file
When you use a text database then you can read now all the file who are extract and are in a single map.
When you see you have a lot of empty files then MoveEmptyFiles can move all files without data to an other map.
This script is working in the map from where you start it up. It make a new map in this map and move there the empty files to.


Step 3: Extracting table definitions.
This is also working in the map from where you start this script.
For each file who ends on .csv it makes a new file with the table definition. 
It assume that the map name is a name of a mdb database who is on level higher.


This scripts don't contain error checking.
I think they work easy 
Attachments
ExtractFiles.zip
This contains all the code files
(3.19 KiB) Downloaded 1221 times
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Horus
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: [Tutorial] MS Access and OOo Base

Post by Horus »

I know "no question is allowed in this forum", so I'm not going to ask "Could someone add this to ooBase FAQ, please?"
Instead, I would say "It would be nice if someone could add this to ooBase FAQ http://wiki.services.openoffice.org/wiki/FAQ_%28Base%29 ;)

Actually, I've seen lots of threads asking more or less the same question "how to convert .mdb to .odb" and the answer is "no". When we do an Internet search on "convert .mdb to .odb", there's no "official" answer, hence the idea to have it in FAQ.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] MS Access and OOo Base

Post by Villeroy »

http://sourceforge.net/projects/ucanaccess/files/ is a platform independent JDBC driver for MS Access to be used with HSQL 2.3 and Libre/OpenOffice as described here: http://askubuntu.com/questions/187389/i ... 571#519571

I tested this setup almost successfully (with minor flaws) under LInux with Microsoft's Northwind.mdb which is a demo database of intermediate complexity for older versions of MS Access. With the right Java setup it should work with any version of Open/LIbreOffice under Linux, Windows and the Mac.

This enables us to query MS Access databases in standard conformant ways using the HSQL engine.
Example by Sliderule using the HSQL system schema:

Code: Select all

Select
   TABLE_NAME,
   CARDINALITY as NUMBER_OF_RECORDS
From INFORMATION_SCHEMA.SYSTEM_TABLESTATS
Where TABLE_SCHEMA = 'PUBLIC'
At the same time we can use some (all?) Access specific terms in direct query mode.
Access function DLookup tested with my Northwind example:

Code: Select all

SELECT DLOOKUP("CATEGORYNAME","CATEGORIES","CATEGORYID=6") 
Notice the missing FROM clause.

However, there is a grain of salt: After setting up your office suite with the required class path entries, your office suite will handle all HSQL databases, including the embedded ones, with the HSQL2 driver. Embedded databases will be converted on the fly and they will become incompatible with the regular setup. Strangely, I can not even load an embedded and converted HSQL after restarting the office suite with the modified setup. If this happens to you, I would recommend to use this database with a frontend-backend setup (much better by any means).

The attached file is my Base document connected to a Nortwind.mdb by Microsoft.
Copies of Northwind.mdb can be found here: http://www.filewatcher.com/m/Northwind. ... 256-0.html
Edit the mdb file path under menu:Edit>Database>Properties
Attachments
NorthWind.odb
Base document to be connected with an external Northwind.MDB using the ucanaccess JDBC driver
(3.25 KiB) Downloaded 789 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jamadei
Posts: 1
Joined: Mon Sep 21, 2015 4:10 pm

Re: [Tutorial] MS Access and OOo Base

Post by jamadei »

However with ucanaccess 3.0.1, it's possible to dissolve the above mentioned "grain of salt",
-by adding in the classpath just one jar (the ucanload jar under the loader folder of the ucanaccess distribution)
-by setting the set the system variable UCANACCESS_HOME so that it points the UCanAccess-3.x.x-bin folder (i.e. -DUCANACCESS_HOME=/home/UCanAccess-3.0.1-bin)
-by using the net.ucanaccess.jdbc.UcanloadDriver that will dynamically load the hsqldb2.3.x database with a dedicated classloader.

DO NOT ADD THE hsqldb.jar to the global classpath.
See also the upgrades to the above mentioned article http://askubuntu.com/questions/187389/i ... 571#519571
OpenOffice 4.1.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] MS Access and OOo Base

Post by Villeroy »

UCanAccess 3.x with OpenOffice or LibreOffice in screenshots:
I used an Ubuntu Linux system with LibreOffice 5 and UCanAccess 3.0.4 for the screenshots

0) Quick test if Java is enabled. menu:Tools>Macros>Run... should pop up a dialog to select a macro. If you get an error message follow this link: Troubleshooting Open/LibreOffice and Java

1) Download the latest version from sourceforge.net and extract the package to some folder on your system. The screenshot indicates that I extracted the package to /usr/local/lib/UcanAccess. The important file is the selected ucanload.jar in subdirectory "loader".
The UCanAccess directory with subdirectory loader and file ucanload.jar on a Linux system
The UCanAccess directory with subdirectory loader and file ucanload.jar on a Linux system
2) Call menu:Tools>Options in your office suite. On the Mac you call the settings of your office suite from the system panel. In the settings dialog you choose the section which is labeled "Advanced" in LibreOffice and "Java" in OpenOffice :roll:

3) The next screenshot shows the small dialog which pops up when you hit the [Parameters...] button. I added the variable DUCANACCESS_HOME with the path of my UCanAccess folder as in the first screenshot:

Code: Select all

DUCANACCESS_HOME=/usr/local/lib/UCanAccess
In Windows you have to add your UCanAccess path with doubled backlashes like this:

Code: Select all

DUCANACCESS_HOME=C:\\MyPath\\UCanAccess
Parameters dialog called from the office suite's Java options
Parameters dialog called from the office suite's Java options
4) The next screenshot shows the small dialog which pops up when you hit the [Class Path ...] button where I added the archive loader/ucanload.jar in my UCanAccess directory.
Class path dialog called from the office suite's Java options
Class path dialog called from the office suite's Java options
This is the general setup which lets you connect Base documents to *.accdb or *.mdb files.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] MS Access and OOo Base

Post by Villeroy »

How to connect a new Base document to some Access database

After you set up your UCanAccess drivers according to my previous posting, call menu:File>New>Database...
Check option "Connect to existing database" of type "JDBC".
You will have to specify a valid URL which starts with jdbc:ucanaccess:// followed by the path to your Access database (*.mdb or *.accdb).
Another thing you need to specify is net.ucanaccess.jdbc.UcanloadDriver as JDBC driver class.
On http://ucanaccess.sourceforge.net/site.html you find more information about possible path settings and additional parameters.
The screenshot is taken from menu:Edit>Database>Properties... of my existing NorthWind.odb. After you moved/renamed your Access document, this would be the place where you can change the connection URL to reflect the new location.
The [Test Class] button checks if the driver is set up correctly according to the instruction of my previous posting. If the class test runs successfully, the [Test Connection] button tests if the connection between this Base document and its backend file can be established.
Screenshot of my NorthWind.odb connected to Microsoft's NorthWind.mdb example database
Screenshot of my NorthWind.odb connected to Microsoft's NorthWind.mdb example database
You see all the access tables and SELECT queries in the tables section of your Base document. Native SELECT queries made in MS Access are displayed as views.
The relations dialog reflects the relations.
Forms and reports have to be built from scratch as office documents. Embedded forms and reports are always Writer documents. Calc spreadsheets can be used as additional reporting engine: [Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply