We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Connection to odb from macro in standalone form

asked 2019-11-21 10:57:55 +0200

hermanv gravatar image

updated 2020-08-10 00:29:49 +0200

Alex Kemp gravatar image

LO Base 6.3 Firebird embedded on Linux Mageia 6 , odb OLVvS is registered. I have created in my odb a form with a button that calls upon a macro to insert some values in another table than the subject table of the form. This works perfectly OK with the macro inside the odb. Now I want to run this form ad a standalone form (the odt is in the same directory as the odb), and I copied the macro to My Macros. Now when I press the button in the standalone form I get the error "Object variable not set, and I get highlighting on the Datasource assignment. The code goes:

Sub Archiveerlid
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.comp,sdbc.firebird,Driver")
DataSource = DatabaseContext.getByName("OLVvS")
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQuery = oConn.CreateStatement()

etc.... As this is my first attempt at developing an odb application, I'm stuck here, i need some clarification.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-11-21 20:06:47 +0200

Ratslinger gravatar image


Since the Base file is registered, it is not necessary to have in any specific directory.

Change this line:

DatabaseContext = createUnoService("com.sun.star.comp,sdbc.firebird,Driver")


DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

There is also an outstanding bug concerning a connection via macro using Firebird embedded. The data is not saved if the Base file is not open and a save actually done after the update occurs. (see tdf#128607). There is a workaround as stated in the report. After any potential updating of the database and before closing the connection, issue a flush command:


With this the Base file does not need to be opened or saved.

Another comment. Since you are working with a Standalone form, you can set a connection via the form and just use that. No real need for a separate connection. Either will work. Your choice.

edit flag offensive delete link more


Tx, that got rid of this problem, but I'm still not out of the woods. I have now the error "Variable not defined" on the next line

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

I checked the bug you indicated, and took a look at the calc file macro's and followed that example, so rem'd the above command and tried:

oConn =  DataSource.ConnectWithCompletion(DatabaseContext)

But that resulted in the same "variable not defined"

And yes, the form is connected , but if I put the macro inside the form and use:

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

then i get "Object variable not set"

hermanv gravatar imagehermanv ( 2019-11-23 13:26:16 +0200 )edit


Note that notifications are not currently working on the site. Normally would have answered sooner. Only caught by trolling my past responses.

Also should have looked at more of your code than just the line giving you the error. You would call ThisDatabaseDocument if you were operating from Base. That is now replaced by DataSource which is why you create the service.

Therefore this line:

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

should be:

oConn = DataSource.getConnection("","")

That should fix it.

Also note, the ConnectWithCompletion needs additional code. That is used for a dialog to be displayed for entering a user name and password. The lines are:

Dim oHandler As Object : oHandler = createUnoService("com.sun.star.sdb.InteractionHandler")             ' Add Dim oHandler As Object :
Conn = Db.ConnectWithCompletion(oHandler)

where oHandler is the service.

A better sample would have been the spreadsheet at the top of the list.

Ratslinger gravatar imageRatslinger ( 2019-11-24 04:46:25 +0200 )edit

Tx again. Now i have the macro working from within Base. The form refers to the macro assigned to the odb file I really don't need that ConnectWithCompletion statement as I see it now working. Part of the my problem was due to the fact (and it is still somewhat hit and mis for me) that I do not see very clearly what is exactly the scope i'm in when I open Tools - Macro's, and I have to decide where I put the macro (My Macros or in the odb at hand). But the macro now works in the form within Base and inserts records in the table in the odb. But I have exported the form to a standalone form. There I made sure the form Datasource is set correctly, and the form executes a query from the odb , so that should be OK. I copied ...(more)

hermanv gravatar imagehermanv ( 2019-11-24 12:14:29 +0200 )edit


Have taken your code with my changes and using my registered Firebird embedded DB I do not get any error. Here is the code used:

Sub Archiveerlid
    Dim DatabaseContext As Object
    Dim DataSource As Object
    Dim oConn As Object
    Dim oQuery As Object
    DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
    DataSource = DatabaseContext.getByName("JobSitesAndFees") Rem My DB name
    oConn = DataSource.getConnection("","")
    oQuery = oConn.CreateStatement()
    MsgBox "OK2"
End sub

I placed this in the macros for the document because it is only used by this document.. Placing code in MyMacros->Standard makes it available to all LO modules and documents.

If you continue to have problems, it may be best to post a sample of the standalone document. This should be enough for me to work with. You do this by editing your question and attach using the paperclip icon on the upper left toolbar.

Ratslinger gravatar imageRatslinger ( 2019-11-24 19:54:32 +0200 )edit

Note. Added DIM statements for oConn & oQuery also to your original code. Always good practice to define variables. Will run without but is necessary if Option Explicit is defined.

Ratslinger gravatar imageRatslinger ( 2019-11-24 20:12:01 +0200 )edit

As another example, have been working with another user dealing with this from Calc. This has graduated over a number of questions and the current working code can be found in this post -> Calc BASIC + Firebird : How to add flush ?

Ratslinger gravatar imageRatslinger ( 2019-11-24 22:34:27 +0200 )edit

Sorry for not posting earlier, but other things..... I copied your code into the standalone's macros, and that works perfectly, many tx. Now I have a start to get further with these macros.

hermanv gravatar imagehermanv ( 2019-12-01 11:02:08 +0200 )edit

Just to let you know: I've been doing this on my Linux Mageia desktop, and all is OK. But now I copied all files to a Windows 10 laptop (made sure the Tools - Options for macros and database connections was OK) and there I had the issue you referred to tdf#128607. Adding the flush command solved the problem.

hermanv gravatar imagehermanv ( 2019-12-02 14:22:40 +0200 )edit

answered 2019-11-21 16:55:13 +0200

hermanv gravatar image

Part of the problem solved: there are two ","(comma) in the DatabaseContext assignment, which should be "."(period). But now I get "Property or method not found: getByName.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-21 10:57:55 +0200

Seen: 311 times

Last updated: Nov 21 '19