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

Read this if you cannot insert/delete/update records
Goto page Previous  1, 2, 3, 4, 5  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
sdd
Newbie
Newbie


Joined: 19 Jan 2009
Posts: 2

PostPosted: Sat Jan 24, 2009 6:06 am    Post subject: Reply with quote

sdd wrote:
I am running Openoffice 3.0 on Ubuntu, and have a problem with the database component.

I can create a table with all the necessary fields and save it. However, when I open it, I cannot add any data. I get the same on both my Ubuntu machines.

When I open the table, every button on the toolbar is greyed out, except 'Refresh'. The permissions on the file seem okay (I couldn't write to it running OO using sudo).

Any suggestions would be appreciated.

Stephen.


I have solved this with help from other forums. I had to create a primary key - without it I cannot save data, with it, it all worked as I expected.

What I don't understand is why it will allow me to create a table without a primary key if it won't work without one.
Back to top
View user's profile Send private message
geoffschultz
General User
General User


Joined: 04 Feb 2009
Posts: 5
Location: Marlborough, MA USA

PostPosted: Wed Feb 04, 2009 7:46 am    Post subject: Reply with quote

After a lot of searching, I finally found the answer as to why I couldn't edit any data in OOo 3.0. The answer for my problem was the lack of a primary key. Here's the URL:

http://dba.openoffice.org/FAQ/specific.html#editmysqltable

and here's a quote of the specific text:
Quote:

I cannot edit a newly created table!

The first reason may be that the table was created without a primary key or a unique index. In this case open the table design for that specific table and select the columns which identify a row uniquely, open the context menu and select the entry "Primary key" and save the table. After that the table should be editable.

Second reason, the table privileges are not set right. This is the case when the table "Mysql.tables_priv" doesn't contain the right privileges. To verify this, open a new query design and insert in the text view the statement "SELECT * FROM mysql.tables_priv" and switch the mode to native in toolbar. When executing this statement, you should see your table name with the necessary privileges. If your table name doesn't appear in this list, you have to grant yourself the privileges. This isn't done automatically. Open the context menu upon your tables entry on the left side in your data source browser and choose SQL. In the appearing window insert "GRANT ALL ON yourtablename TO yourusername" (find more about the GRANT syntax at the MySQL documentation site) and execute this statement. Now your table should be editable.
If you're connecting to MySQL via JDBC, the useHostsInPrivileges=false option in the connection URL may be worth additional consideration. It tells MySQL to ignore the host which you are connecting from, when calculating your privileges.

Third reason, your driver which you are using isn't up to date. In earlier releases of MyODBC we recognize that the privileges and the currently username are not correctly returned.


-- Geoff
Back to top
View user's profile Send private message Visit poster's website
UW
Newbie
Newbie


Joined: 28 Feb 2009
Posts: 1

PostPosted: Sat Feb 28, 2009 4:26 pm    Post subject: Using "Compare Document" feature under "EDIT& Reply with quote

What I want to do is take one database of info. and automatically apply the changes to the other database of info. Example: I download all product changes for that day from my supplier. My supplier has 10.0000 products or so in their database. Of those 10,000 products, the amount that changed that day was only 313. I carry 1,200 of their products in my store. On average, I may carry only 5-10 products on the "supplier product change list" of 313, that actually apply to my store. So instead of manually going through and looking to see if I have any productrs in my store that are on the need to be changed list, I went and opened up both my store cataloug file (CSV) and my suppliers product change file (CSV). Then I went to the toolbar in the my store cataloug file. I clicked on EDIT. I scrolled down and clicked on COMPARE DOCUMENT. Then a browser box comes up showing all my files in My Documents. I scroll over and find my supplier product change file. I click INSERT. Then a box comes up with a few buttons. One of the buttons says ACCEPT ALL CHANGES. Another says REJECT. Another simply says ACCEPT. I click on ACCEPT ALL CHANGES, and then nothing was changed in the items that needed changing in my store cataloug, i.e. a price change on a product, a dicontinued product, etc.

Follow me? I thought this feature automatically puts any changes on the file that needs changing, simply by automatically doing a product compare of both files. Help!
Back to top
View user's profile Send private message
JeffNeedle
General User
General User


Joined: 20 Dec 2005
Posts: 5
Location: Chula Vista, CA

PostPosted: Sun Jun 28, 2009 12:06 pm    Post subject: Another solution for database problems Reply with quote

Thanks to a good fellow from the UK, I now know that the database won't function unless Java is correctly activated. Under Options, Java, you should have at least one of the Java engines checked off.
_________________
Jeffrey Needle
jeff.needle@gmail.com
Back to top
View user's profile Send private message Send e-mail
Tiresias
Newbie
Newbie


Joined: 27 Jul 2009
Posts: 1
Location: Austin, Texas

PostPosted: Mon Jul 27, 2009 6:35 pm    Post subject: Learning Base Reply with quote

I am a new user trying to learn OOo Base. My first effort is a table with 35 data fields and 6 tables linked through the combo box to insert standard values. I set up the table and the combo boxes with a bit of trouble, but finally got it right. The problems occur when I try to load data into the main table. I get many "Integrity constraint violations ..." and my editts don't all get into the data. After many hours recreating tables and making corrections I tried creating a simpler table having only 10 data fields. It worked with no problem, so, there seems to be a size limitation on the number and/or size of the data fields per record. If there is a size restriction where do I find it documented? I amusing a for reference:
OpenOffice.org User Guide for 2.X
Getting Started with OpenOffice.org 3 by OOo Authors
Beginning OpenOffice 3 From Novice to Professional by Andy Channelle
Online help notes
These documents tell a very simplified story. There are many options and variables lefty unexplained.
I would appreciate any help that is offered.

Tiresias
_________________
Neophyte OOo User for coin collecting.
Back to top
View user's profile Send private message
adamu82
Newbie
Newbie


Joined: 28 Dec 2009
Posts: 1

PostPosted: Mon Dec 28, 2009 3:26 am    Post subject: I can not edit data in my base.. Reply with quote

Hello,

'm a beginner in OOo Base. My table can not be edited, on this web site I've foud a lot of different possibilities why it can be so. But I need a step by step tutorial Smile
I'm using OOo 3.1.1 on Windows XP

Thank Everyone for help

Greetings from Warsaw, Poland
Back to top
View user's profile Send private message
elizas
General User
General User


Joined: 04 Feb 2010
Posts: 6

PostPosted: Tue Apr 27, 2010 6:04 am    Post subject: How to INSERT, UPDATE or DELETE record in one statement Reply with quote

Generally we write separate statements to INSERT, UPDATE or DELETE data based on certain conditions. But now in SQL Server 2008 there is a new feature present called MERGE statement using which we can INSERT, UPDATE or DELETE record in one statement.

MERGE is a new feature which Microsoft has introduced with SQL Server 2008 that provides an efficient way to perform multiple DML operations. In previous version of SQL server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most imortant advantage of MERGE statement is all the data is read and processed only once.The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement.You need to specify the SOURCE and the TARGET table or query which should be joined together.

http://www.mindfiresolutions.com/How-to-INSERT-UPDATE-or-DELETE-record-in-one-statement-442.php
_________________
Cheers,
Eliza
Back to top
View user's profile Send private message Visit poster's website
mikeexplorer
General User
General User


Joined: 08 Nov 2010
Posts: 6

PostPosted: Fri Nov 12, 2010 3:25 pm    Post subject: MS Access 2000 with OpenOffice 3.2 Reply with quote

I am also a new user to OpenOffice. I have the 3.2 version and want to be able to use my old Microsoft Access Database files. They were created with Office 2000 (Extension .MDB) I can link to the Access files and view them just fine, but I cannot add/update/delete anything. The top menu bar does say "Table data view" which seems to imply the files are open as read-only. I am using Windows XP Professional and right now I no longer have Microsoft Office installed as I had to do a wipe & reload of the computer. I lost Microsoft Office in the process. (but it was an old version anyway)

I have read through some of the documents and help files but some of the things it says when linking to the access database are not options in base?

Mike
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Sat Nov 13, 2010 1:41 am    Post subject: Reply with quote

Quote:
I have the 3.2 version and want to be able to use my old Microsoft Access Database files. They were created with Office 2000 (Extension .MDB) I can link to the Access files and view them just fine, but I cannot add/update/delete anything.

Welcome to the forum.

It is not possible to edit MS Access data from OOo Base but you can read the data. You will need to transfer the Access tables into a new Base file. Generally this raises few problems but boolean fields can cause difficulties. I suggest you refer to the tutorial in another forum to be found at:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=25300
You will not be able to copy forms or reports from Access to Base.

Let us know how it goes or if you hit problems.

Edit 13 Nov 23:29 I forgot that it should be possible to edit an MDB table from Base, but not an Access query which will appear in the Base list of table as a View with a green right pointing arrow on the icon. In any event it should be possible to copy and paste the data from the Access 2000 tables into a new empty Base file.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu


Last edited by Arineckaig on Sat Nov 13, 2010 4:08 pm; edited 3 times in total
Back to top
View user's profile Send private message
mikeexplorer
General User
General User


Joined: 08 Nov 2010
Posts: 6

PostPosted: Sat Nov 13, 2010 4:18 am    Post subject: Reply with quote

My Access database files are simple mailing lists, nothing fancy. I have no forms or reports made in Access. Mainly what I would do with the access database is export it to a dbase file to be read into a label making program. My old computer still has MS Office installed on it, perhaps it would be easier to export the access databases into something else that Base can read in directly? What would be the best way to go about doing that? (What does Base read in the best that Access can export?)

Mike



Arineckaig wrote:
Quote:
I have the 3.2 version and want to be able to use my old Microsoft Access Database files. They were created with Office 2000 (Extension .MDB) I can link to the Access files and view them just fine, but I cannot add/update/delete anything.

Welcome to the forum.

It is not possible to edit MS Access data from OOo Base but you can read the data. You will need to transfer the Access tables into a new Base file. Generally this raises few problems but boolean fields can cause difficulties. I suggest you refer to the tutorial in another forum to be found at:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=25300
You will not be able to copy forms or reports from Access to Base.

Let us know how it goes or if you hit problems.
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Sat Nov 13, 2010 8:51 am    Post subject: Reply with quote

Quote:
Mainly what I would do with the access database is export it to a dbase file to be read into a label making program

My understanding is that OOoBase can read and write to Dbase files, but I have no direct experience of them.
Quote:
What does Base read in the best that Access can export?

Base is fairly flexible and can read from spreadsheets and even text files. For mailing labels OOo Writer works well with data in OOo Calc and you can maintain the data in the spreadsheet and not use Base at all. If, however, your data is simple text and as you already have Base linked to read the Access table, I suspect it will be just as easy to copy and paste the Access tables into a newly created empty Base file as any other method. I suggest it is worth a try: so long as you COPY and paste the tables into the new empty Base file you will lose no data in event of problems. Best to back-up your Access file before you start to be quire sure.

As you will see from the edit to my previous post Access Queries also appear in the list of tables in Base. Queries lack a primary key and may therefore be more difficult to copy and paste into a new Base file.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
mikeexplorer
General User
General User


Joined: 08 Nov 2010
Posts: 6

PostPosted: Sat Nov 13, 2010 4:23 pm    Post subject: Reply with quote

Ah, I didn't think of that. Open a new Base file and copy/paste the Access records into it.
I will have to try that. Ill also see what base can import since I know Access can export this data to a spreadsheet file. Your right, worst case, I can just convert these to a spreadsheet and use it that way.

Mike
Back to top
View user's profile Send private message
mikeexplorer
General User
General User


Joined: 08 Nov 2010
Posts: 6

PostPosted: Sun Nov 21, 2010 7:30 am    Post subject: Reply with quote

I sort of have the problem resolved. I went back to my old computer which still has Office 2000 installed (I didn't wipe that machine out yet) I opened up all my access files and exported them as Dbase files. Brought them over to the new computer and now Base will link to them just like it did with the Access files and I can edit the records. All the dbase files show in one window of the main screen and that is fine. I have my club databases in files based on year. One issue I have is if I have the 2010 database open and the 2011 database open, I would like to be able to copy records from the 2010 table to the 2011 table to save re-typing the record all over again. Access allowed this no problem and the two databases are exactly the same as far as the data that is contained in them (all the same fields) I just have them separated by the year so as each member renews their membership, I used to be able to copy the record from the previous year to the current year and make changes as needed. (if something changed like their email address)

Mike
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Sun Nov 21, 2010 12:03 pm    Post subject: Reply with quote

Quote:
I would like to be able to copy records from the 2010 table to the 2011 table to save re-typing the record all over again

It should be possible to open your OOo Base file at the frame showing the list of tables. You can then copy and paste in that frame any existing table - either to create a copy with a different name by pasting into a blank area of the frame, or to append its data by pasting to an existing table's name.
Back to top
View user's profile Send private message
mikeexplorer
General User
General User


Joined: 08 Nov 2010
Posts: 6

PostPosted: Mon Nov 22, 2010 3:26 am    Post subject: Reply with quote

I mean the ability to copy individual records from one table to another. The tables have the same fields, they are just organized by year. (filename) but the data contained within the table is the same data. Access would let me highlight a record and copy, the go to the other table and paste it in.

Mike



Arineckaig wrote:
Quote:
I would like to be able to copy records from the 2010 table to the 2011 table to save re-typing the record all over again

It should be possible to open your OOo Base file at the frame showing the list of tables. You can then copy and paste in that frame any existing table - either to create a copy with a different name by pasting into a blank area of the frame, or to append its data by pasting to an existing table's name.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2, 3, 4, 5  Next
Page 4 of 5

 
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