I deleted a field from a table, which was referenced in a query. I got the error and I exit Base without saving the changes, but from that moment on I cannot use BASE anymore, because I always get the following message:
“Impossibile creare il collegamento alla sorgente dati “DBNAME”.” (impossible ti create the link to datasource “dbname”, followed by the text of the failing query) whatever I do.
I would like to create again the field in the table or clean all references in the query, but I cannot access tables or queries. I’m stuck and all data are not accessible anymore.
How can I resolve? And how can I retrieve my data elsewhere?
I set the flag fo keeping copies, but my last copy is 6 months old and a lot of data are not there.
Why cannot I enter the configuration of the tables? The problem only affects one query…
Database files weren’t saved by backup function of LO.
If you have created an internal database with LO you have to backup this file by your own system. I have created a macro here to create backup every time I open the file. So I could get back to the data before last opening the file.
A query won’t run, but the tables should be there without the query. A query has been created in GUI of Base and nothing to do with the connection o the GUI and the database.
So: Do you use interne database (HSQLDB or Firebird) or did you connect to an external database?
You could send me a copy of the Base file by mail function of ask.libreoffice so I might have a look if I could resolve the problem of an internal database.
I use HSQLDB. Unfortunately I cannot share the Base file, because it contains sensible data…
In my case, the problem is in a query (because I search for a field that has been deleted) and in the form that is built on it. It would be enough to me to access the section dedicated to tables: I would reintroduce the field so that query and forms would become consistent, but I cannot access tables section at all because of that message as well as I cannot access query details to delete conditions.
I also did the following: I created a copy of the file: I deleted all queries and all views. Then, when I try to enter the tables section, I still get the error based on the query containing the deleted field. Umbelievable. There something dirty somewhere, but where?
Extract from the message:
“File script errato linea: 81 Colonna non trovata: s.Detraibile in statement [ SELECT
“ImmobileID”
, “AnnoDiRiferimento”
, SUM( “Spesa” ) AS “TotSpese”
, SUM( “InteressiSpesa” )”
About LO, I always find this message in file updating.log:
Update Check Time: 2116610
Update check: https://update-mar.libreoffice.org/update/check/1/LibreOffice/87fa9aec1a63e70835390b81c40bb8993f1d4ff6/Windows_X86_64/LOOnlineUpdater
Download: https://update-mar.libreoffice.org/update/check/1/LibreOffice/87fa9aec1a63e70835390b81c40bb8993f1d4ff6/Windows_X86_64/LOOnlineUpdater
warning: error by the updaterdownload did not succeed
What happens when you open the database file? There isn’t any query executing. So you should be able to connect to the tables folder. If you can’t connect to the tables folder and there are no tables visible the reason for this behavior isn’t the query. The connection between the database and the Base file has been destroyed.
You have got an error of the file script, which is a special thing of HSQLDB. Might be it is the script, which is inside the database file.
You could try to open Tools → SQL.
Try to execute
SHUTDOWN SCRIPT
there.
Close the database file and open it again.
All other messages you reported are messages from the automatic updater. You could deaktivate the automatic update in Tolls → Options.
Just a second before, i tested to remove a field from a table. Closed the .odb file and had no problem to reopen it later. So something strange happend to your file…
When I open the .odb file, Base opens without messages/errors.
If I select TABLES, a pop-up appears with the error message:
<Impossibile creare il collegamento alla sorgente dati “name”.>
followed by the SQL text that contains the reference to the deleted field (“Detraibile”):
<File script errato linea: 81 Colonna non trovata: s.Detraibile in statement [ SELECT
“ImmobileID”
, “AnnoDiRiferimento”
, SUM( “Spesa” ) AS “TotSpese”
, SUM( “InteressiSpesa” )
…>
But I deleted that script!
Anyway, the application doesn’t let me enter the section, so I cannot see the table name and, consequently, I cannot restore the missing field, by example.
Exactly the same happens if I try to open TOOLS/SQL: same message and access to that function is denied.
Similar behaviour happens if I try to enter QUERY section: the message appears, but if I close it I can enter the section and see the queries: I can delete them (and in fact I did it) but I cannot open them and modify (same pop-up).
The error is inside the script file of HSQLDB.
Do this with a copy of your file:
Open the *.odb-file with a packing program.
Go to folder database inside this file.
Open the file “script” in folder “database” for editing.
Have a look at the message you have got. Might be you could delete the whole SQL-Code for this selection. Might be you only have to delete the non existing field…
Save, what you have edited.
Try to start the copied file wit LO.
I’ll search for an adequate packing program to test your suggestion.
Meanwhile I found out an old note I took when I developed the last updates. It may be (I cannot access the proper session, so I cannot prove it) the query is also a VIEW (I mean: the query was also recorded as a view). This would match the fact that I still see the issue despite I deleted all the scripts. If this was the case, do you have a better and faster solution?
Deleting of a column in a table should never be possible if the field is used by a view. The database will refuse this. Did the database crash while creating a query?
Starting condition:
Table with field, view based on multiple tables, including that field, equivalent query in QUERY section: all working fine.
Then:
I deteletd the field from the table: I got an error because deleting that field was not permitted, I acknowledged the error and I closed BASE by replying NO to confirmation of modifications.
From that moment on I’m not able to access tables, views, queries text and forms exec anymore.
Then: Edit the “script” file inside the *.odb-file.
Here, with Linux, I’m using Ark (Unzip) and Kate (Text editor) for such a task.
Great, Robert! I copied the file into Linux env, renamed it as ‘.zip’, executed unzip, went into dir ‘database’ and substituted occurrences of the deleted field with an existing one (I used ‘vi’) in the VIEW definition. Then I saved, compressed, renamed as .odb and moved back the file to Windows.
Now I can see all my data, tables, views, scripts and forms.
Thank you very much for definitive and this easy-to-use solution!
Daniele