Importing a MS Access database into Base

[ChuckS]

I have a database that was created in MS Access, the person who created it is no longer around, the computer that it ran on died - I was able to startup in safe mode and retrieve the database file.

I searched the forum and did not find an answer to this question, I did read the article about cutting and pasting into a new Base document suggested by Kabing in another thread, however Since that machine died I no longer have Access to cut and paste from.

Is there any way to import the Access database file or open an Access .mtb file in Base?

I can use either a Mac - OSX 10.4.11 (Tiger) or a PC with XP Pro (which is currently running OpenOffice.

Thanks

[DrewJensen]

Use the XP machine to start.

First - you can connect to an MDB file directly ( not an import a connection to the mdb tables ) for specifics see
http://wiki.services.openoffice.org/wik ... oft_Access

Second - to import into a Base native database you can just create the Base file that connects, then create a standard Base file. Open them both and drag and drop tables from the one to other...each time you do the Copy table wizard opens and you can follow the wizard to move the structure and data - there a few catches, but just try it first and if you get hung up on any particular field or whatnot come back and ask..

[ScooterC]

Good Afternoon,

I appreciate the directive, BUT....
That is NOT a conversion from Access TO Base.
It merely attaches the Access (MDB) TO the Base (ODB) .

Most of us have worked with MS- Office for many years and are MORE then willing to be 100% OO citizens.
Open-Office, as a whole, is (IMO) the best on the market.
It has done 99% of everything I need, for the last five years. Improvements have JUMPED to a better Product.
My biggest stumbling block had been the Database. That has been solved almost completely.

I have several 2003 Access databases that I WANT to make ODB databases.
OR at the least retrieve the Tables (long missing Excel spreadsheets).
It would appear that real conversion is not possible, at least "in-house".
I would Love to be corrected on this.

Take Care,
ScooterC

[DrewJensen]

right- you do FILE>NEW>Database

On the first page of the new database wizard, select "CONNECT to existing database", in the drop down select MS Access.
Finished...

Using OO.o 3.0 you will have two options for the type of databse MS Access (.mdb) and MS Access2007 (.accdb)

Am I missing something?

[DrewJensen]

Oh sorry - I was reading that backwards.

You are so right - there is no place that you can say File>Import>MS Access

So - do what I said above for the CONNECTED base file.

Then do
File>New>Database
Finished

With both files open, drag the tables from the MS Access - Connected file to the Base Native file. The table import wizard starts for each, when you are done moving the tables they are imported.

Remember to drop the table during the Drag n Drop to the Tables section in the Base file window.

Is this perfect..not hardly...but it works with some reservations

[earlra]

In reference to DrewJensen's above instructions to "On the first page of the new database wizard, select "CONNECT to existing database", in the drop down select MS Access.", I must be missing something obvious, but my dropdown does NOT include the "MS Access" option, as shown in the attached screenshot. I installed mdbtools as recommended. What am I missing? I am running OOo 2.3 on Kubuntu 7.10. Thanks.

[Villeroy]

Microsoft provides the required tools for Windows to access Access by third party software. They will never produce any database drivers drivers for Linux.

[KHall]

I have tried Drew Jensen's method for importing data from an MS Access database into OOo base, but I'm having some difficulty. I can establish a connection to the .mdb datesource with no problem. However, when I attempt to drag tables from the connected file to the new OOo base I get one of two error messages.

First, when attempting to copy I am old I need either a unique index or primary key to proceed. I'm asked if I want to create a primary key. If I answer yes, then I get an error message that says, " Unexpected token: in statement [CREATE TABLE "name of table"(""] "

If I answer no to creating a primary key I get an error, "SQL Status: 22001
Error code: -124

Value too long in statement [names of fields] VALUES (?,?,?,?,?,?,?)]

I think my fields are simply too long under the second scenario, but if anyone has ideas why there's an issue with the primary key, and whether the absence of a primary key will affect my ability to work with the database file, I'd appreciate your response/help.

[Captain Maybe]

I have exactly (more or less) the same situation as KHall. I have an Access database, but no longer have Access. Connecting to the database works, but doesn't appear to allow me to edit the information. Copying the table into a new Base database brings up the error messages KHall quoted.

Does anyone have any solutions?

[nragone]

I have the same problem. When I run Base, the Database wizard pops up and gives me the choice of Creating a new database, opening an existing data base, or Connecting to an existing database. If I choose the later, then click on Microsoft Access (top of the list), then browse to my Access database and select it. Base will indeed open it, but put the Tables and Queries from the Access database into tables and ignore the forms and reports. Unfortunately I haven't found a way to move the queries out of Tables and in to queries or to properly import the forms and reports.

[DrewJensen]

well - there is no support for MS Access forms in Base, it is just that simple. To do so would require that someone take on the job of coding that.

Queries are the same, although I would suspect that coding for this would be less work then forms. As you have noticed the queries in the Access file are treated as Views by base..with the latest versions of Base view definitions can be edited ( was not available in OOo versions prior to 2.4 ) - but I have not tired this with MS Access and kind of doubt it will work...( I'll give it a try though just to see ). If they will open up in the Query designer, as a view with MySQL does, then you could just use Save As to then create the query in the Base.

Even so, queries in Access have features that are just not supported at this time in Base, so even if the edit button did work many queries would fail to open in an editor.

[panther]

I am encountering the same problem.

Is it possible to paste some screenshots for this process?

[TXLandman]

I've read the wiki file on how to open the MDB but I'm completely lost still.
I can get the thing open but once it's open I don't know what I'm looking at. It doesn't help that I haven't ever used Base or Access but I'm generally pretty good at figuring these things out.

Any help would be appreciated.

[Villeroy]

I can get the thing open but once it's open I don't know what I'm looking at. It doesn't help that I haven't ever used Base or Access but I'm generally pretty good at figuring these things out.

Sorry, but databases are not the kind of software you can grasp intuitively. They have more in common with development tools to create user-orientated tools by means of input forms and reports. Having access to the raw data is the first step. You may not even be able to edit the raw data in the table-grids if there are relations involved.

[JimFrodsham]

Hi

I have been playing with this for a couple of days, I have "sort of" managed it.

I exported all the tables from an Access db as XL spreadsheets, then imported them into an new empty Base.

The data seems to be fine.

I have lost the Queries, and the Basic code behind all that. The queries shouldn't be a problem, it's all pretty basic SQL.

I'm not sure about the code yet, or the forms. I imagine I'll have to redo those.

Jim

[JimFrodsham]

Ah, no sorry, that hasn't worked as I thought.

Back to the drawing board.

[Villeroy]

Under Windows, Base can use Microsoft's database engines just like Access can connect to other vendor's databases. Playing around with it is almost hopeless like with Access. How did you "import"? from where?
http://wiki.services.openoffice.org/wiki/Base
Another rambling of mine which obviously helped a little: http://www.oooforum.org/forum/viewtopic.phtml?t=75379

[hugoriocosta]

Hello,

I have almost the same problema KHall (on Fri Jul 11, 2008 12:47 am)


When attempting to copy an old DBF table I'm asked if I want to create a primary key. And I answer no, because I have a suitable feild that can act as a PK.

I get an error, that says : "An error occurred. Do you want to continue copying?"
"SQL Status: 22001
Error code: -124

Value too long in statement [names of fields] VALUES (?,?,?,?,?,?,?)]

I realy don´t know what is appening, I'm not exactely an expert on databases...
But i think that as something to be with the number of columns or the size/length of the fields.

I've try to select some of the fields in the paste wizard, and worked fine... So... That´s a mediocre solution.
My DBF table has 63 fileds and over than 30.000 entrys. Its an database about biological data, like wiegths, dates, mesurements, etc... And I need all the fields.

Any sugestions...

[newuser]

With Windows you should connect to dbf files using the ODBC driver built in the OS, it's faster than the DBF driver supplied with OO.o and it has full SQL support. It's probably the fastest backend available with Base, on my system is 10/20 times faster than the embedded HSQLDB engine and it's fully usable with forms or reports.

[scarles]

This is how I was able to import data only (no forms, queries, etc.) using Windows XP (your experience may vary).

This looks really long, but once you've done it a couple of times, it goes pretty quick.

• Create a new Base file.
• Select "Connect to an existing database" (select the drop-down and scroll up to get to MS Access).
• Browse for your MS Access file.
• Click the "Finish" button.
• Give the database a name and save someplace easy to find (you can just delete it later).
• You should now see your tables from your MS Access database in the bottom pane.
• In the far left hand panel click on "Reports."
• In the top pane click on "Use Wizard to Create Report..."
• The Report Wizard opens.
• Choose the table you want to work with from the drop down menu.
• Select all of the "Available fields" you want in your new database.
• The ones you want will be transferred to the right hand panel that says "Fields in report."
• Sort them in the order you want them in your new database (the one at the top will be on the left and so on).
• Click on the Next> button.
• If you want, change any of the names of the existing fields to the names you ant for the new field names.
• Click on the Next> button.
• I skipped the "Grouping" part (I have no idea what that will do).
• I you want your new database sorted when you insert it, now is the time to set your sorting options (although you can sort it as a new database as well).
• Click on the Next> button.
• You can skip the layout and just click on the Next> button.
• The last option in the left panel says 6. Create report and on the right side at the top it says "Decide how you want to proceed."
• It doesn't matter what you title your report, so I just leave the default.
• **Important** Select the top option "Static report."
• Click on the Finish button.
• Your table should now show up.
• If the table is too wide for this view, go to the menu bar and select View > Web Layout (otherwise just leave it alone).
• Now it's time to select all of the table (this is a tricky part because it can be frustrating).
• Using the mouse, position the I-beam just above the title of the first field so the bottom of the I-beam is barely above the field name.
• Drag the mouse to the right. (This should have selected all of the columns. If it didn't, play around with the I-beam location until it does.)
• Copy.
• Create a new Calc spreadsheet.
• Immediately "Paste."
• My first row is always empty and I add field titles above each column.
• You can now close all of your other documents, databases, windows, etc.
• Create a new Base database (don't open an existing one or connect to an existing one). Don't do anything with this database.
• The only windows you should now have open are your spreadsheet (with all of the data pasted in) and the new database you just created.
• Change the size of the windows and arrange the windows so you can see both.
• In the spreadsheet, select the entire spreadsheet by clicking in the little box above the first row and to the left of the first column (or Menu>Edit>Select All or [Ctrl] [a] in Windows).
• Drag and drop the entire spreadsheet by click-dragging anywhere on the spreadsheet cells.
• Drop it on the Tables area of the new database you created.
• The "Copy table" wizard pops up.
• I give the table a name but ignore everything else and click on Create.
• You will be asked if you want to create a primary key. If you select yes, a new field will be added to your table. That field can be deleted later and a different field assigned as the "Primary Key." If you select no, you may get an error, but everything should still be okay. Make sure you provide a "Primary Key" once your table is imported.
• Right click on your new table and go to Edit.
• Make any changes here because your fields are all set to a default of Text. [If you run into some problems and the data doesn't come through in the right format, you may want to may want to try it again, but at the point where the "Copy table" wizard pops up, click on the Next button and try formatting some of the fields here]

I have no idea what will happen if you have a lot of formulas or weird/extensive field types or properties in your original database. As I said at the top, this works for me to get the data from my databases into Base. I have had problems losing boolean data (when it comes into the database as "text," it shows up in the new table with the correct checks in the boxes, but as soon as I change the field to boolean, it loses the data). I also have no idea what might happen with a huge database.

I hope this works for somebody.

-scott c

[Villeroy]

Thank you very much for your very detailed step-by-step instructions.

This is how I was able to import data only (no forms, queries, etc.) using Windows XP (your experience may vary).

Since Microsoft provides the drivers to connect with Access on it's own Windows platform and nobody could succesfully finish working drivers for other platforms this can not work on any other platform other than Windows. No, Microsoft will certainly never release anything which helps users to leave their platform.

I have know idea what will happen if you have a lot of formulas or weird/extensive formatting in your original database

A database does not store formattings. It stores raw data (mainly numbers, text and binary data) and the driver delivers the raw data to any requesting frontend, leaving all formatting up to the frontend, be it Access, Base a web-application or any other tool set.
You should definitively try the self-reporting tool in Access. It delivers reports (in txt or doc format) about all the tables, views and queries. From this report you may be able to copy and paste the queries' SQL-strings into any other database application (or into a decent text editor for adjustments). Access has a huge arsenal of helper-functions, which are specific to Access. This means that some queries will refuse to work due to missing helper-functions. But at least you get a chance to construct something equivalent or find a good reason to stick with Access for now.

[scarles]

Thanks Villeroy.

A database does not store formattings.

My use of the word "formatting" was really supposed to be "field types" or "field properties." I knew what I was referring to, but my fingers and brain didn't communicate with each other. I fixed it in the original post.

You should definitively try the self-reporting tool in Access.

I have Access and I'm fairly familiar with the features. Thank you. But for those who don't have Access or its self-reporting tools, but they do have Access files, and would like to get the data from there into OOo, this is one possibility.

-scott c

[Arineckaig]

FWIW I offer a suggestion for Windows users who want to bring Access tables into Base but no longer have the MS Access program. When faced with the error messages described earlier in this thread, I resort to an alternative method whereby an Access table that has been connected to Base can be copied and pasted into a new HSQL embedded Base file.

Open a new Calc file, press f4 and select the Access table that was previously registered with Base. Click top left corner and drag the contents of the table into Calc.

For some reason that I do not understand this method of dragging an Access table into Calc is more tolerant of certain data types in Access that are rejected when directly copied and pasted from a Base/Access connection to Base embedded HSQL. In any event once the data is in Calc it is much easier to see where problems may arise and to correct the cause of not entirely helpful error messages that can still appear when the Calc file is copied and pasted into the Base/HSQL file.

[Villeroy]

Thank you for this important hint. Yep, Calc-spreadsheets know only text or floating point numbers (doubles). You can convert most of the database types into text or double quite easily, except for binary(picture, memo).

For some reason that I do not understand this method of dragging an Access table into Calc is more tolerant of certain data types in Access that are rejected when directly copied and pasted from a Base/Access connection to Base embedded HSQL.

This would be important to know to improve the situation somehow. If you could describe which data do import correctly into Calc but don't import into HSQLDB we could file an issue to the developers. Do you get errors (which?) or do you get wrong values imported (which values?)

[Arineckaig]

If you could describe which data do import correctly into Calc but don't import into HSQLDB

I was afraid you might ask this. Alas, I lack your attention to detail and each time I hit a problem I simply got around it but failed to record the source or cause of the problem. Henceforth I will try harder. My apologies.

[rbturnbull]

I got the same error -Value too long in statement [names of fields] VALUES (?,?,?,?,?,?,?)] while trying to drag a table from
an Access DB.
I found the problem was caused by a Boolean field (Yes/No) being incorrectly converted to a Text field.
When I corrected it in the Type Formatting screen of the wizard, it worked fine

[Villeroy]

I got the same error -Value too long in statement [names of fields] VALUES (?,?,?,?,?,?,?)] while trying to drag a table from
an Access DB.
I found the problem was caused by a Boolean field (Yes/No) being incorrectly converted to a Text field.
When I corrected it in the Type Formatting screen of the wizard, it worked fine

The simple workaround would be a checkbox. While in design mode right-click the text box and replace it with a checkbox.
The bug deserves a bug report for component "database access (dba)" from a Windows user able to reproduce the problem. [Tutorial] Reporting bugs or suggestions.

[MarcvanWegberg]

Hi,

I converted two MS Access databases, each containing only one table, to OpenOffice Base using the two instructions by Drew Jensen above. I integrated them in the following instruction:

- In OpenOffice Base you do FILE>NEW>Database
- On the first page of the new database wizard, select "CONNECT to existing database", in the drop down select MS Access.

- Make another new file: File>New>Database

- With both files open, drag the tables from the MS Access - Connected file to the Base Native file. The table import wizard starts for each table. When you are done moving the tables are imported.
- Remember to drop the table during the Drag and Drop to the Tables section in the Base file window.

For me this instruction worked, mostly. Of the 503 records in one of these databases, 499 records successfully made the conversion. What happened to the other 4 I don't know. Of the 297 records in the other database, 294 converted. (Unfortunately I don't know which three I lost!) So perfect this approach isn't, but I settled for it happily enough. Thanks for this great tip. I then copied these two databases to OpenOffice Base on the Mac and so made the transition from Windows to MacOSX.

[Villeroy]

For me this instruction worked, mostly. Of the 503 records in one of these databases, 499 records successfully made the conversion. What happened to the other 4 I don't know.

You can drag the icon of a table or query from the database main window into a spreadsheet.
You can drag the grid view of a loaded table or query from the grey top-left corner in order to copy all records.
A spreadsheet does not care about the structure of the data. It takes any text, date and number at any position.

Copy bot tables side by side on the same sheet leaving one blank column between them, sort them by the same column and compare.
Click a cell anywhere in the field to be sorted and then click the sort button [A-Z].

[evwool]

I do have Access so can't exactly duplicate the problems here. Difficulties I did notice when
dragging an Access table into an Open Office database:
1. Don't 'allow' Base to choose the data types and field definitions - during the import stage,
click on each field in turn and make sure that it is the correct data type - ie that not only is
'Date' is chosen as the Data-type for Date/Time fields, but you also choose the format of the
imported data by using the Format button. If I remember rightly, the dates caused the most
problems
2. Make sure that Text fields are long enough for the data you want to import into them. Base
chooses a default of 50 characters for a text field but your Access field may be longer so pay
attention to the field length button.
3. Access has a different way of expressing Boolean values ('tick fields') from most other
databases. It counts True as -1 (most other database formats count True as 1) and False as 0. So
import Boolean fields as Integer fields (I've started replacing my Boolean fields in my Access
databases with Integer fields anyway for other reasons and just restricting the input into these
fields to 0 and -1). It will then be easy to use an Update query in Base to change the -1's into
1s if you want to duplicate a Boolean field.