Importing a MS Access database into Base

Discuss the database features
MarcvanWegberg
Posts: 2
Joined: Sat Oct 17, 2009 12:56 pm

Re: Importing a MS Access database into Base

Post by MarcvanWegberg »

Dear all,

thanks for replies. Concerning the data conversion route MS Access > Spreadsheet > Base:
Villeroy wrote: A spreadsheet does not care about the structure of the data. It takes any text, date and number at any position.
For me this does not seem to work: my MS Access databases contain several memo fields of quite large size (several Kbs in cases), and this does not seem to come across in a spreadsheet. I wonder if spreadsheets have maximum cell sizes? This is why I preferred the direct conversion route MS Access > Base, using Drew Jensen's suggestion. Thanks again,

Marc
OpenOffice 3 on MacOSX 10.6
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

Calc supports floating point numbers (in various formattings) and text (32,000 chars, not sure) as the only datatypes. No binary data (arbitrary file contents, pictures).
Create a simple query without the binary contents and drag this into the sheet. It should help you to find the differences in the 2 row sets.
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
aleczuke
Posts: 1
Joined: Tue Jan 26, 2010 7:15 pm

Re: Importing a MS Access database into Base

Post by aleczuke »

Thank you DrewJensen for showing how you can create a new database CONNECTED to an access database - and then create a new BASE then drag and drop tables to copy...

On a hunch, I simply opened my access database (since I'm on Windows) and created a new BASE database, and dragged and dropped the access tables over to the new BASE tables icon, i.e. removing the need to connect to the access database.
Open Office 3.1.1
Windows Vista Unfortuneately
DWFII
Posts: 48
Joined: Sat Jan 09, 2010 3:30 am

Re: Importing a MS Access database into Base

Post by DWFII »

From a newbie to Base and not very good with databases in general...a related question:

I have a program that can supposedly convert an MS Access database to MySQL but I don't understand MySQL. The program wants to me to identify a MySQL server or do a MySQL dump of the MS Access database.

I don't understand the terminology.

I don't understand how to set up or identify a MySQL server that I can use.

I don't know what a MySQL dump is or how it will affect the database structure. Will OO be able to understand and use the information in the MySQL dump?

My MS Access database is not complicated--just a listing of boots sold, to whom, address, etc., and the payment amount. but it does contain several forms and queries.

I just migrated to Win7 pro x64, my access software is circa 1997, I think and I'd like to dump it and use OO exclusively.
Open Office 3.3 on Win7 Pro x64
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

The vast majority of databases are served by a server program [including the one which stores and delivers our communicate here. Just look at the bottom-right corner]

A web-server is a program that waits for HTML requests, a mail server is a program that waits for mail requests, a database server is a program waiting for row set requests. The database server handles simultanious access of more than one requestor at a time, even if two users want to edit the same record in a database.
You store some MySQL files in some dedicated directory, including a set of tables describing who is allowed to do what (log-in, write permissions, read permissions, delete permissions, insert permissions).
Before you post here you fill out a log-in form in your browser and get write permission for your own postings and insert permissions (new postings) for most of the forums.
[DATA]<--->[Server]<--->|possibly some network|<--->[Driver]<---->[Base]--->[office documents]
Recent versions of OOo Base are shipped with a built-in driver to communicate properly with MySQL servers.
In Base you configure the connection to some MySQL server including your log-in data. This configuration is stored in a local Base document. The Base document shows the tables and views of the database and depending on your permissions you may be able to view or edit the tables. Within the Base document you can add queries to get meaningful recombinations of row sets, you can add reports to dump row sets into reports(embedded Writer docs), you can add forms for manual entry into the database (also embedded Writer docs). If the database is a registered one (Tools>Options>Databases..) you see all tables and your queries in the beamer window (Calc/Writer F4) and you can pull them into documents as data pool for spreadsheet calculations, serial letters, labels, business cards, bibliographic references...

Rule #1: Base never "opens" any data files. It always connects to some pool of foreign data. Even the special type of database you can create from scratch in a single file start working after the raw data have been extracted into a temporary directory and connected through a server software running in the background under the control of OpenOffice.org.
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
DWFII
Posts: 48
Joined: Sat Jan 09, 2010 3:30 am

Re: Importing a MS Access database into Base

Post by DWFII »

Thank you for the response.

I got most of that. But it raises another question...
Since you didn't mention anything about MySQL dumps...I guess i need to look at a local MySql server. How do I set one up on my computer?

And here's another question that has really confused me:

I opened the latest version of OO and Database this morning and Base offered to open MS Access datadases (one a generic "MS Access" option and anoterh MS Access 2007 option).

So...as a test...I opened one of my .mdb's and it appeared to open fine. What's more, I was able to save it to a test.odb (a base format)?? I thought OO couldn't do that? What am I missing and how soon will I regret it? :?
Open Office 3.3 on Win7 Pro x64
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

"Open" some *.mdb and tell us about your status bar and Edit>Database>Properties
I can not try since my OpenOffice.org will never "open" mdb since Microsoft will never write a database driver for Linux (even if they could compile such a thing within minutes).

This is the status bar for an embedded HSQLDB (that special animal I mentioned).
Image
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
DWFII
Posts: 48
Joined: Sat Jan 09, 2010 3:30 am

Re: Importing a MS Access database into Base

Post by DWFII »

Villeroy wrote:"Open" some *.mdb and tell us about your status bar and Edit>Database>Properties
I can not try since my OpenOffice.org will never "open" mdb since Microsoft will never write a database driver for Linux (even if they could compile such a thing within minutes).

This is the status bar for an embedded HSQLDB (that special animal I mentioned).
Image
Alright...I'm not at that computer right now but after lunch.

about the MySQL server?
Open Office 3.3 on Win7 Pro x64
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Importing a MS Access database into Base

Post by r4zoli »

about the MySQL server?
Your question better to ask on MySQL Forum.
Googling gives: Migrating from Microsoft Access to MySQL
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

I have not set up any MySQL server since years. But I know there are many comprehensible tools (comprehensible<>easy) which help you to configure a good starting point with one administrator and access permissions for the local computer. After you got your tables and indices working with some dummy data, you add users and groups of users (analog to this forum's groups of users, volunteers, modearators and administrators) and specify the machines from which the server accepts connections.

The software you use to build up the database structure is specific to the database type (MySQL, SQLite, Access, PostgreSQL, Oracle,...).
When a database server serves this structure multiple users can use the same database with a wide range of different applications. Web-applications (like this forum or web-shops), Base, Access, accounting software or programs for data mining and statistics.

In Access you can split a mdb file into a backend (the database) and several copies of a frontend which consists of the forms and tools to work with the database.
Access can connect to MySQL in a similar way as Base does. Then you create only the frontend tools in Access to work with the remote MySQL database that has been created by some other software.
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
DWFII
Posts: 48
Joined: Sat Jan 09, 2010 3:30 am

Re: Importing a MS Access database into Base

Post by DWFII »

OK...

I'm sure I don't fully understand the ramifications of this but here are two captures of what I'm seeing:

The first illustrates the ability to connect to a MS Access data base:
MSAccess.PNG
the second shows the database "imported" (?) into Base and saved with a odb extension in an entirely different directory.
booksold_(1024_x_768).jpg
I can get the table data but if there are forms or queries associated with the mdb information it is not translating. No biggie...jast getting the information into Base in a comprehensible format is most of what I wanted to achieve. That said, if I wanted to create a query, a form or a report with the table information now saved in odb format, could I do so from this window?
Open Office 3.3 on Win7 Pro x64
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

Yes, this is an ordinary connection to an external database. Base does not "open" the mdb. When you edit your data, only the original mdb file gets modified, not the so called "Base document" which does not keep any data at all. Remove the mdb and the Base document will be worthless.

Your mdb file is the backend served on this local machine by some Microsoft stack of software to a Base frontend which currently has no queries nor forms nor reports. The Microsoft driver only serves the raw table data so you can use them in some database tool other than Access. The connection lets you edit Access tables through the Base file.

This has absolutely nothing to do with "loading some file in Base". Base always loads a Base document which keeps the settings to connect with something else plus some additional tools to work with the transfered data (queries, forms and reports).

In the first screenshot you connect to an existing Access database.
The "open database" option is just a history of recently used databases.
The first option creates a brand new database that does not depend on external data somewhere. The database is wrapped into the Base document. But it works exactly like any other connection because the database gets extracted and connected in the same manner when you start working with it.
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
DWFII
Posts: 48
Joined: Sat Jan 09, 2010 3:30 am

Re: Importing a MS Access database into Base

Post by DWFII »

Well, this confuses me because I seem to be saving any changes I make in OO. And it is being saved, by default, into an odb format. At least, I see a very separate and discrete odb file in a separate and discrete folder. How does Microsoft know where the odb file is stored?

So...you're telling me that if I delete or move (?) the mdb, the odb will no longer have the information in it?

Then...if this is true (and I do not doubt it...just want to be clear) as long as I keep a copy of the mdb, perhaps in the same folder as the odb, I can work on (edit add, subtract info) the database at my pleasure.

In a way this sounds a lot like running Access or OO or any other database as a front end for a MySQL database. Maybe that sounds silly but to this database clueless person it sounds similar. As I understand it, to run MS Access as a front end for a MySQL database, you have to keep the MySQL database files where Access can find them and edit them. If you delete the MySQL files the Access database is no good. Am I wrong? What am I missing?

So...if Open Office will handle...edit, save, etc....the Access files, why would I need to set up a MySQL server? Why not just run with the mdb's? Delete Access altogether and store the previously generated mdb's in with the newly saved odb's.

I'd like answers...I'd like to understand it better...but if it will work this way, I'm a happy camper.

I'm still hoping someone will tell me if I can create forms and records and queries with the Access information in Open Office...?
Open Office 3.3 on Win7 Pro x64
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing a MS Access database into Base

Post by Villeroy »

When you add any number formatting (how you want Base to display certain fields) or queries (definitions which exact table data you want Base to ask from the Access driver) or forms/reports (Writer documents in Base), this information is stored in the Base file.
Working with the database as a user, editing database content in the ready made Base frontend, modifies the mdb fileonly. Just open some grid view, store some new information. The Base file should remain unchanged, but the mdb should change.
Close the Base file, rename or move the Access file, reload the Base file and the connection is broken until you put the mdb in the old place or point Base to the new location [menu:Edit>Database>connection]
I can't try. It is impossible do connect Access under Linux because MS does not publish any mdb drivers for Linux and as a matter of fact Base can not "open" mdb without the help of the database vendor (Access driver by Microsoft).

Your MySQL database has to be served by a server. It is a slightly more professional thing than Access and Base.
The connection to such a server is not specified by means of a file location C:\path\file.mdb.
Currently there are 3 different ways (3 different drivers) to connect a Base file to a MySQL server (notice that a server is a software that may run on the very same machine as the one you connect from).
As far as I know, http://extensions.services.openoffice.o ... _connector is the latest, fastest, best and easiest way to connect Base with a running MySQL server. http://wiki.services.openoffice.org/wik ... Native/1.0
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
JoeNotAnExpert
Posts: 1
Joined: Tue Oct 02, 2018 4:41 pm

Re: Importing a MS Access database into Base

Post by JoeNotAnExpert »

Almost 10 years to the day, I adhere to JimFrodsham's advice of Wed Oct 01, 2008

1) Copy/paste Access tables to an XLS (with one tab per table) and then open the XLS in OpenOffice.
2) Create an empty OpenOffice base
3) Drag each tab into the Table panel. The advantage is that base retains the order of the field names. Each field must be defined, and there were errors, but as the XLS was open it was easier to find where the problem lay and correct it in the XLS (or in the database definition).
4) Tables were the only thing that could be imported.

Now, basically 3 days later, I have converted all my Access tables and queries into ODB equivalents. Some hit-and-miss, but it's done. After many years of threatening to convert to Base, it's over. OK, I've still got Forms and Reports on the "To do" menu, but the data side of things is finished. As a non-expert, I was expecting it to be more difficult and so, in my own modest way, I would encourage others to take the plunge.
OpenOffice 4.15
Windows 10
Post Reply