# refresh existing dbf database tables in a macro

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?

edit retag close merge delete

Sort by » oldest newest most voted

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:

 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()

Connection.close()


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.

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

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

( 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?

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

( 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?

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

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

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

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