[Solved] Can't Edit Spreadsheet

[crossy]

Hi,

I have created a mailing list using a Calc spreadsheet. Been successfully using it for several years, but today when I attempted to amend an incorrect address I find I am unable to do so. There is no Edit option available, almost seems like I have inadvertently protected the spreadsheet.
Any suggestions would be appreciated, please.
Rob.

[Villeroy]

What happens when you try editing?

[crossy]

Absolutely nothing. Clicking on the cell is futile.

[RoryOF]

In the title bar does your file say "read only"?

[Villeroy]

Absolutely nothing. Clicking on the cell is futile.

And when you type something into the cell after clicking it?
What if you double-click a cell?

[crossy]

Can't type into any cell, double clicking is ineffective.

I've tried to attach a screen grab of the headers.

[Villeroy]

You can not edit a spreadsheet in Base. You've got to edit the underlying spreadsheet with Calc.

[crossy]

Sorry, that doesn't work either. I finish up at the same place.

[Villeroy]

Fix your broken Windows. https://support.microsoft.com/en-us/hel ... s-explorer
Do not open the database document (ending with *.odb). Open the spreadsheet document (ending with .ods).

[crossy]

Not sure why you think Windows is broken?

Can only find .odt and .odb files.

[crossy]

A search of my system has located .ods files for other address lists, but not for the one I'm seeking.

[Villeroy]

A glance on the file explorer of a fresh Windows installation shows that this system is severely broken. You don't know how much confusion on this forum alone is caused by this mess.
Anyway.
Open the odb file as you did in the screen shot. The one which shows the right data in some table.
Now look at the status bar. This is where your data are stored.


Left part shows the connection type.
Right part shows the location where the source data come from.

[crossy]

Does not look right?

[Villeroy]

At least we know now, that your database is completely unrelated to any spreadsheet. At some point you have copied your sheet data into an embedded HSQL database.
Call menu:Tools>SQL... paste and execute this:

Code: Select all

alter table "MailingList" add column ID integer Generated by default as identity;
alter table "MailingList2" add column ID integer Generated by default as identity;

Then call menu:View>Refresh Tables
Now your tables have auto-incrementing row numbers (primary keys). Primary keys are essential for certain features of a relational database. Base can not edit tables of a relational database when there is no primary key on the table.

########################################################

Off topic since you have a working database by now. To the casual reader, this is the easiest approach to generate an editable database from a dumb sheet list:
Save the spreadsheet as a dBase(*.dbf) file in a dedicated directory. This saves only the active sheet.
File>New>Database...
Connect to existing database
Type = dBase
Specify the directory where the dBase file is saved.
Register the database = Yes
Save the database.
dBase is editable without primary key since it is not a relational database.

[crossy]

OK, I'll give that a try and get back to you.
This did start out as a spreadsheet, I don't understand how we got to this point though lol.

[crossy]

Hi, tried your advice and finished up here.

Rob.

[Villeroy]

I've never seen that error message. Nevermind.

Create a new directory.
Copy a table icon (MailingList or MailingList2) from your HSQL database.
Paste into a spreadsheet.
Save the spreadsheet as dBase file in the new directory.
Do the same with the other list if you want.
Connect a new database document to the directory of the dBase file(s) as described above.
Open the database tables and see if the result pleases you. The tables are edtitable unless you saved them in some strange directory where you have no write access (another Windows desease).

Call Tools>Options>Base>Databases
Register the new database under the same data source name as your old data source so your serial letters, label printers in Writer keep on working with the new source.

[crossy]

Thanks for all your help. I'll give that a go.

I'll also print the database out just in case. In the worst case I can just create another spreadsheet.

Rob.

[crossy]

Hi there.
After printing out the existing information, I then copied it all to a new database (.ods) but found that I could not save it in the same folder as the original files because Windows 10 said I did not have permission. This reminded me that in the past I had experienced permission problems with other programmes. So I right clicked the folder I wanted to use (where all the original files were saved) and gave Everyone permission (only my wife and I use this machine). So then, not only can I save the new .ods files in the folder I want, but I noticed that I now have the ability to edit the Base file as the Edit Data button is no longer greyed out. I'll post a screenshot shortly. So it looks like the problem was permissions, and it is solved.

[crossy]

Aoo2.JPG

Hi, tried your advice and finished up here.

Rob.

Got to thinking, I wonder if the reason for this error was the permissions problem?

[crossy]

Tried to execute the commands you suggested again and got a message that the column already exists. All I have done is change the permissions so I'd say that was the reason for the message saying that the database is in read only mode. Looks like the reason for the initial problem was simply Windows 10 Permissions. As I have said, I have had similar problems with other programmes before. I now think that this problem may have been instigated by the Creator's Update which I installed about 2 weeks ago, perhaps it changed permissions. Who knows?
Anyway, thanks for your guidance, I have marked this thread as Solved.

Rob.