Can I use the command line to open base and run a macro?

[MeghansUncle2]

I need to perform a background task that will open an Access DB using ODBC from base, execute one of the Access select queries, and return the result set to a CSV file. Is this possible using Open Office? If so, where do I begin to look for help with the solution. I mean documents, I can figure it out If I get a jump on where to look.

Also, I need to do the above without MS Office installed. Can Open Offices' Base perform the required work without MS Office being installed?

[UnklDonald418]

If you are new to Base, I recommend using LibreOffice rather than OpenOffice, because the Base module in OpenOffice hasn't seen any updates for over a decade.
https://www.libreoffice.org/

You can assign a startup macro by selecting the following from the main Base menu:
Tools>Customize>Events>Open Document> Macro
and select the macro you want to run

When you first open Base select the Open existing database option
From the list select Microsoft Access to connect to the tables in a .mdb file
Or Microsoft Access 2007 to connect to the tables in an .accdb file.
You can query the tables, but it must be a Base query which may have slightly different syntax than an Access query, but if your familiar with SQL it shouldn't be too difficult to get working.

Base is a small front end for a HSQL database, and doesn't have any tools for exporting to CSV, however the HSQL back end can work directly with CSV.

To communicate directly with the HSQL back end select the menu option Tools>SQL. This bypasses the Base front end.

Here is an example, creating a text table with a few fields and connecting that table with a .csv file

Code: Select all

create text table "MyTableName" (
"tdate" Date,
"Field1" VARCHAR(50),
"Field2" DECIMAL(10,2),
"Field3" VARCHAR(20)
);
SET TABLE "MyTableName" SOURCE "SomeFileName.csv;fs=,;ignore_first=true";

When that has successfully Executed, in the Tables area of Base select View>Refresh Tables to inform the Base front end of the changes and your new table will be shown in the list of tables, of course with no data.

To populate the table Execute something like

Code: Select all

Insert into "MyTableName" ( "tdate", "Field1", "Field2", "Field3")
Select "Date", "FieldA", "FieldB", "FieldC"
FROM "NameOfAccessTable" 

Select Save and the data should be written into the .csv file.

The OO/LO API is very different from the Access API. It's quite complex and opaque , so this is the macro code I used in my test database named TestCSV

Code: Select all

REM INSERT RECORDS INTO DATABASE TEXT TABLE
SUB UpdateCSV()
Dim Context
Dim DB
Dim Conn 
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("TestCSV")
Conn=DB.getConnection("","")

Stmt=Conn.createStatement()	
strSQL= "INSERT INTO ""MyTableName"" ( ""tdate"", ""Field1"", ""Field2"" , ""Field3"") Select ""Date"", ""FieldA"", ""FieldB"", ""FieldC"" FROM ""NameOfAccessTable"" where ""FieldA"" = 'DITECH'" 
Stmt.executeUpdate(strSQL)

Conn.close()
End Sub

Each time I run it, a list of records are added to the csv file. Since this macro is highly dependent on the database, it is best to save the macro with the database, rather than a global library like My Macros.

A resource I used to get the csv connection
http://www.hsqldb.org/doc/1.8/guide/
Look here for macro programming information. There is a book that is a free download that is pretty much the bible on OO/LO macros, unfortunately it doesn't cover the Base module.
http://www.pitonyak.org/oo.php

[MeghansUncle2]

I will try this and post results. Probably will take me a while to process this information.

Thank you so much!!!

[F3K Total]

Hello,
i tried the proposal from UnklDonald418, wasn't successfull, because the .csv, written by my HSQLDB, contained lot's of empty rows, when i opend it with an editor or Calc.
Any try to use e.g.CHECKPOINT DEFRAG didn't work on that .csv TextTable.
But the question was, if one could

perform a background task that will open an Access DB using ODBC from base, execute one of the Access select queries, and return the result set to a CSV file.

I think, i have found a solution for that, but it's a bit complicated.
First in words:

• a batch file containing the path to Commandline_extract_CSV.odb document, which is linked to an access database must be startet.
• the batch file asks for the input of a parameter, in my test it were the beginning letters of the surname from the persons of a contacts table
• after input of the letter/s the batch starts OpenOffice in background, and gives two parameters to Openoffice
  1. the path to Commandline_extract_CSV.odb
  2. the beginning letters of the surnames
• the local Office Suite contains a starbasic macro, named CreateCSV_ACCESS, located in macro library Standard, there in Module1.
• OpenOffice will now load the .odb file and the macro CreateCSV_ACCESS contains an area which does the parameter query on the database, opening an output file named OUT.CSV and writes this file.

find here the batch file

Code: Select all

@echo off
set /p V1=enter first letters of Surname:
ECHO.
set sOfficepath=C:\Program Files (x86)\OpenOffice 4\program\soffice.exe
set sFilePath=C:\Users\<USERNAME>\Desktop\CommandLineCSV_Export\Commandline_extract_CSV.odb
REM the FilePath is given to the Starbasic Macro as a parameter, so OpenOffice will load the database in background
set sMacropath=macro:///Standard.Module1.CreateCSV_ACCESS(%sFilePath%,%V1%)
REM another way would be to load the database using this batchfile, but then OpenOffice will appear
REM "%sOfficepath%" "%sFilePath%" "%sMacropath%"
ECHO "%sOfficepath%" -invisible -norestore -nologo "%sMacropath%"
"%sOfficepath%" -invisible -norestore -nologo "%sMacropath%"
ECHO Ready, all rows with Surname, beginning with "%V1%" written to File OUT.CSV!
PAUSE
exit

and the starbasic macro

Code: Select all

Sub CreateCSV_ACCESS(sUrl,sFilter)
    sUrl = converttoUrl(sUrl)
    dim args(1) as new com.sun.star.beans.PropertyValue
    args(0).Name = "Hidden"
    args(0).Value = True
    args(1).Name = MacroExecutionMode
    args(1).Value = 4
    oComponent = StarDesktop.loadComponentFromURL(sUrl,"_blank", 0, args)
    sURLFolder = Replace(oComponent.URL,oComponent.Title,"")
    oDatasource = oComponent.Datasource
    oConnection = oDatasource.getConnection("","")
    oStatement = oConnection.createStatement()
    sSQL = "SELECT `ID`, `Vorname`, `Nachname`, `Strasse`, `PLZ`, `Ort` FROM `T_Contacts` WHERE `Nachname` like '" & sFilter & "*'"
    oResult = oStatement.executeQuery(sSQL)
    nColumns = oResult.Columns.Count - 1
    sURL = convertFromUrl(sURLFolder & "OUT.CSV")
    f = FreeFile()
    Open sURL for Output as #f
    dim aRow(nColumns) as String
    dim sRow as String
    nCounter = 0
    while oResult.next
        for i = 0 to nColumns
            aRow(i) = oResult.Columns(i).getstring
        next i
        sRow = join(aRow,";")
        print #f, sRow
        ncounter = ncounter + 1
    wend
    Close #f
    oConnection.close
    oComponent.close(true)
    'stardesktop.terminate
End sub

R