looking for help on Base

users
#1

the site http://www.libreoffice.org/features/base/ states
"... provides native-support drivers for some of the most-widely
employed multi-user database engines: MySQL, Adabas D, MS Access..."

When I open Base if I choose to "connect to an existing database" the
list of available drivers doesn't include MS Access (or JET), it
includes only:
JDBC
JDBC Oracle
Adabas D
Foglio elettronico
dBASE
Testo
MySQL
ODBC

How do you activate the MS Access native-support driver?
Is a SQLite native-support driver available?
Many thanks, Roberto Scotti

#2

Hi Roberto,

Roberto Scotti wrote (04-08-11 10:19)

When I open Base if I choose to "connect to an existing database" the
list of available drivers doesn't include MS Access (or JET), it
includes only:
...

That connection is Windows only.
Does this explain what you see on your computer?

Regards,

#3

Hi :slight_smile:
It's probably better to stick with MS Access tbh. Base is not being actively
developed and there are no plans to recruit devs for it. Hopefully some devs
might stumble into the project at some point in the future and start working on
the bugs.
Regards from
Tom :slight_smile:

#4

Hi Roberto,

Roberto Scotti schrieb:

the site http://www.libreoffice.org/features/base/ states
"... provides native-support drivers for some of the most-widely
employed multi-user database engines: MySQL, Adabas D, MS Access..."

When I open Base if I choose to "connect to an existing database" the
list of available drivers doesn't include MS Access (or JET), it
includes only:
JDBC
JDBC Oracle
Adabas D
Foglio elettronico
dBASE
Testo
MySQL
ODBC

You can try to use ODBC, http://wiki.services.openoffice.org/wiki/Connecting_to_Microsoft_Access. But you will get mostly read access and you cannot use reports or forms from Access.

Why do you need the connection?

Kind regards
Regina

#5

Hi Tom,

I've been a Base user for 7 or 8 years. As soon as Base came out I switched to it. I had first used a DOS based database, then Access, then Paradox, and then Base. For my needs, Base works very well. I used the guide from OpenOffice, Getting Started With Base to get things setup The present version can be found here, as well as other helps: http://wiki.services.openoffice.org/wiki/Database
There are several small bugs in Base that are more of an annoyance than anything else. It is super fast (now that the Linux Java workaround has been found), and it is much easier to share the data between my computers than it was with Paradox. While they might not have developers working on it, there isn't anything I need to have changed. The mail merge with writer works perfectly. I've never lost any data. So even if there are not any developers working on it, I feel it is a worthy program/application. Anytime I've had an issue of needing to know how to do something, I've always been able to find an answer with on-line searches.

#6

Yes, it may be. I am working on Ububntu based systems.
Is it possible to install MS-Access and/or SQLite native-drivers in
Linux?
Many thanks, Roberto Scotti

PS - I added your address in CC to be shure you would get the message,
if thi causes doubling your messages please let me know.

#7

Hi Roberto,

Access will not run natively in Linux. I run Ubuntu 11.04 presently. I was a windows user when I switched to Base. One reason for switching was I knew someday I wanted to migrate to Linux. It was very easy since OpenOffice worked in both platforms. I run Quicken and Dreamweaver through Wine on Ubuntu. You may need to move up to Crossover Office, but it would be likely some version of Access would work. I have not researched this though. In general, some versions of Microsoft Office do work with Crossover Office.

Don

#8

Roberto,

It is possible to get SQLite to work on Ubuntu 10.04 LTS. These are my notes on how I set it up on my machine...

Most of software in Ubuntu 10.04 can not directly access SQLite Databases and it is necessary to install unixODBC plus the SQLite Driver.

Installation of the unixODBC package (http://www.unixodbc.org/) can be easily done from the Ubuntu Software Centre. The website provides a link to the appropriate driver maintained by Carl Weirner.

Installation of the SQLite ODBC Driver
(http://www.ch-werner.de/sqliteodbc/) is a little more involved.

The website provides a link to the appropriate linux download
(http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.88.tar.gz).
Download this file and extract the directory to a folder.

Open a terminal within the sqliteodbc-0.88 directory and run the following commands.

    ./configure
    sudo make install

Check that the drivers have been registered in /etc/odbcinst.ini. The configuration settings required for this file are outlined in the ../sqliteodbc-0.88/README file. My installation did not require any changes as the odbcinst.ini file was correctly configured.

In order to have Ubuntu see you odbc database change the ~/.odbc.ini file so it includes the following data.

    [mysqlitedb]
    Description=My SQLite test database
    Driver=SQLite3
    Database=/home/username/databasename
    # optional lock timeout in milliseconds
    Timeout=2000

You should now be able to see the database in LibreOffice Base or other packages that can use ODBC drivers.

#9

Hello Don.
I have already succeded in runnin Access in some MS-widows emulation.
I can also wiew and (to some extent) export info from MDB files trough
"GNOME MDB Viewer is a grapical interface to MDB Tools. It lets you view
and export data and schema from MDB files produced by MS Access
97/2000/XP/2003."

What I need now is what is claimed on the LibreOffice-Base
http://www.libreoffice.org/features/base/ which states that it
"... provides native-support drivers for some of the most-widely
employed multi-user database engines: MySQL, Adabas D, MS Access..."

Many thanks, rs
Nuoro Forestry School
Università di Sassari
http://nuoro-forestry-school.blogspot.com/

#10

Hi Guys,

Another option if you are migrating from windows is to establish a VirtualBox that runs Windows and install Office on this. You can also do this on VMWare, although I find VirtualBox a lot faster.

The Windows Platform on the VM have samba shares mapped as network drives and they are blissfully unaware that the data being accessed are on my Ubuntu filesystem. Data can be readily manipulated in a Microsoft Windows package and Linux-based package at practically the same time.

VMWare also provides tools to bundle your windows box into virtual machine, although I found this carried over too much 'baggage'. I just created an empty VM using VirtualBox and reinstalled Windows and Office.

#11

Maybe this will help:

http://packages.ubuntu.com/natty/i386/database/libreoffice-base
<quote>
You can extend the functionality of LibreOffice Base by installing these
packages:

* unixodbc: ODBC database support
* libmyodbc | odbc-postgresql | libsqliteodbc | tdsodbc | mdbtools: ODBC
   drivers for:
   - MySQL
   - PostgreSQL
   - SQLite
   - MS SQL / Sybase SQL
   - *.mdb (JET / MS Access)
* libmysql-java | libpg-java | libjtds-java: JDBC Drivers
   for:
   - MySQL
   - PostgreSQL
   - MS SQL Server and Sybase

Check to see if you have those installed.

#12

Hi :slight_smile:
It is not a good time to migrate to Base. Hopefully there are other front-ends
that could be used instead? Base has no devs working on it so bug-reports are
not getting fixed and there are no plans to attract devs to work on Base anytime
soon.

Regards from
Tom :slight_smile:

#13

Hi :slight_smile:
It's good to hear some success with Base. I think it might help to have a lot
of trivial issues amongst the bug-reports to help potential new devs learn their
way into the code. The key word there is "might".

The documentation team would appreciate any help with consolidating various
guides they have found and perhaps others into something like the state of the
guides for the other apps but it's a major challenge because Base is
unpredictably quirky between one release and another.

Regards from
Tom :slight_smile:

#14

Yes, at least I tried to install mdbtools, the stand-alone viewer is
working:
"gmdb2 0.6pre1
GNOME MDB Viewer is a grapical interface to MDB Tools. It lets you view
and export data and schema from MDB files produced by MS Access
97/2000/XP/2003."
What is needed next to have it available in LbreOffice-Base?
Many thanks, rs

#15

It appears to still have issues. I'm getting this error:

[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

I'll post back when I get it figured out.

#16

It appears to still have issues. I'm getting this error:

[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

I'll post back when I get it figured out.

Since many years I have never read some write-up where somebody managed to access mdb under Linux nor Mac.
There used to be a read-only driver from an open source project back in 2005/06. After many months of hard work they gave up further development when their write access revealed some tendency to destroy the connected database. Read-only would be just fine for most people, but I can not even find that version anymore.
The Windows drivers are made by Microsoft. Even if they could compile the same thing for Linux easily, they would never do so.
I use to recommend the excellent tools provided by MS Access to export data, query definitions, doc reports, macros and all sorts of meta information.
The mdb tools are good enough to export usable CSV. Then you can copy tables from a CSV connected Base document into a database connected Base document. Like always, mind the locale setting in respect to the decimal separator and date formats used in the csv.
Greetings,
Andreas

#17

I finally managed to get Base to connect to the Access file (old Office
97 Northwind example .mdb) and even list the tables in the file. However
when I attempt to view the table LO (and OOo) crashes with no apparent
crash info (i.e., LO & OOo simply dismiss). To do that I use a different
driver: /usr/lib/libmdbodbc.so.0 instead of /usr/lib/libmdb.so.1. So
it's closer than before...

For Roberto: my odbcints.ini entry looks like this:
[mdbtools]
Description = MSAccess Driver
Driver = /usr/lib/libmdbodbc.so.0
Driver64 =
Setup = /usr/lib/libmdbodbc.so.0
Setup64 =
UsageCount = 1

and my odbc.ini entry;
$ cat /etc/odbc.ini
[Northwind]
Description = Northwind Sample Database
Driver = /usr/lib/libmdbodbc.so.0
Database = /home/gg/Documents/MyDocuments/Nwind.mdb

#18

Am 06.08.2011 19:25, NoOp wrote:

I finally managed to get Base to connect to the Access file (old Office
97 Northwind example .mdb) and even list the tables in the file.

Wow. That's great news.

However
when I attempt to view the table LO (and OOo) crashes with no apparent
crash info (i.e., LO & OOo simply dismiss).

I'm not surprised. *.mdb is a file format from outer space.

#19

>>
>> It appears to still have issues. I'm getting this error:
>>
>>
>> [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
>>
>> I'll post back when I get it figured out.

>
> Since many years I have never read some write-up where somebody managed
> to access mdb under Linux nor Mac.
> There used to be a read-only driver from an open source project back in
> 2005/06. After many months of hard work they gave up further development
> when their write access revealed some tendency to destroy the connected
> database. Read-only would be just fine for most people, but I can not
> even find that version anymore.
> The Windows drivers are made by Microsoft. Even if they could compile
> the same thing for Linux easily, they would never do so.
> I use to recommend the excellent tools provided by MS Access to export
> data, query definitions, doc reports, macros and all sorts of meta
> information.
> The mdb tools are good enough to export usable CSV. Then you can copy
> tables from a CSV connected Base document into a database connected Base
> document. Like always, mind the locale setting in respect to the decimal
> separator and date formats used in the csv.
> Greetings,
> Andreas
>
>

I finally managed to get Base to connect to the Access file (old Office
97 Northwind example .mdb) and even list the tables in the file. However
when I attempt to view the table LO (and OOo) crashes with no apparent
crash info (i.e., LO & OOo simply dismiss). To do that I use a different
driver: /usr/lib/libmdbodbc.so.0 instead of /usr/lib/libmdb.so.1. So
it's closer than before...

For Roberto: my odbcints.ini entry looks like this:
[mdbtools]
Description = MSAccess Driver
Driver = /usr/lib/libmdbodbc.so.0
Driver64 =
Setup = /usr/lib/libmdbodbc.so.0
Setup64 =
UsageCount = 1

and my odbc.ini entry;
$ cat /etc/odbc.ini
[Northwind]
Description = Northwind Sample Database
Driver = /usr/lib/libmdbodbc.so.0
Database = /home/gg/Documents/MyDocuments/Nwind.mdb

Hello, thanks for posting back.
I am not interested in ODBC connections.
Since the site http://www.libreoffice.org/features/base/ declares
that Base
"... provides native-support drivers for some of the most-widely
employed multi-user database engines: MySQL, Adabas D, MS Access..."
I expect to have a direct connection as "MDB viewer" has.
In my /usr/lib/ folder I do not have any libmbdodbc file

rs@rs-XPS-U:~$ ls /usr/lib/libmdb*
               /usr/lib/libmdb.so.1 /usr/lib/libmdbsql.so.1
               /usr/lib/libmdb.so.1.0.0 /usr/lib/libmdbsql.so.1.0.0

I downloaded Nwind.mdb form
http://download.microsoft.com/download/access97/Nwind/97/WIN98/EN-US/Nwind.exe
and, using "MDB viewer" [GMONE MDB 0.5.99.0.6pre1.0.20051109-7ubuntu1
(mdbtools-gmdb)] I can see the data in the tables, see the list of
other objects (views, forms, reports, and macros) export the schema and
the tables.

I would expect to have at least as much fom Base or is ti not possible?
Many thanks again, roberto

#20

...

I finally managed to get Base to connect to the Access file (old Office
97 Northwind example .mdb) and even list the tables in the file. However
when I attempt to view the table LO (and OOo) crashes with no apparent
crash info (i.e., LO & OOo simply dismiss). To do that I use a different
driver: /usr/lib/libmdbodbc.so.0 instead of /usr/lib/libmdb.so.1. So
it's closer than before...

For Roberto: my odbcints.ini entry looks like this:
[mdbtools]
Description = MSAccess Driver
Driver = /usr/lib/libmdbodbc.so.0
Driver64 =
Setup = /usr/lib/libmdbodbc.so.0
Setup64 =
UsageCount = 1

and my odbc.ini entry;
$ cat /etc/odbc.ini
[Northwind]
Description = Northwind Sample Database
Driver = /usr/lib/libmdbodbc.so.0
Database = /home/gg/Documents/MyDocuments/Nwind.mdb

Hello, thanks for posting back.
I am not interested in ODBC connections.
Since the site http://www.libreoffice.org/features/base/ declares
that Base
"... provides native-support drivers for some of the most-widely
employed multi-user database engines: MySQL, Adabas D, MS Access..."
I expect to have a direct connection as "MDB viewer" has.
In my /usr/lib/ folder I do not have any libmbdodbc file

rs@rs-XPS-U:~$ ls /usr/lib/libmdb*
               /usr/lib/libmdb.so.1 /usr/lib/libmdbsql.so.1
               /usr/lib/libmdb.so.1.0.0 /usr/lib/libmdbsql.so.1.0.0

I downloaded Nwind.mdb form
http://download.microsoft.com/download/access97/Nwind/97/WIN98/EN-US/Nwind.exe
and, using "MDB viewer" [GMONE MDB 0.5.99.0.6pre1.0.20051109-7ubuntu1
(mdbtools-gmdb)] I can see the data in the tables, see the list of
other objects (views, forms, reports, and macros) export the schema and
the tables.

I would expect to have at least as much fom Base or is ti not possible?
Many thanks again, roberto

It's possible in the Windows 3.3.3 Final version. Doesn't work in the
Windows 3.4.2 version. I tested both in WinXP on separate virtual
machines & both have options to connect to MS Access & MS Access 2007.
The 3.3.3 install
LibreOffice 3.3.3
OOO330m19 (Build:301)
tag libreoffice-3.3.3.1
connects & shows the tables to the nwind.mdb. The 3.4.2 (Final)
gives an SQL error:

A connection for the following URL was requested
"sdbc:ado:access:PROVIDER=Microsoft.Jet.
OLEDB.4.0;DATA
SOURCE=C:\tempdir\Nwind.mdb".

Just to be sure, I'm unstalling 3.4.2 & reinstalling 3.3.3 on that
virtual machine to test in the same environment.

As for linux... I'm afraid we're stuck with trying odbc. Note:
libmbdodbc is a individual package (sudo apt-get install libmbdodbc).