Run SQL in the current Base database

[VirtualVAT]

I can find many references to the UNO service which does that like this one [Solved] LibreOffice Base - Run SQL in a Macro (View topic) • Apache OpenOffice Community Forum but I need to connect to the current Base’ database to run SQL exacly there. There is one answered question in the past LibreofficeBase: how to execute SQL from BASIC script but in my case with the latest LO 6.3.3.2 the system reports an error “ActiveConnection property or method not found”.
How can I use now the current Base’ DB connection to execute the SQL?

[Ratslinger]

Hello,

Have posted a number of examples on this site using SQL in macros. However, many times new users of macros (especially concerning databases) do not fully understand that depending upon where the macro is run from, the syntax may differ. Now in the second link mentioned, if this is run from the IDE of Basic there is no error. If, however, you run this say from a push button on a form you will get the error.

So with the code from the noted post and run from IDE, there is no error:

if IsNull(ThisComponent.CurrentController.ActiveConnection) then
    ThisComponent.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute("SOME SQL COMMAND")

Now to execute from the form you need to go one step up:

if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
    ThisDatabaseDocument.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("SOME SQL COMMAND")

or another method:

if IsNull(ThisComponent.Parent.CurrentController.ActiveConnection) then
    ThisComponent.Parent.CurrentController.connect
endif
Dim oStatement As Object
oStatement = ThisComponent.Parent.CurrentController.ActiveConnection.createStatement()
oStatement.execute("SOME SQL COMMAND")

For a more complete explanation on this, see my answer in this post → ThisDatabaseDocument vs ThisComponent?

[VirtualVAT]

Thank you very much, Ratslinger, it works and your link was also very useful.