Base: Insert Record disabled in query

[dave31175]

Version 6.0.7.3

I only recently discovered that Base exists, and decided to tinker with it a bit to see if it might replace an MS Access database I’ve been using for years. I was able to get my current data (3482 records) imported into a table and can use Insert>Record to add data while in the table.

I then created a query to sort the data and rearrange the columns to my liking. However, I cannot add/edit data while viewing the query. Insert>Record is disabled. Why is this? and what do I need to do to be able to add/edit data in query view? (And secondarily, why isn’t column resizing in queries retained? Seems that should be pretty basic…)

Thanks

[Ratslinger]

Hello,

See no problem in testing. Simply add new record on new line at end of query list. Do NOT see in Query where you find Insert>Record. This IS in Table view though. Please provide more info as problem cannot be found based on what was provided.

You have stated imported into table, and you started from Access, but what DB are you using? The SQL being used may help. Are you actually in a query or possibly a form using the query?

[dave31175]

Thanks for your reply.

https://photos.app.goo.gl/3FnFW9ZD5qrhDHGf8
This image shows Insert>Record menu item in query view. It is disabled. When I go to the last line in the query list I cannot move down to a new line. I also cannot type in any of the fields to edit them. The query view is read-only, and I don’t know why it is or how to change it.

What DB am I using? In Base, File>New>Database>Create a new database gives me only the option “HSQLDB Embedded”. Is that what you’re asking?

[dave31175]

From Access, I exported my table to Excel. I created my new database in Base. I selected all data in my Excel sheet, then in my database, on the Tables tab I did a right-click>paste. I set data types for my fields, then viewed my table. I found that it was read-only and googling produced results that told me it was because of pasting from Excel. I copied/pasted the table to a new table and then was able to add/edit/remove data in the table. I repeated the process for a 2nd table. I then used the query wizard to create the query pictured in the link above, but am not able to add/edit/remove data when viewing the query.

[dave31175]

The SQL for the query is simple:

SELECT “Watched”.“title” AS “title”,
“Watched”.“date” AS “date”,
“Source”.“desc” AS “source”,
“Watched”.“imdb” AS “imdb”,
“Watched”.“director” AS “director”,
“Watched”.“year_” AS “year”,
“Watched”.“d_rate” AS “d_rate”,
“Watched”.“k_rate” AS “k_rate”,
“Watched”.“rate” AS “rate”,
“Watched”.“comments” AS “comments”
FROM “Source”, “Watched” WHERE
“Source”."." = “Watched”."." ORDER BY
“date” ASC, “Watched”.“title” ASC

[Ratslinger]

Hello,

This answer is somewhat of an educated guess.

First I stand corrected on the Insert>Record notation. Don’t recall ever looking at that in the different views until your question. This is not presented when running the query in a query edit mode which is where I do most of my SQL (direct code).

Here is a starting table:

Used the Wizard & created this query:

You can see the last line available for entry. However, if the key field (here it is ID TEST) is removed:

now there is no entry for a new line. Nor can a record be edited or deleted. It has no way of knowing where this record is since the key field is missing. I imagine this is where your problem lies.

[dave31175]

Thank you very much! Your educated guess was correct. When I created a new query including all fields in the table, I can add/edit/remove data.

I appreciate your help. I’m far from a database expert, but have used Access for long enough to make it do what I want and kind of know how it “thinks”. I obviously haven’t yet figured out how Base “thinks”!

[Ratslinger]

@dave31175 Keep in mind, including all fields is not always necessary. The critical part here is that the key field is needed to find the record. Know also that Base is a front end to a database. LO includes HSQLDB embedded (and Firebird embedded with Experimental Features enabled) but you can also use Base to connect to a variety of other databases. So it is not only what Base may do but also what the database may do.

[dave31175]

It does make sense now that the primary key field would need to be included. I successfully created a data grid form based on my query in which I’m able to add/edit/delete data. I do have a question about the primary key “ID” field. Is there any way to make it an auto-numbering field so it always by default inserts the next available integer, rather than my having to enter it for every new record?

[Ratslinger]

Yes. Edit the table definition (right click table name & select Edit). Select your key field. Then under Properties (lower part of edit) Change AutoValue from No to Yes. Save.

Seems the LO documentation may be of help to you. See this page → LibreOffice Base Handbook

[dave31175]

Thanks for this. It apparently won’t let me change it now (“SQL Status: IM001 Changing autoincrement property of existing column is not supported”), but I’ll keep this in mind for future tables. Thanks for the link – I’ve saved it for future reference.

[Ratslinger]

Do not recall seeing this in HSQLDB but rather in Firebird. In HSQLDB you can also change an ‘int’ field (one per table and it’s part of the key) to auto increment with this SQL:

ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" INT GENERATED BY DEFAULT AS IDENTITY;

This must be done through menu (from main screen) Tools->SQL...

[dave31175]

I updated my LO to 6.1 (as 6.0 didn’t have conversion from HSQL) and converted my DB to Firebird, hence the error trying to change autoincrement. I had to change the syntax of some of my queries, especially regarding date statements, however I think I’ve got it sorted and pretty much have everything I need that I had in my MSAccess DB. Thanks again for all your help

[Ratslinger]

Please note, when dealing with Base it is not only important to specify which LO version & OS you are using but also the specific DB you are talking about. As of this question it was HSQLDB and you may see how it can add confusion.

It is certain, as you have already started to see, you may have further Firebird questions. Please ask as new questions so other may also benefit.