![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
The subject says it all, how successful is Base in importing Access Databases?
LO 5.0.x
Unfortunately, MS Access users were spoilt with the 'complete' database
package supporting tables, queries, forms, reports and modules/macros.
Although its 'openness' or performance or compatibility with the rest
of the MS Office suite leave much to be desired (in my humble opinion)
the MSaccess package is a (more or less) complete solution. LibreOffice
on the other hand cannot match the user comfort (yet!).
There is a tool, access2sql that to allows you to generate the sql
needed to build all your 'tables' again (including their data) and
'queries'. But for the 'forms' and 'reports' you will have to start
from scratch in LOBase and, as I say, the user comfort for that is not
as mature as in MSAccess. In LOBase you can also write modules/macros
in Visual Basic. I have no experience of that either, but it does
suggest that VBA macros might with reservation be portable and
modifiable.
In other words, i blieve it is not hopelessly impossible and
I am sure that the experts can add their 'two penn'orth'
Cheers
Harvey
The subject says it all, how successful is Base in importing Access
Databases?LO 5.0.x
--
Ken
Mac OS X 10.8.5
Firefox 44.0
Thunderbird 38.0.1
"My brain is like lightning, a quick flash
and it's gone!"Unfortunately, MS Access users were spoilt with the 'complete' database
package supporting tables, queries, forms, reports and modules/macros.
Although its 'openness' or performance or compatibility with the rest
of the MS Office suite leave much to be desired (in my humble opinion)
the MSaccess package is a (more or less) complete solution. LibreOffice
on the other hand cannot match the user comfort (yet!).There is a tool, access2sql that to allows you to generate the sql
needed to build all your 'tables' again (including their data) and
'queries'.
Thanks Harvey. I'd like to add to this description. Access2sql seen no
release since beginning of 2013 or so and have not seen, say, Linux
packages for this software. In context of Base: access2sql seems to
convert to mysql's and postgresql's SQL, not HSQL's.
For the same task (converting table design and data) there are also
following converters:
- mdbtools (a FOSS pioneer in the topic, implemented in C),
- jackcess (implemented in Java).
Both are maintained and have fairy regular releases. Disclaimer: I am
only user of mdbtools.
But for the 'forms' and 'reports' you will have to start
from scratch in LOBase and, as I say, the user comfort for that is not
as mature as in MSAccess.
Yes, converting forms and report would be doable if interested parties
joined forces. That would be building "on shoulders" of projects I
mentioned above. I don't understand why MS would not agree to publish
docs for MS Access binary formats. Based on my experience I can't stop
believing MS has no access (pun!) to full knowledge about Access
anymore because employee retention has its limits. If you want to make
sure kids of your kids will be able to open your works FOSS is the
most obvious solution.
There's one thing more. MDB Doc http://mdbdoc.sourceforge.net is an
add-in for Microsoft Access 2000-2013 to automate creation of Access
database design's documentation in XHTML format. All objects are
included. Access is required for it but as soon as you have objects'
definition and data exported, you can start porting using whatever
tool you like.
In LOBase you can also write modules/macros
in Visual Basic. I have no experience of that either, but it does
suggest that VBA macros might with reservation be portable and
modifiable.
In terms of APIs Access Basic sits between VBA and VB. Having similar
language is not a challenge, having the same API and implementation
that behaves like original... that's challenge. Here, the behavior can
expose specific implementations coming from MS Windows. VB can call
Windows DLLs natively, can use MS controls; and I remember in Access
97-2000 times I had to do that to achieve acceptable results as simple
as displaying a tree of data. So sometimes old, legacy behavior is
practically inseparable part of Access apps, software that someone
would like to port.
Because of that, in my opinion in terms of forms, reports, scripting
and even macros a *fresh start* sounds better. That would not map a
single legacy or even current system's components to FOSS tools
(calling it "compatibility") but sane, reasonable behavior that could
be reproduced in multiple implementation or at least in one FOSS
offering that can be adopted everywhere. (and we would call it
standards instead)
I'd like to remind none of these concepts (tables, queries, forms,
reports, etc.) are part of the Open Document Format standard or any
draft I know.
Cheers.
Hi:
On GNU / Linux / Ubuntu, and of course in other distributions, there are
to program that you would probe because could help you to export Access
DB to open document:
1) MDBtools (View and export MSAccess db)
2) Kexi of Caligra Suite that say it is able to read MS Access db
I hope this help,
Regards,
Jorge Rodríguez
The subject says it all, how successful is Base in importing Access
Databases?LO 5.0.x
--
Ken
Mac OS X 10.8.5
Firefox 44.0
Thunderbird 38.0.1
"My brain is like lightning, a quick flash
and it's gone!"Unfortunately, MS Access users were spoilt with the 'complete' database
package supporting tables, queries, forms, reports and modules/macros.
Although its 'openness' or performance or compatibility with the rest
of the MS Office suite leave much to be desired (in my humble opinion)
the MSaccess package is a (more or less) complete solution. LibreOffice
on the other hand cannot match the user comfort (yet!).
Thanks, Harvey. Not the answer I was hoping for, but the one I expected. LOL
Personally, I like the idea of the complete database package, as I think it makes it easier for the average person to create something useful for them.
Hi:
On GNU / Linux / Ubuntu, and of course in other distributions, there are
to program that you would probe because could help you to export Access
DB to open document:1) MDBtools (View and export MSAccess db)
2) Kexi of Caligra Suite that say it is able to read MS Access db
Thanks, jorge. I'll have to check with my "conspirator" on how much effort in learning he's willing to do to create a Linux database.
Ken
Well, it is probably unrealistic to expect a 'complete' solution for
the complete porting of a database from a proprietary solution to an
open source one, bearing in mind the number of fields of API issues
needing attention and the understandable resistance from the
proprietary side to supporting a free solution and all that that
entails with risking divulgence of company secrets to a competitor.
What one can hope for from LOBase with time is growing maturity and
user comfort in the area of the forms, reports and macros. My
impression is that it is slow in coming.
What I appreciate very much in LOBase is the back-end support for
diverse databases (e.g. mysql/mariadb etc). This, at least, opens the
door for developing the database backend (schemas, tables, queries,
views) with other tools until the hoped-for user comfort arrives at the
front-end.
Cheers
Harvey
Ken,
One thing about Kexi. I looked at it a few weeks ago and discovered that Kexi has a capability of reading Access database files to some degree. However, it reads and converts the access database into its own internal database. Kexi has no capability to interface to and use an external database server (aka "Back End") such as Mariadb or MySQL, as LO Base does.
I am using LO Base as a database client (aka "Front End") on Linux and connected to my Mariadb database server using a Java "connector" driver. I do this because the LO internal HSQDB has limitations that MySQL and Mariadb do not have. You may consider this if your database(s) are large or complex.
Years ago, I had my data in an access (1.1) database and needed to port my data to MySQL. I managed it by using the option in access to output the database as a comma-separated file, much like a spreadsheet ".csv" file. I then was able to set up MySQL to import this file into its database format. Of course, as some others have noted, the forms and reports needed to be recreated. At this time, OpenOffice (before LO was available) did not have a database client which would work with MySQL, so I chose an open source client called Rekall and had to recreate my data entry forms and reports. It was labor intensive, but needed to be done. Then Rekall went bust and I had to find another client. By then, OpenOffice had Base, which would talk to my MySQL database engine. I did not need to do anything with my MySQL database, but I did have to recreate all of my data entry forms and reports yet again - more labor. So, the bottom line is that any time you change database clients, expect to recreate the data entry forms and reports. There is no standard for them. One big advantage to using an external database such as MySQL or Mariadb, is that they use standard SQL, while the LO Base HSQDB database server uses a non-standard version of SQL. So, using HSQDB could lock you in to it.
My databases are critical to me. All of my database software decisions were based on being able to easily port my data to another client or server and not need to recreate it. Depending on the size of your database, that could take much more time than recreating just the forms and reports.
Hope this helps with your decision. Good luck.
Girvin Herr
After all the years I'm still shocked by the level of ignorance on this user
support mailing list.
https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=25300
Hi:
On GNU / Linux / Ubuntu, and of course in other distributions, there are
to program that you would probe because could help you to export Access
DB to open document:1) MDBtools (View and export MSAccess db)
2) Kexi of Caligra Suite that say it is able to read MS Access db
Thanks, jorge. I'll have to check with my "conspirator" on how much
effort in learning he's willing to do to create a Linux database.Ken
Ken,
One thing about Kexi. I looked at it a few weeks ago and discovered
that Kexi has a capability of reading Access database files to some
degree. However, it reads and converts the access database into its own
internal database. Kexi has no capability to interface to and use an
external database server (aka "Back End") such as Mariadb or MySQL, as
LO Base does.I am using LO Base as a database client (aka "Front End") on Linux and
connected to my Mariadb database server using a Java "connector"
driver. I do this because the LO internal HSQDB has limitations that
MySQL and Mariadb do not have. You may consider this if your
database(s) are large or complex.
Thanks for the above info, Girvin. I appreciate it.
I understand the concept of Front End/Back End, but never have dealt with it. Nor have I ever used MySQL, Mariadb, or others. Access and a bit of dBase is all I've ever used, and in general, even then that's more power than I've ever needed.
Another gentleman and I trying to get a social agency we both volunteer for out of the dark ages of computers. Due to ignorance of computers, and a good bunch of resistance to change, it will be a daunting task.
How much functionality does the Access2Base extension offer, in terms of
making it easier to create queries, forms, reports and modules/macro?
jonathon
<snip>
I understand the concept of Front End/Back End, but never have dealt with it. Nor have I ever used MySQL, Mariadb, or others. Access and a bit of dBase is all I've ever used, and in general, even then that's more power than I've ever needed.
Ken,
Actually, IIRC, Access has both a client and server built in. The user isn't normally aware of it. In my experience with Access 1.1, the server is called the "Jet" server. Today's Access may no longer use the Jet server, but I am sure something like it is still in there somewhere. I must admit the Access bundled concept is addictive. As a newbie to databases back in the 90s, I liked it and it was a shock and a learning experience to wean myself off of it and go with the industry standard forms of client/server architecture and the SQL language. Since then I have learned a lot and find the latter concept very powerful. In your case, if Access and dBase had/have more power than you ever needed and that power is all that you will ever need, then the LO internal HSQLDB engine is probably a good choice for your application.
Now that you mentioned dBase, you may, or may not, be aware that LO has a dBase option. But a limitation to it that I found is that older versions of dBase files are not supported. I have some old dBase 1.x files with dbase programs that will not load into LO, let alone run.
Girvin
Jaroslaw,
What I found back on July 4th (and today) is this snippet from the Kexi FAQ, Q1.2:
http://kexi-project.org/wiki/wikiview/index.php@KexiFAQ.html
"- *Currently you can not "open" (i.e. connect to) databases created outside of Kexi. You can only import the tables and data*. "
That tells me that databases other than Kexi can only be imported (converted to Kexi format and maintained internal to Kexi, not Kexi maintaining the server version). When Kexi states that they support databases like "MySQL, PostgreSQL, xBase and MS SQL/Sybase", I must assume they mean that Kexi can import and convert such databases, not connect to their server and maintain the server versions. There is a big difference.
My Kexi research was precipitated by confirming that AOO 4.1.2 had a broken Report Builder and it looked like it was not going to be fixed anytime soon. This was a show-stopper for me. My Slackware 14.1 Linux comes with Kexi 2.7.4, so I naturally looked at it. I was searching for another Mariadb client option to see what Kexi could do for me, since I was having problems with LO 4.x+. When I discovered the above FAQ statement, it told me I could not use Kexi in my application. I was not going to get "locked in" to another integrated application like MS Access. It could be that at some time in the future Kexi will have the connection capability that I require and I may revisit it.
In case you're interested, I ended up using AOO 4.1.2 for documents, which doesn't have the problems I am having with LO 4+, and LO 5.0.6 for my databases, which has a working Report Builder (Kudos to the devs for fixing the lines in reports not WYSIWYG problem).
Girvin
*Hi all*
*Re: The LO Base discussion* - just my "Penny's Worth"!!!
After a 5+ years effort to use LO Base I have given up completely!! It is *NOT* usable at all in it's "out of the box" set-up, ie with the HSQL engine. It is totally unstable, crashes frequently AND - *MORE IMPORTANT* - trashes all you data when it goes down!!! This seems to be something to do with the compressing that the program does as you exit the program.
The only way I got any (sort of results) was by using MySQL as the backend but it took a couple of months to get it working and after a few months even that crashed on me. I recently had to re-build my computer after a hardware failure and my OpSys upgraded to 64bit and since then I can not even get the MySQL linking in LO Base to even start!!
So if you are happy to keep lots and lots of backups, and spend lots and lots of time re-building everything at almost monthly intervals - and by re-building I mean the Database Tables, redesign all your Forms and set-up all your Queries and Reports from scratch - then go with it, otherwise give it a miss.
I have searched for a suitable replacement. I run Linux here, and have looked at many programs. In desperation I was keeping things going using LO Calc and this is not easy. The rest of LO is fine and I use it a lot but base is the (VERY) weak link in the chain. This not only my opinion - if you dig around you will find many reports of this.
Early this month I gave *Kexi* a second try and I'm finding it very workable. It is still a bit "young" but does everything I want for my Database and I'm very comfortable with it. It is "All-in-one" using SQLite as the engine and the only major problem I have come across is the program crashing during Form design but frequent (say every 5min saves) seems to get round this. Doing actual work with the finished set-up has so far proved Rock Solid. It's worth a look at and stay with it long enough to learn its rather different layout and way of doing thins!!
Have fun - I am and very happy to be away from LO Base.
All the best
IanW
Pretoria RSA
Ken,
One thing about Kexi. I looked at it a few weeks ago and discovered that
Kexi has a capability of reading Access database files to some degree.
However, it reads and converts the access database into its own internal
database. Kexi has no capability to interface to and use an external
database server (aka "Back End") such as Mariadb or MySQL, as LO Base does.Hi,
If you mean ability to connect without generating any metadata as in
pure frontends, and being a SQL frontend, then yes. The internal
database is created on first use, however it still can be the server
database possibly running on the same server like the original one,
it's just not the same database.I recommend https://en.wikipedia.org/wiki/Kexi#Features - 2.x supports
MySQL, PostgreSQL, xBase and MS SQL/Sybase backends (it does so
without limiting itself to capabilities of ODBC/JDBC).KEXI 3 would be able to do open databases "in place", just not 3.0.
Still, it would not be called a db frontend however transmitting
unchecked/raw SQL strings back and forth; it would be still more a
different type of software: an integrated app creator / environment,
so a slightly more high-level tool.Jaroslaw,
What I found back on July 4th (and today) is this snippet from the Kexi FAQ,
Q1.2:http://kexi-project.org/wiki/wikiview/index.php@KexiFAQ.html
"- Currently you can not "open" (i.e. connect to) databases created outside
of Kexi. You can only import the tables and data. "That tells me that databases other than Kexi can only be imported (converted
to Kexi format and maintained internal to Kexi, not Kexi maintaining the
server version). When Kexi states that they support databases like "MySQL,
PostgreSQL, xBase and MS SQL/Sybase", I must assume they mean that Kexi can
import and convert such databases, not connect to their server and maintain
the server versions. There is a big difference.
You are right, there's a difference. But there is no Kexi format.
Databases are imported back to any place you have access to, that can
be very the same server you already use.
To possibly clarify this a bit since we touche the topic of this
thread - the "compatibility" - I'd like to add:
- Just connecting to a database and not emplying any meta data would
mean forms, reports, scripts, etcetera, can't be saved. This is
because no server we're talking about has support for "CREATE FORM",
"CREATE REPORT" command, a standardised one or not. Kexi does, that's
the reason for having own extensions. Just like Base but please refer
to the next point.
- Kexi needs no additional database or XML files to store forms,
reports, etc. Commands like "CREATE FORM" are translated to native SQL
commands of your database of choice, on the fly.
- Based on the above, all data stored and raw structures are
accessible without exporting to other MySQL/PostgreSQL/MS SQL tools
and APIs. There's no what would be called a Kexi format, there's the
meta data. Of course forms are not visible to, say, MySQL because
MySQL has no form capabilities and is not designed for that. (Neither
Oracle has, Oracle Forms has, as an extension)
For example if we crafted a form or report running on top of table
"Customers (ID, name, surname)" and add column (age) to it in MySQL
Admin tool, change _won't_ be visible in the form, no matter what tool
we're using.
These above design choices were justified by these facts and that back
in 2004 some backends such SQLite 2 had no way to fully discover the
schema schema. As said before KEXI 3 would have the discussed behavior
a bit more liberal, benefiting from advancements in database backends.
Cheers
<snip>
I understand the concept of Front End/Back End, but never have dealt
with it. Nor have I ever used MySQL, Mariadb, or others. Access and
a bit of dBase is all I've ever used, and in general, even then that's
more power than I've ever needed.Ken,
Actually, IIRC, Access has both a client and server built in. The user
isn't normally aware of it. In my experience with Access 1.1, the
server is called the "Jet" server. Today's Access may no longer use the
Jet server, but I am sure something like it is still in there
somewhere. I must admit the Access bundled concept is addictive. As a
newbie to databases back in the 90s, I liked it and it was a shock and a
learning experience to wean myself off of it and go with the industry
standard forms of client/server architecture and the SQL language.
You've just mentioned the big "roadblock" for the average person to make use of databases. They are too complex to learn and use for most people. That's where the "all-in-one" solution is a better answer. It's a lot easier for the average user to wrap their heads around and then use it.
What happens? The average person fills up spreadsheet after spreadsheet of flat file data. My brother-in-law is a perfect example. Years ago, he was putting their music collection into a spreadsheet. When the sheet got to large for RAM and his computer crashed, he started splitting into multiple spreadsheets. But that made their goal of printing their entire collections of songs, alphabetized, impossible. I took the spreadsheets and combined them into Access 97, created an input form and reports, and everyone was happy.
Even getting people to use a flat file database like Database Oasis would be better than a spreadsheet.
Since then I have learned a lot and find the latter concept very
powerful. In your case, if Access and dBase had/have more power than
you ever needed and that power is all that you will ever need, then the
LO internal HSQLDB engine is probably a good choice for your application.
It may be, if this was a single user issue. But we need to be compatible with MS Office without having enough Windows systems, where as I can lay my hands on 3 other Linux systems that are being unused.
Now that you mentioned dBase, you may, or may not, be aware that LO has
a dBase option. But a limitation to it that I found is that older
versions of dBase files are not supported. I have some old dBase 1.x
files with dbase programs that will not load into LO, let alone run.
I didn't know this, but must admit dBase is probably not the best answer.
Hi all:
For collections, there are two programs on GNU / Linux Ubuntu and
usually on others Distributions:
1) GCStar
2) Tellico
Both have multiple options to import and export information. By the way,
my little knowledge can't permit to me to share with you what kind of
data base use them or if when have a lot data, they will come slowly,
but this are my 1 cent to this topic.
Regards,
Jorge Rodríguez
In defence of LibreOffice Base.
Unlike Ian I have had very good results with Base.
Over the last 8 years I have used Base as a a "front end" to a MySQL database in 5 different countries for different projects.
One database was for the New Ireland (PNG) Provincial Government Old Age & Disabled Pension fund. 170,000 records updated and maintained by two dedicated data entry people, but accessed by approximately 35 other users. LibreOffice Base on Windows 10, 8 and XP PC, on Ubuntu 12.04 PC, on Macintosh OS X laptop. All connecting via ethernet and wireless to a Ubuntu 12.04 Server.
Another similar situation with Ministry of Tourism, Tonga collecting data on visitors to Tonga and also used for tourism venture's licensing and administration and conventions. 25 users connecting LibreOffice Base to MySQL on FreeBSD server, with Windows and Ubuntu PCs.
Full administration for 5 divisions in Sanma Provincial Council, Santo, Vanuatu. Including HR, Accounts, Youth, Disabilities, Planning, etc. 75 staff members, plus external users, with LibreOffice Base connecting to MySQL on Ubuntu 14.04 server from Windows (10, 8.1, XP), Ubuntu 12.04 and Macintosh PCs.
Now I used Access (Base not available) at Kokopo Business College for student records. Created a database for College Library with Base on Ubuntu PCs connecting to central MySQL database via Fibre Optics.
Had real trouble trying to set this up with Access for multiple users (25 teachers, 10 admin staff).
When I got LibreOffice Base I had similar problems with using the HSQLDB engine.
I found that connecting Base to MySQL was very easy and I had real problems trying to connect Access.
I could also use Base on Linux machines and update all my colleagues to a consistent operating system.
You can not export Access forms, reports, macros, etc to use with Base but you can export all the data from Access and import it into Base very easily.
Sorry to keep on!!! I have had good results with LibreOffice Base connecting to an external database. I also make extensive use of Macros with Base.
Just in case you are wondering -- I am retired and about to have my 75th birthday so not just straight out of Uni.!!!
Thanks for listening
Tony Bray
tonybsa@mac.com
MacBook Pro 15 inch Mid 2009
2.8 GHz Intel Core 2 Duo, 4 GB RAM,
Mac OS X 10.11.6
LibreOffice 5.0.6
Scribus 1.4.4
Libre Project 1.5.19
Parallels Desktop 11.02
Ubuntu 14.04
LibreOffice 4.4.4
MySQL, PHP, Apache
I will second that defense of Base. Tony's experience is much broader than mine, so counts for more points. I only maintain two inventory databases with several tables each on a single Slackware 14.1 Linux machine. Many of these inventory tables have more than 1000 records and growing. I am connecting LO Base to a mariadb local server with the mysql-connector-java connector and, unlike Ian, in the over one decade of use I have never had a data loss or a crash of any sort that took my data or the Base forms or reports. It is very stable, which is good, because I rely on these databases very much. I consider them critical. The Report Builder is another story, but it has never corrupted my data, and it is improving with time. Long live Base's connection options.
BTW, Tony. I am also retired and only 2 years younger than you, with a birthday coming up soon.
Girvin Herr
Just to be clear, I also support these eulogies to LOBase, and look
forward to a time when the Base's user comfort improves. I, too am
running Base (on OpenSuse Linux 42.1) with a Mariadb backend but only
with a very small (one table!) database. Years ago MSAccess spoiled me
for features and user comfort, AS LONG I WAS THE ONLY USER!. But the
databases I created with it were quite large and complex (one of them
had 100000 records in one table and 40 tables, for a demanding
engineering project). At that time I also created a simple LOBase
application (on windows 2000) and MySQL (on OpenSuse Linux) for storing
and retrieving scanned papers. That db worked quite well, but speed was
not its best feature. Now, (in retirement, approaching 70!) I am still
on the lookout for applications that would benefit from good database
support whereby LOBase/mariadb would always (still) be my configuration
of choice.
Cheers
Harvey