OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] Cannot add new field to existing table

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Sun Mar 01, 2009 10:39 pm    Post subject: [solved] Cannot add new field to existing table Reply with quote

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


Last edited by 2ways on Mon Mar 02, 2009 3:54 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Mon Mar 02, 2009 7:43 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Mar 02, 2009 10:48 am    Post subject: Reply with quote

Hello

Quote:

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


Look here
http://www.oooforum.org/forum/viewtopic.phtml?t=80023&highlight=

short : maybe not your fault but an bug in OOo.


Romke
Back to top
View user's profile Send private message
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Mon Mar 02, 2009 12:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Mar 02, 2009 2:45 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Mon Mar 02, 2009 3:12 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Mon Mar 02, 2009 3:54 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Mar 02, 2009 4:36 pm    Post subject: Reply with quote

2ways:

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

You said:
2ways wrote:
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.

That is probably, because, you are using the OpenOffice 'QuickStart' option, and, the database is 'locked', until that component is also ended.

But, there is another ( much better ) technique you can use, to 'see' your results, without first having to exit the database and / or the QuickStart.

After making your SQL changes to your database tables, you only need to "Refresh" the database. From the database menu:

View -> Refresh Tables

Even with the SQL changes, you will now be able to see them reflected in the GUI.

Sliderule
Back to top
View user's profile Send private message
2ways
General User
General User


Joined: 16 Jul 2008
Posts: 36
Location: Sydney, Australia

PostPosted: Mon Mar 02, 2009 4:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group