Problem with changing Field Types in a Base Table! Can't edit existing field!

[DMG]

I really hope someone can assure me, a Newbie, while I try to learn LibreOffice BASE, version 7.4.3.2.

I have been unable to import my databases from Microsoft Access, so I had no choice but to begin creating my tables from scratch.

I have now discovered, however, an issue that is very concerning to me (but perhaps I am just doing it wrong):

After I saved the first table, I tried to go back and change the Field Type from CLOB to Text VarChar. But it wouldn’t let me! So I had to DELETE THE FIELD completely and enter it again at the very bottom row of the table design.

This is really starting to worry me. Because I am a Newbie, I may accidentally choose the wrong field type or length while designing my tables, etc. Then later on, after my tables are populated with data, will that mean that I will LOSE DATA if I have to delete a field name and re-enter it at the bottom?

[Wanderer]

The main concept to absorb is: Base is no database, it connects to databases. It is some kind of GUI to handle a database. What can be done is limited to the elements Base AND database can handle in a common language. Other stuff may need other solutions, for example an ALTER TABLE statement in the SQL-Window.
.
LibreOffice installs 2 database systems, you will at the moment usually deal with a embedded HSQL V1.8 as default, but Firebird is also possible to use (and it may be noted the default bilbliography is actually dBase)
.
If you dont wish to loose data YOU need to make precautions: Instead of delete and recreate you could create an additional column with new type and transfer all data in the new column. After this is done you can delete the original column and rename the new one to the old name. You should check before deleting anything, if your conversion was flawless.
.
If your columns are part of relations you may face situations where you can not delete columns at all, because they are required in the other table. Then you have to remove the relation first, make your changes and re-create the relation.
.
In most cases you should see this inconvenient ways as features to ensure integrity of your data.
.
Last point: Safety of your data is maintained by having backups, versioned backusps and external backups. This is done not by the database, but by the admin - usually YOU. In case of HSQL-embedded the data resides inside the .odb-file and is unpacked on opening/repacked on closing. Create backups often.

[DMG]

THANK YOU for your helpful tips, Wanderer.
.
I do have a follow-up question: You mentioned that I could create the new column with a new type, and then transfer all data to the new column. The “transfer of data” step is where I am stumped.
.
I have exported the data from my old MS Access database into a Calc spreadsheet, and have tried copying the data into the new BASE table. But it is not working for me. What specific steps should I take to transfer the data?

[Wanderer]

@DMG

What specific steps should I take to transfer the data

I meant column to column inside a database via SQL:

UPDATE mytable SET new= 2000+ old WHERE  old < 100

as a quick example of fixing a column with mixed years 2022 and 22. This commands enter at Tools>SQL (and substitute the lowercase parts with names from your data)
.
Depending on your needs you may have to cast types, could Truncate etc.
.
Your approach should work also, if your types fit. Mark and copy the whole table in Calc. Then go to the tables pane in base, and select paste from the context-menu (right-mouse-button) of the destinated table. An assistant appears, where you can even re-organise the imported columns.
IMHO here the main obstacle is to prepare the Id-field properly. Sometimes I just fill it with calc before starting the import.

[Wanderer]

@DMG

have been unable to import my databases from Microsoft Access, so I had no choice but to begin creating my tables from scratch

Btw: there may be other options available, if you are still working with Windows:

https://forum.openoffice.org/en/forum/viewtopic.php?t=109125

[RobertG]

@DMG Seems you are using Firebird. And there you created a field with a type, which doesn’t exist in the other internal database HSQLDB. So the GUI doesn’t know if CLOB could save the same data as VARCHAR.

Try with other fields. It will work to change a VARCHAR-Field to a CHAR field (fixed text). You could also change the length of a VARCHAR field to higher values, but not to lower values. It will work to lower values if you are using the internal HSQLDB. HSQLDB is integrated better into Base than Firebird at this moment.

[DMG]

After reading everyone’s comments, I tried the import again. I did make some progress, but still have a few questions. The exact steps I took, and the error message I received, is as follows:
.
THE PROCEDURE I USED:
I exported one of the tables in my original MS Access database to a Calc spreadsheet. (The original Access table contains 55 fields that already includes a unique ID, many Yes/No Boolean fields, some short text, a few integer fields, and one really long text Memo field.)
.
I then created a new database in LibreOffice Base. Because of everyone’s comments, I switched to the internal database HSQLDB (instead of the default Firebird).
.
I copied all the records (rows & columns) in the Calc spreadsheet.
.
In the “Tables” pane of Base, I right-clicked to PASTE the clipboard contents (that I copied from the Calc export).
.
From the “Copy Table” option, I chose “Definitions and Data”, and “Use first line as column names.” NEXT >
.
I moved all the Existing Columns to the right column. NEXT>
.
In the Type Formatting window, I made the following changes:
– Changed the ID field to Integer type… Entry Required.
– Changed many fields to the Yes/No Boolean type.
– Changed a few Foreign Key IDs to the Integer field type.
– Changed a field that contains a lot of text to Memo field type.
– Changed a few numeric fields to the Tiny Integer field because they are whole numbers that will not be used in calculations.
– Changed a few numeric fields to the Number field type.
– Left all the remaining fields at the default Text [VARCHAR} type.
.
Clicked “Create” button.
.
I received a message that asked whether a Primary Key should be created now? I chose “YES,” despite the fact that I already had a field for the Primary Key. [!!! UPDATE: In retrospect, I should have chosen “NO,” then edited the table again to set my pre-existing ID field as the Primary Key.]
.
The table was created… but now I have a few followup questions:
.
.
QUESTIONS:

1. Although it appears that all my data copied over, I still got the following Warning message:
   .
   Value too long in statement [INSERT INTO “Table1” (a long list of my 55 fields) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
   .
   Because my data is there, I’m not sure whether I still need to adjust something in order to avoid any problems in the future. Does anyone know? [!!! UPDATE: My data was NOT all there because a few of the records contained fields with more characters than defined in the Base table. In order to see which fields needed a larger length, I inserted columns in the Calc spreadsheet source with the formula =LEN(cell)
   .
   .
2. I then edited the table to change the new ID field to “AutoValue” is YES. Should I now DELETE the OLD Microsoft Access ID field? Is there any reason to keep it? [!!UPDATE: See above procedure steps. I should NOT have created a new ID field, but set my existing ID field as the Primary Key.]
   .

THANK YOU for any assistance!

[RobertG]

Which system do you use?

@DMG

Because of everyone’s comments, I switched to the internal database HSQLDB (instead of the default Firebird).

Every “normal” installation doesn’t use Firebird as default. Creating a Firebird database is an experimental feature. Might be you have switched experimental features to ‘on’. Switch it off, because there will appear the migration wizard to Firebird every time you open your HSQLDB.

@DMG

Value too long in statement

If you get this there couldn’t be saved all content of the Calc table to the HSQLDB. You have written about Memo-fields. Could be the text is longer than the maximum you allowed in HSQLDB.

Are you sure all records have been added to the HSQLDB table? Thought the import would end at the row, which couldn’t be imported well…

@DMG

I then edited the table to change the new ID field to “AutoValue” is YES. Should I now DELETE the OLD Microsoft Access ID field? Is there any reason to keep it?

Don’t know why you created a new ID-field. If this table is the only table you want to import you could delete the OLD Microsoft Access ID. If there are other tables to import there might be tables, which will use this ID as foreign key.

[Wanderer]

@RobertG

Thought the import would end at the row, which couldn’t be imported well…

Have seen this sometimes, and was asked, if I’d like to continue. Only non-fitting lines were omitted. Therfore:

@DMG

I’m not sure whether I still need to adjust something in order to avoid any problems

Count your rows. Maybe sort both tables and compare the sequence…
.
You may also check fields lenght before import. See:
https://forum.openoffice.org/en/forum/viewtopic.php?t=109125

[DMG]

Thank you for the tips, Robert G and Wanderer!
.
I did reset the Options to switch off the “experimental” mode.
.
I did a comparison check and all my records did copy over. [I attempted to create the database SO MANY TIMES and fixed so many errors after each warning message that I probably figured this warning out along the way]
.
You are absolutely right about the duplicate ID fields… my bad. Base was automatically creating these ID fields each time, and it took a few attempts before I figured out how to set the primary key using an existing field.
.
THANK YOU BOTH SO MUCH FOR YOUR HELP!! I’m a long way from being done with the entire database, and I’m sure that I’ll many questions in the future, but I will post these new topics under more appropriate headings and tags. THANK YOU AGAIN!

How do I show my appreciation on this forum to you both? Just click the “Like” heart?

[RobertG]

@DMG : Set one of the right answers to “solution”. So everybody will know: a solved problem.

[DMG]

Sure! Both you and Wanderer offered helpful solutions and suggestions, but this forum allows for only ONE “solution.” In a long thread with multiple tips, it would be nice to be able to mark multiple solutions. I do appreciate everyone’s help, though!! Happy New Year!