[calc]when link to external data, which is libreoffice .odb file, OK button disabled

users
#1

In calc, when I try to insert a link to:

   ~/evansl/prog_dev/LibreOffice/database/products_supplier.odb

from the Insert>Link to external Data...

the OK button remains disabled. How can I link to an existing
database which was created with Libreoffice?

My os infor is:

~/prog_dev/LibreOffice/database $ uname -a
Linux evansl-Inspiron-531 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
~/prog_dev/LibreOffice/database $

My Libreoffice info is:

~/prog_dev/LibreOffice/database $ libreoffice4.1 --version
LibreOffice 4.1.2.3 40b2d7fde7e8d2d7bc5a449dc65df4d08a7dd38

#2

In calc, when I try to insert a link to: [...].odb from the

Link to external Data... the OK button remains disabled.

The help text says you can link to a named range in an HTML, Calc, or Excel file - so apparently not to a database file.

How can I link to an existing database which was created with Libreoffice?

I don't think you can. You can copy and paste (or even drag and drop) a Base table into a Calc spreadsheet, but you then have a fossilised copy, not a link, of course.

I trust this helps.

Brian Barker

#3

Please take a look to documentation:
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide
Chapter 10 Linking Calc Data
Linking to registered data
sources..................................................................................18
Viewing data
sources.................................................................................................................
19
Editing data
sources..................................................................................................................
20
Launching Base to work on data
sources..................................................................................
20
Using data sources in Calc
spreadsheets..................................................................................
20

Miguel Ángel.

#4

On 01/05/14 16:53, mariosv wrote:> Please take a look to documentation:
> https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide
> Chapter 10 Linking Calc Data
> Linking to registered data
> sources..................................................................................18
> Viewing data
> sources.................................................................................................................
> 19
> Editing data
> sources..................................................................................................................
> 20
> Launching Base to work on data
> sources..................................................................................
> 20
> Using data sources in Calc
> spreadsheets..................................................................................
> 20
>
> Miguel Ángel.
>

Thank you very much Miguel. The reference you gave was *much*
better than the help provided by my pressing the Help button
in calc.

Following the instructions in the reference you gave, I was able to
copy the database data to calc. Then I tried OLE linking from calc to
the database. It appeared to work in calc. Then I changed the
database, expecting the changes to be propagated to calc; however, the
libreoffice just closed (both calc and base and the writer which I was
using to read the LibreOffice_Calc_Guide you gave). When I attempted
to reopen the Writer doc, it worked. I closed the Writer doc and
attempted to open the calc document. Both CPU's kept jumping up and
down (high of 100% or around there) and memory use gradually rose to
89% before LibreOffice again stopped.

Have you any suggestion about how to recover the data
(it was a toy example; so, no big deal if I can't
recover) and how to avoid this problem in the future?

BTW, my OS and LibreOffice version were given in my OP.

-regards,
Larry

#5

I was able to open the base file w/o problem.
I'll just delete the calc file and create a new calc
and try again.

I tried finding a bug for this here:

   https://bugs.freedesktop.org/query.cgi

using search strings:

   ole link calc base

then:

   ole link calc

then:

   ole link

but got zero bugs found.

-regards,
Larry

#6

Hi Larry,

In calc, when I try to insert a link to:

  ~/evansl/prog_dev/LibreOffice/database/products_supplier.odb

from the Insert>Link to external Data...

the OK button remains disabled. How can I link to an existing
database which was created with Libreoffice?

OLE linking of an ODB file is not supported (by any of the LO modules).
Your database file is either registered with LO, in which case it is
accessible to Calc/Writer and the other modules via the UNO API, and
whatever db interfaces those modules support, or it is unregistered, in
which case, you have to open a connection to the datasource first (e.g.
via macro) and then use that connection as the starting point for
manipulation of database objects within the module that you wish to use.

Calc supports database ranges obtained via queries in an ODB file, but
the ODB object is not embedded in the Calc document as such.

I don't really understand what exactly you are trying to achieve, so
maybe I have missed something ?

Alex

#7

On 01/06/14 09:02, Alex Thurgood wrote:> Le 05/01/2014 18:32, Larry Evans a écrit :
>
> Hi Larry,
>
>> In calc, when I try to insert a link to:
>>
>> ~/evansl/prog_dev/LibreOffice/database/products_supplier.odb
>>
>> from the Insert>Link to external Data...
>>
>> the OK button remains disabled. How can I link to an existing
>> database which was created with Libreoffice?
>
> OLE linking of an ODB file is not supported (by any of the LO modules).
> Your database file is either registered with LO, in which case it is
> accessible to Calc/Writer and the other modules via the UNO API, and
> whatever db interfaces those modules support, or it is unregistered, in
> which case, you have to open a connection to the datasource first (e.g.
> via macro) and then use that connection as the starting point for
> manipulation of database objects within the module that you wish to
> use.
I've registered the database, and can view the tables in calc.
>
> Calc supports database ranges obtained via queries in an ODB file, but
> the ODB object is not embedded in the Calc document as such.

That's unfortunate.

>
> I don't really understand what exactly you are trying to achieve, so
> maybe I have missed something ?
>
> Alex
>

No, you haven't missed anything because I never specified my untimate
goal. The intermediate goal of embedding the .odb in the calc was to
allow using the query abilities of base together with the ilp solving
abilites of calc to find an invoice containing the best supplies to
satisfy the demand for a set of products. Now I could 1st enter the
data in the database, then open the calc and then copy the data from
the database into calc, and then find the best invoice using calc
solver; however, it would be more user friendly if all could be done
just from calc, which is what I thought embedding might do.
The reason I thought embedding would provide this convenience is
because of this quote:

   OLE objects can be linked to a target document or be embedded in the
   target document. Linking inserts information which will be updated
   with any subsequent changes to the original file, while embedding
   inserts a static copy of the data. If you want to edit the embedded
   spreadsheet, double-click on the object.

from page 25 of:

https://wiki.documentfoundation.org/images/7/75/CG4110-LinkingCalcData.odt

(Thanks again to Miguel for that link).

Thanks Alex for the info that and .odb can't be embedded into a .ods.

-regards,
Larry

#8

After a little experimentation, I can get closer to what I want.
In calc, right clicking on the query, and selecting 'Edit
Database File...', I can change the database and save the
changes. Then, back in calc, I can disconnect and the reconnect
to the database, and the changes appear in calc.

-regards,
Larry

#9

Even better is the method described here:

https://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=18511

which is just use the menu:Data>Refresh Range,
which updates the calc cells with the just change database
query; however, the 'database source window' is not
refreshed.

-regards,
Larry

What's strange, thought, is the