[solved] Cannot add new field to existing table

2ways · Last edited by 2ways on Mon Mar 02, 2009 3:54 pm; edited 1 time in total

Hi Folks,

I have trawled the forums and found every other problem, but not this one.

I cannot add new fields to an existing table. The table was created by importing a csv file via Calc as per a procedure I discovered on the web. That was years ago. Everything has been working fine. I have been able to add fields whenever I wanted.

All of a sudden (may have been when I updated to OOo 3.0.1, but couldn't be certain), I can no longer add fields to my table.

I can enter table design mode, but cannot change anything in the table. If I try to 'Insert Rows' via right-click, OpenOffice crashes.

I can add new data and modify old data in the table itself.

I can add a new column via SQL directly, and it shows up when I open the table, but it doesn't allow me to save any data input into the new column and the new column does not appear in the table design view.

I am using an Intel MacBook with OS X 10.4.11 and OOo 3.0.1.

Any help? Any fellow sufferers?

Cheers,
2ways

2ways · Posted: Mon Mar 02, 2009 7:43 am Post subject:

Brief update...

I exported the main table in the database into Calc and saved it.

Then created a new database and imported the table from Calc.

I was able to set the primary key and delete fields, and I could save those changes without any complaints.

I could also add new fields and save the changes, HOWEVER...

There seems to be a fixed number of fields in my table because, while I can add new fields, I can only add new fields up to the number of fields I delete. That is, when I open the table in design view, I cannot add any fields, but if I then delete one, I can add new one back.

Also, it continues to be true that right-clicking to 'insert rows' crashes OOo every time.

Thanks for the help.

Cheers,
2ways

RPG · Posted: Mon Mar 02, 2009 10:48 am Post subject:

Hello

2ways · Posted: Mon Mar 02, 2009 12:57 pm Post subject:

Thanks, Romke.

It's good to know there are some related issues out there.

I'm using an HSQLDB database.

It would seem the whole structure of my table is being constrained by something. It could be some sort of permissions problem, but I'm not really sure where to look.

It could be something added unintentionally to my table definition.

How do I access the SQL for the whole table? I know how to issue commands, but where can I view and edit the SQL table definition?

My problem doesn't seem to be with data entry, but with adding a new table column (or field).

Thanks again.

2ways

RPG · Posted: Mon Mar 02, 2009 2:45 pm Post subject:

Hello

If you cannot change the table in the normal way you can change the table with the SQL tool. You are talking about a constraint making it impossible to change your table. I have no idea about it. If you not use the SQL tool it is difficult to change the table . I believe they are starting with make it more easy.

Better is maybe to read post of Sliderule he give good examples.

as example this link
http://www.oooforum.org/forum/viewtopic.phtml?t=80210&highlight=alter+table+add

He give information who can be important for you.

Romke

2ways · Posted: Mon Mar 02, 2009 3:12 pm Post subject:

Thanks, Romke.

The curious thing is that, during 'normal operating conditions', I am able to change the table definition by simply clicking on the next row down in the table design view and adding a new field. Also, the right-click in table design view gives you the 'insert rows' option.

Are those methods not actually supposed to work? Are they just placeholders for features that will be completed properly in future releases?

I am happy to manipulate the table via SQL directly, and, in the past, when I have done that, the table design view reflects the changes made by SQL, but now, the table design view is ignorant of the changes made by SQL.

I'll just have to create a new database with the main table the way I want it now, and hope I don't ever want to add to it! Hopefully, I can get the data into the new table without too much trouble.

Thanks again.

2ways

2ways · Posted: Mon Mar 02, 2009 3:54 pm Post subject:

Thanks, Romke.

I've tried again with SQL and have discovered that if I close and reopen the entire database, the table design view does reflect the changes made with SQL.

I was previously only saving and closing the table, and that wasn't good enough.

Still curious about the 'insert rows' option in table design. Anyone?

Now, if I could get someone to answer my post about filtering forms based on a concatenated full name combo box, I'd be very excited!

Thanks again and apologies for not trying the simple 'close and reopen' option before posting.

2ways

Sliderule · Posted: Mon Mar 02, 2009 4:36 pm Post subject:

2ways:

Not wanting to interrupt the great advice from Romke, but just an FYI, and for others reading this.

You said:

2ways · Posted: Mon Mar 02, 2009 4:43 pm Post subject:

Thanks, Sliderule.

That will certainly be more efficient!

Any thoughts on http://www.oooforum.org/forum/viewtopic.phtml?t=80599?

Sorry to sneak that one in...

Thanks for the help.

2ways