How to control the field type when converting mdb to odb?

[Chengpu@gmail.com]

Hi, I have a mdb file to be converted to odb. One of the tables contains a "Long Text" field. After conversion, it becomes "SQL Null []" field, with its content truncated. Is there a way to control the field type during conversion?

Thanks
Regards,
CP

[Bidouille]

One of the tables contains a "Long Text" field.

LONGVARCHAR is not enough?

[Villeroy]

Hi, I have a mdb file to be converted to odb. One of the tables contains a "Long Text" field. After conversion, it becomes "SQL Null []" field, with its content truncated. Is there a way to control the field type during conversion?

Thanks
Regards,
CP

What exactly did you do? Which type of database is indicated in the status bar of your Base document?

[Chengpu@gmail.com]

I just use OpenOffice Database Wizard to connect to an existing mdb access file. The record type of Long Text in the access db is converted to SQL Null field type, possibly because some long text records contain http links.

[Bidouille]

to connect to an existing mdb access file

So it's not "converting" but just connected.

The record type of Long Text in the access db is converted to SQL Null field type

Where you see that? Provide a screenshot to understand.

[UnklDonald418]

It appears that MSACESS "Long Text" data type is similar to LONGVARCHAR type in Base with an HSQL back end. CLOB objects can be difficult to port.
What I saw was that MSACESS only delivers the first 64K characters and you must request more pages to go beyond that limit.
Are your strings of data really that long?
In Base, on the third page of the Copy Table dialog you can choose the data type and size for each field in the new table.
If your text fields are less that about 30K try VARCHAR with an appropriate size limit, if larger than 30K try LONGVARCHAR.
If the data strings are more than about 3K, I don't think an Embedded Base database will work very well because the odb file will be so large that it may take minutes to load or save the file. In that case a JDBC connection would likely work better.

[Chengpu@gmail.com]

Hi! Thank you for helping me on my question. I have not figured out how to post a picture yet.

I found the type of the field in design view of MS Access to be "Long Text".

I then:
1. Click the OpenOffice Database
2. Connect to the existing mdb
3. Click Finish and choose a name for the odb.
In these steps, there is no place to choose what field type should be.

In the converted odb, edit shows that the corresponding field has type "SQL Null []", with the content truncated.

Is there any other way for the conversion?

[Villeroy]

In the odb file, right-click the table, choose "Edit..." and look up how Base declares the offending column.
Try to convert the column explicitly like

Code: Select all

SELECT CAST("offending_column" AS LONGVARCHAR) FROM "tbl"

Code: Select all

SELECT CAST("offending_column" AS VARCHAR(1000)) FROM "tbl"

or does it recognize the length at least?

Code: Select all

SELECT LENGTH("offending_column" ) AS "Len" FROM "tbl"

There is also an alternative, platform independent driver for MS Access: https://ucanaccess.sourceforge.net/site.html

[UnklDonald418]

1. Click the OpenOffice Database
2. Connect to the existing mdb
3. Click Finish and choose a name for the odb.

Look in the lower left of the Base window it will probably show Microsoft Access and on the same line name and location of the mdb file you are connected to. That indicates you sim ply have a connection to your mdb file, no conversion has taken place.

To create a converted Base database leave this connection open
Open Base again this time selecting the option Create a new database, accept all the default options and give the new database a suitable name
This Base window should show Embedded database in the lower left corner and on the same line HSQL database engine (the default back end for Base)
From your mdb connection drag the table you want to convert to the table area of the Embedded connection and you should see a Copy Table dialog.
You can keep the same table name or type a different one and select the Definition and data option
Also select the Create Primary key option. This is important if you want to be able to edit your converted database.
Select Next to get the Apply columns dialog page.
Select the >> option to move all the Existing Columns from the mdb file to the new table.
Select Next to see the Type formatting dialog page.
Here you should check that each field has the correct data type and size. On my system Text fields default to a length of 50 characters, which may be too short for some of your fields particularly the troublesome one, so lengthen them appropriately. For the troublesome field I would chose VARCHAR type and a length of 3000. Only use LONGVARCHAR as a last resort.
Click Finish and the table will be created.
If it reports errors then Cancel and start over, increasing the sizes of any field of the VARCHAR type that might not be large enough.
If you succeed in creating the table but your data is still truncated or you see other issues you can Delete the new table and try again.

Once the table is successfully created there is one more change needed to make the table editable.
Right click on the new table name and select Edit to open the Table Design dialog.
Select the Field Name "ID"
In the lower pane change AutoValue to Yes.
Exit the Table Design dilaog and Save the changes.

One warning about Embedded Base databases. An odb file is actually a zip archive and any interruption of the zip process usually results in file corruption and data loss. It seems to happen primarily to impatient laptop users who close their computers before Base has completed saving its data to disk, but an operating system lockup on any computer can have the same result. Back up the odb file frequently and I recommend keeping a few archival copies just in case.