VBA and Base – the Access2Base Extension

[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
http://user.services.openoffice.org/en/ ... 20&t=46549

I would draw attention to a most interesting utility for VBA programmers mentioned in the other forum at
http://www.oooforum.org/forum/viewtopic ... ht=#479213

An extension by Jean-Pierre Ledure called “Access2Base” has recently been posted and can be found at
http://extensions.services.openoffice.o ... ccess2base
with full documentation at http://www.access2base.com.

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.

[cazbym]

I have just posted this on the "other forum", but thought I would repeat it here.

This is wonderful.

From OpenOffice.org Forum

The solution is based on an extension I posted a few days ago. It's called Access2Base and you can find it on http://extensions.services.openoffice.o ... ccess2base.
Full documentation is available on http://www.access2base.com.

Code:
How to proceed ?

Install the extension
Download the example database from http://www.access2base.com/howtos/Open_ ... 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!

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

[JPL]

Indeed:
- 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 http://www.access2base.com/Access2Base.html#DownLoad
The above (other post) mentioned http://www.access2base.com/howtos/Open_ ... Record.odb 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

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.

[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

								' 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

			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

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!

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

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

[pastim]

Thanks. I'll take a look.

[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.
JPL

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