Ask Your Question

refresh existing dbf database tables in a macro

asked 2017-09-30 06:38:51 +0200

mtmigs gravatar image

updated 2017-10-01 16:05:32 +0200

I have written a macro that loads data from several dbf files into different sheets of a calc spread sheet. The macro works very well except for one problem, it does not see recent changes in the dbf files. The datasource is an odb file pointing to a directory with a varying number of dbf tables which are loaded by another process. The odb file only see changes when I manually refresh tables (Base View->Refresh tables) using Libreoffice base.

I tried to record a macro in base but the record macro option is grey.

Does anybody know how to refresh tables with a macro?

Here is a little more information about my macro. Here is the code I use to connect to the odb file:

Dim DatabaseContext as Object
Dim Datasource As Object
Dim Connecticus As Object
Dim Stm as Object
Dim Resultset as object

DatabaseContext = createUnoService("")
Datasource = DatabaseContext.getByName("catgamedata")
Connection = Datasource.GetConnection("","")

I was hoping there was some method for the DatabaseContext that would force the update.

I tried adding your (Ratslinger) code and it was working great until I closed Base leaving only Calc running. Then it crashed on the "doc =", the error "Object variable not set". So I tried a few variations:

test_doc = Connection.Parent
test_doc = Connection.Parent.DatabaseDocument
test_doc = Connection.Parent.DatabaseDocument.CurrentController

The first and second left "test_doc =". The third one left "test_doc = Null". It seems that without Base running there is no CurrentController.

Is it possible to accomplish the same update of the database odb some other way? Is there a method (function, procedure, whatever they call object attached routines in this language.) that will force a refresh?

I found this command:


As usual I could not find any information about what "docmd" and "DBRefreshTables" require. Does anyone have any ideas?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-09-30 07:25:27 +0200

Ratslinger gravatar image

updated 2017-10-01 05:21:09 +0200

This works from Base:

sub refreshtables
           REM use this statement from main screen
doc = ThisComponent.CurrentController.Frame
          REM use this statement if running from form
REM doc = ThisComponent.Parent.CurrentController.Frame
dispatcher = createUnoService("")
dim args1(0) as new
dispatcher.executeDispatch(doc, ".uno:DBRefreshTables", "", 0, args1())
End sub

doc = statement depends upon where you are executing it from.

EDIT 9/30/2017 - Corrected, Base need not be open:

Here is code based on your added info:

 Dim DatabaseContext as Object
 Dim Datasource As Object
 Dim Connecticus As Object
 Dim Stm as Object
 Dim Resultset as object

DatabaseContext = createUnoService("")
Datasource = DatabaseContext.getByName("catgamedata")
Connection = Datasource.GetConnection("","")

Rem your code here


If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more


@mtmigs Record macros - first it must be turned on. From menu Tools->Options and then under LibreOffice->Advanced select Enable macro recording. Even with that on, in Base it is not available from main screen but it is at the form level. Wouldn't rely on using that especially in Base.

Ratslinger gravatar imageRatslinger ( 2017-09-30 07:35:08 +0200 )edit

It executes! Unfortunately, it does not seem to update the tables.

Possibly your comment, "This works from Base:" is the problem. The macro that needs to refresh the tables runs in Calc.

mtmigs gravatar imagemtmigs ( 2017-09-30 15:39:00 +0200 )edit

Had a gut feeling you were going to come back with that statement. You can now realize how important it is to be specific in your question. I will edit my answer with the fix.

Ratslinger gravatar imageRatslinger ( 2017-09-30 16:29:38 +0200 )edit

It works great... Oops, well only if Base is running with that database open. Is there a way to not need to have base running?

mtmigs gravatar imagemtmigs ( 2017-09-30 20:09:11 +0200 )edit

Haven't found any alternative yet. Have even tried to open the base file first in macro, process then close base file. Didn't work. Will notify if anything found. This is not a common process.

Ratslinger gravatar imageRatslinger ( 2017-09-30 22:39:00 +0200 )edit

I do not know this version of Basic. The last time I used Basic was in 1980 and it was different. I do know something about objects from C++ and Delphi. Is it possible, if the CurrentController is null, to temporarily create a CurrentController and then delete it after doing the refresh?

mtmigs gravatar imagemtmigs ( 2017-09-30 22:55:36 +0200 )edit

OK. I got something to work but it still involves opening the DB (through macro). Will edit my answer shortly. FYI - Basic in LO is Star Basic, created in 90's by Oracle to go along with Star Office (origination of OpenOffice of which LO branched from). Creating a CurrentController is beyond me at this time. If you even want to begin looking into it, I suggest starting here: AOO Dev Guide also for LO.

Ratslinger gravatar imageRatslinger ( 2017-10-01 01:09:03 +0200 )edit

Finally found answer in this Pitonyak document -Andrew Base. Couldn't find with hunt & peck in MRI. Works fine on this end.

Ratslinger gravatar imageRatslinger ( 2017-10-01 05:19:47 +0200 )edit

@mtmigs Not sure how you are using Base outside of an interim access point. If that is all it is, the above mentioned document discusses (with examples of) direct connection without the need for Base.

Ratslinger gravatar imageRatslinger ( 2017-10-01 06:38:54 +0200 )edit

Do you know anything about this command: docmd.runCommand("DBRefreshTables")

mtmigs gravatar imagemtmigs ( 2017-10-01 15:35:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-30 06:38:51 +0200

Seen: 329 times

Last updated: Oct 01 '17