refresh existing dbf database tables in a macro

[mtmigs]

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("com.sun.star.sdb.DatabaseContext")
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 = com.sun.star.comp.dba.ODatabaseSource”. 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:

docmd.runCommand("DBRefreshTables")

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

[Ratslinger]

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("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
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("com.sun.star.sdb.DatabaseContext")
Datasource = DatabaseContext.getByName("catgamedata")
Connection = Datasource.GetConnection("","")
Connection.getTables().refresh()

Rem your code here

Connection.close()

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

[Ratslinger]

@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.

[mtmigs]

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.

[Ratslinger]

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.

[mtmigs]

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?

[Ratslinger]

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.

[mtmigs]

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?

[Ratslinger]

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]

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

[Ratslinger]

@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.

[mtmigs]

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

[Ratslinger]

Was there a problem with the latest answer provided? I haven’t see any.

Also, docmd.runCommand("DBRefreshTables") is part of Access2Base.