[Solved] Cannot edit or add data in MsSql
[Solved] Cannot edit or add data in MsSql
I have successfully connected to and authenticated my password with an external MsSql database which I use on my website.
However, through OpenOffice Base I cannot edit the data. The edit data option is greyed out, and I unable to add a new record or edit existing data.
Each table has a unique index field.
Please help - I am really trying to move away from Microsoft Office, but this is a deal-breaker for me.
However, through OpenOffice Base I cannot edit the data. The edit data option is greyed out, and I unable to add a new record or edit existing data.
Each table has a unique index field.
Please help - I am really trying to move away from Microsoft Office, but this is a deal-breaker for me.
Last edited by Hagar Delest on Tue Jun 10, 2008 11:11 am, edited 2 times in total.
Reason: tagged the thread as Solved.
Reason: tagged the thread as Solved.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: help - cannot edit or add data in MsSql
Ok - are you connecting via ODBC or JDBC?
Either way - can you give me the different version numbers ( MySQL, and the connector, and OOo )
Thanks
Either way - can you give me the different version numbers ( MySQL, and the connector, and OOo )
Thanks
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: help - cannot edit or add data in MsSql
Sure thing!
OpenOffice Ver 2.3.1
connecting via ODBC system DSN
using SQL server driver 2000.85.1117.00
I do not know the MS SQL version - it's provided by my ISP and I can't find a version number anywhere in their documentation.
Thanks for your help and quick reply...
OpenOffice Ver 2.3.1
connecting via ODBC system DSN
using SQL server driver 2000.85.1117.00
I do not know the MS SQL version - it's provided by my ISP and I can't find a version number anywhere in their documentation.
Thanks for your help and quick reply...
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: help - cannot edit or add data in MsSql
well, quick doesn't mean much if it doesn't help.
OK - the server is at your ISP...hmm, and they give you interactive access that's not always the case.
However it could be that you have read only access via the internet - I have seen that before. In those cases the ISP usually offers something like phpMyAdmin that you get to from your control panel on the hosting site - but when you do that you are seen as one user ( at localhost ) and when you connect via the internet from your desktop you are actually a different user.
Anyway - i am not saying that it is the issue.
So - one if you know you have write access via the internet then we can look for a problem on the Base side. If you don't know that for sure, then a quick email to the ISP would be the first thing I would do, and ask then if you have write access when connecting like this. If you do no then they will need to make the change on their side. If they say you do, then we can look at some optional settings in the connection string that might help.
OK - the server is at your ISP...hmm, and they give you interactive access that's not always the case.
However it could be that you have read only access via the internet - I have seen that before. In those cases the ISP usually offers something like phpMyAdmin that you get to from your control panel on the hosting site - but when you do that you are seen as one user ( at localhost ) and when you connect via the internet from your desktop you are actually a different user.
Anyway - i am not saying that it is the issue.
So - one if you know you have write access via the internet then we can look for a problem on the Base side. If you don't know that for sure, then a quick email to the ISP would be the first thing I would do, and ask then if you have write access when connecting like this. If you do no then they will need to make the change on their side. If they say you do, then we can look at some optional settings in the connection string that might help.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: help - cannot edit or add data in MsSql
Well, I have always connected the same way (ODBC) via Microsoft Access and have always been able to edit and add records - which was why i checked the Unique index, since I read that OpenOffice won't let you add or edit without one - but since I designed all of these tables to work in tandem I made sure to add an auto-increment index to each table.
Still - I appreciate your help and hope you can point me in the right direction!
Thanks.
Still - I appreciate your help and hope you can point me in the right direction!
Thanks.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: help - cannot edit or add data in MsSql
Ok - first thing to check.
Open the base file, before you open a form or click on tables ( before you connect in other words ) check
Edit > Database > Advanced Options
Make sure that the option "Ignore privileges from the database driver"
That could clean it up for you.
Open the base file, before you open a form or click on tables ( before you connect in other words ) check
Edit > Database > Advanced Options
Make sure that the option "Ignore privileges from the database driver"
That could clean it up for you.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: help - cannot edit or add data in MsSql
Problem solved.
Further info...I decided to check every table in my database (there's a lot) and this problem only affects certain tables
Playing around, I decided ti re-import them into msaccess. I realized the reason why they worked in MSACCESS was that MSACCESS allows you to select a unique identifier. SO that is the definite cause of the problem since OpenOffice does not allow you to choose one.
Apparently when I was setting up these select tidentity fields I neglected to make them a primary key - just an Identity. Since I can't edit field properties after data is present, I have added a dummy key column. Problem solved.
Thanks for you help - and I'm sorry to be the source of the problem (that''s usually the case)
Further info...I decided to check every table in my database (there's a lot) and this problem only affects certain tables
Playing around, I decided ti re-import them into msaccess. I realized the reason why they worked in MSACCESS was that MSACCESS allows you to select a unique identifier. SO that is the definite cause of the problem since OpenOffice does not allow you to choose one.
Apparently when I was setting up these select tidentity fields I neglected to make them a primary key - just an Identity. Since I can't edit field properties after data is present, I have added a dummy key column. Problem solved.
Thanks for you help - and I'm sorry to be the source of the problem (that''s usually the case)
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: help - cannot edit or add data in MsSql
Not at all - that is the idea of the forum - we all learn from each other.
Glad it worked out.
Drew
Glad it worked out.
Drew
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: help - cannot edit or add data in MsSql
follow up - that actually doesn't work since I already have identity fields (I don't understand why an identity column isn't also a key) but now my question is how do I duplicate these tables? I use the value in the auto-incrementing identity field in other tables - I am worried that if I create a new table and copy and paste the data, how can I make sure the data has the same identity value?
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: help - cannot edit or add data in MsSql
well actually you can set a column as a primary key, even if it is populated - alright never done that with MSSQL but I would fall over if you can't ( fortunately this is a carpeted room )..
Can't do so at this minute, but later this evening I can test it with the MS SQL server here to be sure and let you know...it should just need an ALTER TABLE command in the SQL window.
Can't do so at this minute, but later this evening I can test it with the MS SQL server here to be sure and let you know...it should just need an ALTER TABLE command in the SQL window.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: help - cannot edit or add data in MsSql
googled alter table and got my syntax for alter table add primary key...
you are a saint. May good karma rain down from the heavens upon you.
Problem solved. Thank you so much!
you are a saint. May good karma rain down from the heavens upon you.
Problem solved. Thank you so much!