Page 1 of 1

VBA and Base – the Access2Base Extension

PostPosted: Sat Mar 17, 2012 12:39 pm
by Arineckaig
Many newcomers to Base who have previous experience of VBA find the transition to the OOo API less than comfortable. This issue was discussed in a recent thread on “Programming Resources” at

I would draw attention to a most interesting utility for VBA programmers mentioned in the other forum at

An extension by Jean-Pierre Ledure called “Access2Base” has recently been posted and can be found at
with full documentation at

Its aim is stated as:
It is intended first to support people having a knowledge of MSAccess and willing to step over to a similar but free software, i.e. OpenOffice/LibreOffice Base. It is also useful for users having already a practical knowledge of OpenOffice/LibreOffice and want to start building applications with it, while remaining focussed on the application or business logic only.
It is also recommended to people having tried to program in OOo/LibO Basic with the standard OpenOffice/LibreOffice API and having given it up ... “

Having deserted VBA with Access 97, I am quite unqualified to express an opinion on the merits of the implementation of this aim, but at first sight it shows every promise of being the route that VBA programmers should investigate. In any event, IMHO, it is a most remarkable piece of work.

Re: VBA and Base – the Access2Base Extension

PostPosted: Sat Mar 24, 2012 8:11 pm
by cazbym
I have just posted this on the "other forum", but thought I would repeat it here.

This is wonderful.

From Forum

The solution is based on an extension I posted a few days ago. It's called Access2Base and you can find it on ... ccess2base.
Full documentation is available on

How to proceed ?

Install the extension
Download the example database from ... Record.odb
The code is quite concise

I am having a problem with this which I'm sure is something silly I have done.

I have successfully migrated to hsqldb2 to protect against data loss, almost a year ago, so have had to download, alter to zip extension, extract and connect to script file. I have also downloaded the Access2Base extension. I have copied the macro from the embedded database and transferred forms across into the newly created database, which I have renamed DBEX.odb. So I now have your macro in DBEX.Standard.Module1 and the Access2Base macros in My Macros.

The tables show up properly. However, when I press the button nothing happens.

What am I doing wrong?

I'm rubbing my hands with glee at the thought of getting this working!

Re: VBA and Base – the Access2Base Extension HSQLDB

PostPosted: Sun Mar 25, 2012 5:04 pm
by cazbym
Well I think I have found the cause of or at least the general area of my problem.
There is a macro which has to be assigned to the open document event of my database. I have assigned it (wrongly I believe) to the .odb file, but think I should be assigning it to the actual database file, which is not an OpenOffice document as such.

I know where my database files are, but have no idea how to assign this macro to them or (it).

As far as I can make out the macro DBOpensimply loads a library of macros on opening the database.

Can anyone tell me how to do this, or at least if I'm on the right track.

Re: VBA and Base – the Access2Base Extension

PostPosted: Fri Mar 30, 2012 3:38 pm
by JPL
- Access2Base is installed as a Basic library in the My Macros container
- the location of the database itself does not matter
- a short routine has to be stored in the database document itself (the "odb" file) and linked to the OpenDocument event of the database.
See the documentation extract below or see more details in
The above (other post) mentioned can serve as an example. Look at the Basic code in the file.
Hoping this will help.

Open the ".odb" file (the database document) in the main OOo/LibO Base window.
With Tools + Macros + Organize Macros + OpenOffice[LibreOffice] Basic... open the Basic IDE and create a Basic module in the Standard library of the database. The module should contain as a minimum next code:
Code: Select all   Expand viewCollapse view
Sub DBOpen(Optional poEvent As Object)
   If GlobalScope.BasicLibraries.hasByName("Access2Base") then GlobalScope.BasicLibraries.LoadLibrary("Access2Base")
   Call OpenConnection(ThisDatabaseDocument)
End Sub

Assign in the main Base window with menu items Tools + Customize... (Events tab) the above Sub ("DBOpen" in the example but use the name of your choice) to the OpenDocument event. Save in the ".odb" file itself.
Close and reopen the database document (".odb") to trigger the OpenDocument event. If no error message, then OK.
Start programming macros.

Re: VBA and Base – the Access2Base Extension

PostPosted: Fri Jun 15, 2012 2:39 pm
by pastim
I am in the process of moving off Windows to Ubuntu, and Access is one of my key issues. This extension is great, and although I have only just started moving one of my applications, it has made the process much much easier. As usual these days, the problem with learning something new is that there is too much information, rather than too little, and finding the right information at the level you need is really hard.

All I really need to do is some fairly simple form and control manipulation, with some SQL thrown in. I haven't yet found a source of information that gives me some simple examples in Base Basic from which I can learn. Access2Base has therefore been a lifeline.

I have had two minor problems (I would give line numbers if the Basic editor showed them, but it doesn't - I wish it would....).

1) When attempting to access a listbox control using Controls, it threw a trace error when nothing was currently selected. The cause was in the case entry Case "STRINGITEMLIST". I had to change this as follows adding the If statement in the line following the comment

Code: Select all   Expand viewCollapse view
                        ' Check ValueItemList
                        bListboxBound = _ListboxBound(ocControl)
                        If bListboxBound Then                        
' Changed by Pastim on 14/6/12 - set value only if something selected (i.e not -1)
                           If ocControl.ListIndex > -1 Then
                              ocControl.Value = ocControl.ControlModel.ValueItemList(ocControl.ListIndex)
                           End if   

2) I cannot set the value (using SetValue) of a checkbox (I've had no problem with SetValue on other control types). All goes well until right towards the end of _setProperty in the statements:

Code: Select all   Expand viewCollapse view
         If _CheckProperty(oModel, "DataField") Then
            If Not IsNull(oModel.Datafield) And Not IsEmpty(oModel.Datafield) Then
               If oModel.Datafield <> "" Then oModel.Commit()
            End If
         End If

The oModel.Commit throws the following error:

Code: Select all   Expand viewCollapse view
Error #423 (Property or method not found:) occurred at line 851 in _setProperty

The earlier checks on what the function is trying to do (i.e. set the State of the checkbox control) all pass OK. It's just the Commit of the model that fails. If I knew enough about Base Basic I'd try using the underlying commands to see if I can do it without Access2Base, but I haven't yet fathomed how to do this.

If I set / unset the checkbox manually on the form it works fine. It is just the SetValue that fails (GetValue works OK).

Nonetheless, I repeat, this extension is great! :bravo:

Re: VBA and Base – the Access2Base Extension

PostPosted: Sun Jun 17, 2012 1:01 pm
by pastim
I should just add that I am using the native connector to MySQL version 5.5. This may have some relevance to either of the two glitches I reported above.

Re: VBA and Base – the Access2Base Extension

PostPosted: Sun Jun 17, 2012 2:12 pm
by rudolfo
I have no experience with the Access2Base extensions (mainly because I have never used Access), but I can give you some hints how to get an introduction into Base programming with basic. The two pdf documents by Roberto Benitez listed at the bottom of his page on OOo Base: "OOo Basic Db Development" and "Forms and Dialogs" helped me a lot. They give a good introduction what you can achieve with Forms and for which areas you require macros to get things done effectively.

Others recommend articles by Andrew Pitonyak which are surely good as well. But I follow my personal preferences and that's what I recommend. Might be biased ... but that's how it is.

You will also need the API reference. But this is a reference and not a tutorial.

Re: VBA and Base – the Access2Base Extension

PostPosted: Sun Jun 17, 2012 3:55 pm
by pastim
Thanks. I'll take a look.

Re: VBA and Base – the Access2Base Extension

PostPosted: Sat Jun 30, 2012 11:56 am
by JPL
Hello, (I was only recently aware of the awakening of this older thread)

first of all thanks to PASTIM to have revealed a few bugs in the implementation of Access2Base:
  • The controls of type CheckBox in a form must not be committed (with the Commit UNO method) when changed programmatically (while all other control-types do need a commitment !?).
  • API abort when no item was selected in a listbox bound to an underlying database field.
  • No support of numeric fields of type Currency.
Note that the corrections will be made available with the release of version 0.8.0, expected during july.
The main enhancement in this version will be the support of Standalone (Writer) forms.

Now, about the discussion in above posts about which API best fits the purpose of building applications with OO Base ?
My decision to develop Access2Base was based on next postulates (and as you know postulates do not need to be demonstrated ...):
  • The frontend part of OO Base is a clone of MSAccess - something like MSAccess 97 (1997!) but still a clone.
  • MSAccess is a tremendous worldwide success, among IT professionals as well as end-users.
  • MSAccess proposes an intuitive API hiding the underlying complexity while OO Base proposes UNO, i.e. the API used by the developers of the software. This exludes de facto all end-users and even a segment of IT professionals.
So, there was a hole to fill ...!

Thanks for your feedbacks.

Re: VBA and Base – the Access2Base Extension

PostPosted: Sat Jun 30, 2012 6:41 pm
by pastim
Good stuff. I completely agree about the Access API. I've been trying to understand the native BASE one by looking at some of your code, without much success, so your development is a great help (so much so that I'd be much more reliant on Windows than I am now).

If there is a newer relevant thread I should have used, please let me know.