Unable to change Key ID

English
#1

Hi I am using: Version: 7.1.6.2 / LibreOffice Community
Build ID: 0e133318fcee89abacd6a7d077e292f1145735c3
CPU threads: 4; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: en-GB (en_GB.UTF-8); UI: en-US
Calc: threaded

I have tried (and failed) to IMPORT an Access database which was made up from a linked CSV multiple tabbed spreadsheets. However, I have copied each sheet into Base to create my tables.

My problem now is I cannot change the primary key ID key to one of the linked cells. I get the following: errors

  • The column “Haz Ref” could not be changed. Should the column instead be deleted and the new format appended?

  • Attempt to insert null into a non-nullable column: column: Haz Ref table: CAUSES in statement [ALTER TABLE “CAUSES” ADD PRIMARY KEY (“Haz Ref”)]

  • Error: SQL Status: S1000

  • SQL Status: 23000

  • Error code: -10

  • Attempt to insert null into a non-nullable column: column: Haz Ref table: CAUSES in statement [ALTER TABLE “CAUSES” ADD PRIMARY KEY (“Haz Ref”)]

Any helps hugely appreciated
Many thanks
Gerry

PS: Is there a way to have a linked spreadsheet in CSV format automatically update as well?

#2

Hello,

You do not specify what database you are using. A guess is that it is HSQLDB embedded.

The error indicated that Haz Ref has NULL values in it. Since you are attempting to make this a primary key, Nulls or duplicate values are not allowed. Correct first.

As you state you created tables from the sheet data, what is this meaning of linked cells?

Needs further clarification of just what you want. Base can use data in a spreadsheet or text files or tables but not mixed. If you are looking for a method to create CSV files from tables, that is possible also.

#3

Yes it is a HSQLDB as thats the only DB option I have.
Regards the values in the Haz Ref, there are values in each field.

I copy and pasted them in as importing the spreadsheet would not work.

OK, I work in a spreadsheet that “ideally” is linked to the database an automatically updates, that how it works in Access, but as I am using a MaC I am trying to get as close to the original solution.

I’ll restart the import again (copy) the spreadsheet in.
Is there a way to change the dtabase from what it is to something else?

Thanks in advance
Gerry

#4

You will have the same problem with MySQL, PostgreSQL, MS Access (JET db engine), SQLite, Firebird etc. A spreadsheet is not a database.

  1. Create an empty database with the right column column types (as small as possible, as big as necessary, no nullable columns), add indices and relations.
  2. Test your spreadsheet for consistency and fix any inconsistencies.
  3. Copy the carefully prepared spreadsheet data into the carefully prepared database structure. Copy and paste in the right order: 1-sides first, then the dependent n-sides.

In order to avoid failing or incomplete import, do the following tests in the spreadsheet and edit your data on sheet until they fulfill all the rules of your database structure:
=MAX(LEN(column)) [entered with Ctrl+Shift+Enter as array formula] returns the max. length of a text column for the right size of a VARCHAR field.
=COUNTBLANK(column) counts blanks. There should be no blanks in a mandatory column.
=COUNTA(column) counts any values text or number.
=COUNT(column) counts numbers
=COUNTA(column)=COUNT(column) TRUE if there are only numbers.
=ROWS(column)=COUNT(column) TRUE if there are only numbers with no blanks.
=MIN/MAX(column) min and max values of a column to determine the right numeric type.
=N(MATCH(value;column;0)) returns 0 if value does not occur in some other table’s column (test for referencial integrity).
“column” should be a reference like A$2:A$1001 if you have column labels in row #1 and 1000 records below the labels.
=COUNTIF(value;same_column)>1 returns TRUE if this row’s value has duplicates in the same column.
Date/Time numbers need to be formatted accordingly. ISO date/times are allowed to be text. Text value “2021-09-17 23:45:59” fits properly into a database column of type TIMESTAMP, but not text value “9/7/2021 11:45 am”

How do I change the order of fields when I edit a table? base 5.0.3.2
#5

Andreas - thank you for your detailed and v helpful response.

I probably should give a tad more context to what this database is and its original data source.

I am taking over a role that is required to keep a register of data points around hazards and risks. It’s a prescribed dataset, of which not all fields are required in all cases but 70% are. The output is a report, and in a spreadsheet format - it is impossible to read it as is because of the structure. The alternative is a Word/Writer style document. equally difficult to read. My predecessor has developed an Access database and I’ve tried importing that (from raw spreadsheet) to no avail.

So, I have completed this:

> Copy the carefully prepared spreadsheet data into the carefully prepared database structure. Copy and paste in the right order: 1-sides first, then the dependent n-sides.

Can I check - what is 1-sides first mean?

That said I copied the WHOLE sheet where some columns will not be used, so I think what I’m understanding is to install what is ONLY required for the report (output of this), OR is it possible to indicate to Base what is a mandatory column and if so, how?

These are entered in as pure dates 01/02/1923 currently

Lastly, as this will be a live document as in refreshed every 2-3 weeks, adding additional data from the spreadsheet (especially if I must have data in every column which won’t be possible because of the nature of it) I should append data in? or whats the best approach? Double-entry i.e into the spreadsheet and the database is probably not wise, a risk of missing someting.

Many thanks
Gerry

#6

The purpose of a database is to keep data so you can use them with different software. Connect a Base document to your Access database and keep on using it. On Windows this should be no problem at all. “ucanaccess” is an alternative Access driver for all platforms.
Embedded HSQL (or embedded FIrebird) is a bad decision anyway. Embedded HSQL can be a step stone to get your data out of spreadsheets. But you should not use embedded databases for serious work.
If you want to change databases with the help of Base:
Connect one database to the Access DB and another one to your MySQL or PostgreSQL server, to your SQLite file, Firebird, embedded or standalone HSQL, H2 DB or whatever.
Copy the table icons from the Access database to the other.
Finally, rebuild relations and indices.
Spreadsheets are just another obstacle because sheets do not keep the meta information about your database oolumns.

#7

That’s the problem I can’t! I cannot get the access file in anyway. I had hoped Base would be a good alternative but it’s proving to be.

The ONLY option I have in creating a database on my version is HSQL - should I have other options? The access file I have with all the connections is a .accsbd and I’ve cearched all ways to get this in to no avail.

#8

Then I would suggest that you stick to Access. It is not the worst MS product.

#9

What I should have said, the reason I’m needing base is I’m using a Mac!

#10

Well, then get a Windows PC with Access and see what Access can do for you. There is no reason why the target system should cope with 100% of the transition.
As far as I remember after 20 years, Access can export raw data, SQL scripts, reports with meta-data.

I can open and edit Northwind.mdb on Linux and I have tested some accdb file in the past. Without having your database on my disk, it is impossible to tell what can be done and what not. I know next to nothing about recent versions of Access.

#11

Thanks, I’ve downloaded Parallells and see if that will allow me to use my Office365 version of Access.
Many thanks
Gerry

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.