[Solved] Newbie with a odb question

Discuss the database features
Post Reply
michaelmaclauchlan
Posts: 3
Joined: Tue Jan 02, 2018 5:12 pm

[Solved] Newbie with a odb question

Post by michaelmaclauchlan »

Hi all, I'm a long term MS Access user and thought I'd try OpenOffice Base.

I wrote an MS Access DB 2007-2010 file format (accdb) and converted it to ODB. The conversion seemed to go well until I got this error.

"The connection to the data source "new odb file" could not be established The connection could not be created. May be the necessary data provider is not installed.

Both the original MS Access file and the converted ODB are on my desktop. The MS Access file functions well.

Do I need to install a 'Data provider' or other software?

Thanks for any help

Michael
Denver, CO :D
Last edited by michaelmaclauchlan on Wed Jan 03, 2018 3:54 pm, edited 1 time in total.
OpenOffice 4.1.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie with a odb question

Post by Villeroy »

You did not convert anything. You connected a Base document to an external database (the accdb). See status bar of the Base document.
Same thing when you connect an Access document to some external database without having the required database driver installed.
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
michaelmaclauchlan
Posts: 3
Joined: Tue Jan 02, 2018 5:12 pm

Re: Newbie with a odb question

Post by michaelmaclauchlan »

Thank you.

So how do I convert the accdb to ODB so that I can enter data and make changes etc in ODB ?
OpenOffice 4.1.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie with a odb question

Post by Villeroy »

The general concept of databases allows you to use the database backend (the actual data storage) with different frontends.
Access and Base are frontends to utilize multiple types of backends. Access comes with the JET database backend that can be embedded in an Access document. Base comes with a free HyperSQL backend that can be embedded in a Base document. But both programs are most useful when you connect them to already existing 3rd-party databases.

If you really want to leave behind MS Access in favour of Base then you have to learn a lot of technical things about the new tool, install either the MS drivers ("data providers" in MS speak) or install the "UCanAccess" Java drivers. Then you connect one database document with the accdb and copy over tables to the new Base database.

This forum has a section for tutorials, examples and links regarding the Base component.
[Tutorial] MS Access and OOo Base

Keep in mind that the whole Base component is no more than 20 MB of software, including drivers for PostgreSQL, MySQL, HyperSQL. This tiny collection of connectivty tools is far, far away from a commercial development suite like MS Access.
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
michaelmaclauchlan
Posts: 3
Joined: Tue Jan 02, 2018 5:12 pm

Re: Newbie with a odb question

Post by michaelmaclauchlan »

OK thx! Perhaps easier to re-write my MS Access db in OBD (it's a brand new small DB so easy to do)

Michael
OpenOffice 4.1.4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie with a odb question

Post by Villeroy »

Yes. I use to recommend a newly designed database with tables, indexes and relations and then copy old data into the new database. In order to copy old data to the new database, you need to install something that allows Base to read your old database or you need MS Access to export raw data into Excel sheets or plain text or dBase.
Another thing you should know before using Base: The Base component has not been improved in 8 years. If anybody works on the Base code, then because some changes in the core application raised a new bug in the Base component. It is more or less the same application as in 2006 with minor improvements until 2010. The most trivial method how the actual HyperSQL backend is wrapped into a zip archive (the *.odb document) makes your database prone to complete data loss, for instance when you shut down the computer or close the laptop lid while the document is saving. In other words, you need to do regular backups.
Once you have your embedded HSQLDB working, you can split the backend database from the containing database frontend. I use split HSQLDBs since many years with no instabilities. The separate database remains intact no matter what happens to the frontend and you can use a modern HSQL database driver instead of the 10 year old driver that is shipped with the office suite.
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
LilZebra
Posts: 43
Joined: Sun Mar 06, 2016 4:27 pm
Location: Canada

Re: Newbie with a odb question

Post by LilZebra »

Villeroy, it sure would be nice if some programmers were to update LO and AO Base.

By viewing tutorials on YouTube of how MS Access 2013 and later works, you can do such wonderful (easy) stuff like, in Table view, put a field before another field, just by drag & drop. This cannot be done in Base currenlty. A workaround for us is to set up a Query and put that field before the other, like so:
https://youtu.be/VyZ234TaxcI?t=101

Easily allowing a Hyperlink field so one can easily add an E-mail or web URL field to a record, like so:
https://youtu.be/oCq03BVtlH8?list=PL34E ... 6CBA0&t=33

The "split" database would become the default type created rather than the buggy "embedded" Base file.

Starting AutoNumbers with '1' instead of at '0' in Base. Of course I don't make anything useful as '0', just use it for testing purposes.

Another thing that MS Access can do is in its Report generator.
LibreOffice 6.1.3.2 (Linux Fedora 29 on x86_64) with 'Notebookbar'
OpenOffice.org since 2002-12. LibreOffice user since 2013-current.
HSQLDB 2.5.0
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie with a odb question

Post by Villeroy »

LilZebra wrote:The "split" database would become the default type created rather than the buggy "embedded" Base file.
Well, it used to be the only type of database in version 1 of OpenOffice.org but the crowd cried for something in one document file like MS Access and version 2 was the beginning of the Base document (odb).

Base is still most useful and easy to use when you just want to reuse an already existing database with your ODF documents (spreadsheets, pivot tables, text reports, serial letters, label printing etc). The external database can also be a spreadsheet or a set of text files which adds some limited query capabilities to these pseudo databases.

Base as a database development tool is a desaster. All this is not a big issue when you learned a little bit of SQL, however most Access "developers" are completely SQL illiterate. Implementing something which is closer to MS Access would take many thousands of development hours. I design all my databases in a text editor with some predefined SQL snippets for related tables and there are plenty of professional development tools that are not speicific to one particular type of database, e.g. SQL Workbench. Feel free to create a Writer template with some document variables and auto-text snippets. This could be a usable database design tool avoiding some pitfalls and typos. Then copy the text into Base's SQL box and the result could be a complete many-to-many relation with 3 tables. Of course you should understand the SQL code.
In the end, all database design is development work. If you know only a little bit of SQL and the theory behind SQL you don't need MS Access.
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