[Base] Access2Base - version 0.9.1 released

Discussions about using 3rd party extension with OpenOffice.org

[Base] Access2Base - version 0.9.1 released

Postby JPL » Wed May 01, 2013 4:14 pm

Access2Base is an OpenOffice/LibreOffice extension for (business or personal) application developers and advanced users.
It provides a AOO/LibO Basic library of macros implementing a number of functionalities - directly inspired by MSAccess. The macros are callable from an OpenOffice/LibreOffice Base application.

:arrow: Version 0.9.1 has been released on 01-may-2013.

Main enhancements:
- a performance improvement when processing very large (up to 1000+ items) listboxes.
- a workaround has been implemented to survive the LibreOffice 4.0 bug described in BugZilla.
- the implementation of the SysCmd method for the management of status bars.
- a Dialog object and its controls for managing dialogs like you could manage forms before
- the Format property may be set programmatically for date and time controls.
- the addition of the OpenSQL action to open a datasheet containing the data described by the provided SELECT SQL statement.

The Dialog object is illustrated with a nice Calculator widget:
Calculator.png

More info:

If you have questions, suggestions, bugs or other topics about the Access2Base extension (especially version 0.9.1) you would like to discuss, I suggest you post a reply to this thread.

JPL

PS the thread about release 0.9.0 is here
Windows 7 / LibO 5.1 / OOo 4.1
Ubuntu 14.04 / LibO 5.1
The Access2Base extension for LibO/AOO Base can be downloaded from the LibO/AOO extension centers. It is embedded in LibreOffice >= 4.2.
Its documentation is on http://www.access2base.com.
JPL
Volunteer
 
Posts: 76
Joined: Fri Mar 30, 2012 3:14 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby greypelican » Tue Feb 04, 2014 10:34 pm

JPL Many thanks for your design of A2B. It has given me a greta boost as I was going round in circles with getting to understand UNO apis.

I am in process of migrating my access db. Something that I have been wanting to do since I went onto a Mac some eight years ago. Talk about procrastination! Anyway, decided on MySql and installed.

Can talk very happy with MySql either via terminal console or through Ooo. Tables migrated long handed by re-creation but data batch INSERTEd via text files. Am now at converting VBA modules. That's when I discovered Access2Base.

My problem is currently that I cannot seem to overcome the insertion of dates as a variable. In the following snippet, the date "04/02/2014" is a literal only because of the multiple attempts that I was making. Within the module it is derived from a form field as entered by the user.

code:

Set orsRecords = Application.CurrentDb().OpenRecordset("TransportDb.Paymast")
With orsRecords
.AddNew ' Fields initialised with the default value
.Fields("payweek").Value = payweek
.Fields("shiftdate").Value = "04/02/2014"
.Fields("startime").Value = shiftstartime

:code end

Within the MySql terminal, I can enter the date as 'yyyy-mm-dd' (with single quotes).
I have placed Date() as the value and that was accepted without problems. Date() equates to dd/mm/yyyy format.

Can you please advise as to how I should code my variable as a Value?

Kind regards
OpenOffice 4.0.1 on MacOS 10.7 with MySql 5.6 on my way from msaccess
greypelican
 
Posts: 3
Joined: Tue Feb 04, 2014 10:06 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby JPL » Wed Feb 05, 2014 2:08 pm

JPL, Many thanks for your design of A2B. It has given me a greta boost as I was going round in circles with getting to understand UNO apis.

Thanks for your feedback !

Can you please advise as to how I should code my variable as a Value?

The answer is simple. I refer to the documentation on here.

The argument of the Value property should be a Basic variable of type Date (VarType = 7).
Use the Basic built-in date functions to
- build the argument (f.i. DateSerial, TimeSerial, Now, ..)
- process the returned value (f.i. DatePart)

Hoping this will help.
JPL

PS as you probably know Access2Base 1.0.0 has been released in december 2013.
Windows 7 / LibO 5.1 / OOo 4.1
Ubuntu 14.04 / LibO 5.1
The Access2Base extension for LibO/AOO Base can be downloaded from the LibO/AOO extension centers. It is embedded in LibreOffice >= 4.2.
Its documentation is on http://www.access2base.com.
JPL
Volunteer
 
Posts: 76
Joined: Fri Mar 30, 2012 3:14 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby greypelican » Wed Feb 05, 2014 3:22 pm

JpL Many thanks for prompt return.

The problem is not surrounding the construction of the date variable but more of pushing it into sql. I can enter manually through the terminal console without any problems. But from within the Basic module I get error #1513 whichever way I structure my date. I have gone through all the permutations (that I can think of) and combinations of single and double quotes.
Each time the same error is thrown out. FATAL Error #1513 (Value "2013-12-20" is invalid for property 'Value') occurred in a call to function 'Field.setValue'

Code: Select all   Expand viewCollapse view
Set orsRecords = Application.CurrentDb().OpenRecordset("TransportDb.Paymast")
   With orsRecords   
      .AddNew            '   Fields initialised with the default value
      .Fields("payweek").Value = payweek
      .Fields("shiftdate").Value = "'2013-12-20'"
      .Fields("startime").Value = shiftstartime



My Ooo locale is set for UK format.

Any more thoughts?
OpenOffice 4.0.1 on MacOS 10.7 with MySql 5.6 on my way from msaccess
greypelican
 
Posts: 3
Joined: Tue Feb 04, 2014 10:06 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby JPL » Wed Feb 05, 2014 5:39 pm

The problem is not surrounding the construction of the date variable but more of pushing it into sql.

I believe you're wrong. You have not to worry about the SQL. The API will take care of that.

Instead of
Code: Select all   Expand viewCollapse view
      .Fields("shiftdate").Value = "'2013-12-20'"

try something like
Code: Select all   Expand viewCollapse view
      .Fields("shiftdate").Value = DateSerial(2013, 12, 20)

or alternatively
Code: Select all   Expand viewCollapse view
      .Fields("shiftdate").Value = DateValue("12/20/2013")

Personally I prefer DateSerial as it is independent from locale settings. DateValue seems sensitive to DD/MM/YYYY, MM/DD/YYYY etc. formats.

StarBasic has also the CDateFromIso and CDateToIso functions to provide conversions from strings to date variables.

Please tell us if solved.
JPL
Windows 7 / LibO 5.1 / OOo 4.1
Ubuntu 14.04 / LibO 5.1
The Access2Base extension for LibO/AOO Base can be downloaded from the LibO/AOO extension centers. It is embedded in LibreOffice >= 4.2.
Its documentation is on http://www.access2base.com.
JPL
Volunteer
 
Posts: 76
Joined: Fri Mar 30, 2012 3:14 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby greypelican » Wed Feb 05, 2014 6:31 pm

:super: JPL

Spot on! Many thanks.

I was using date value but assigning prior to the .Value statements.

Kind regards.
OpenOffice 4.0.1 on MacOS 10.7 with MySql 5.6 on my way from msaccess
greypelican
 
Posts: 3
Joined: Tue Feb 04, 2014 10:06 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby Steve R. » Tue Feb 11, 2014 12:14 am

Thanks very much!!! My database programming has been in MS Access and I am in the process of converting my MS Access databases to Base.
I'm still at the "bottom" of the Base learning curve so I anticipate it will be a while before I have in-depth questions/comments.
Being able to use MS Access syntax, which I know, has been very useful.
Ubuntu 16.04 and Windows 10
User avatar
Steve R.
 
Posts: 162
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: [BASE] Access2Base - version 0.9.1 released

Postby Ncr » Sat Feb 22, 2014 1:34 am

I must say
Nice work JPL!
This is my third attempt on Base through the years and this time Access2base almost convinced me.
I sympathize with the concept of community-driven software, never joined but have been using write for years.
Base works well with foreign database files.
But the front-end is such a crap! Building reports is a pain, even with oracle report builder, and the forms make me wanting to go back to Office97, when i started using VBA. Error messages appear for the strangest reasons!
I think i will stay with access runtime until somebody improves the look and feel of Base.
But i think your effort is a major one. Keep up the good work!
Openoffice 4 on windows 7
Ncr
 
Posts: 1
Joined: Sat Feb 22, 2014 12:58 am

Re: [BASE] Access2Base - version 0.9.1 released

Postby muse79 » Fri Mar 28, 2014 9:56 am

I'd just like to say Access2Base has been invaluable to me. After switching from MS Access to Libre Office and having a good foundation in coding, I found it frustrating to write macros. With Access2Base it has been a lot easier. I'm much more confident with being able to write macros for my range of needs now. There's been a few hiccups along the way and I'm sure there will be more but I couldn't have got any where near as far as I have without it. Thank you!
libreoffice 4.2 Windows 7 64bit
muse79
 
Posts: 11
Joined: Sat Feb 22, 2014 8:50 pm

Trouble setting fields to Null in Edit/Update loops

Postby KurtJ » Tue Aug 26, 2014 7:47 pm

Using version 1.0.0 of Access2Base in LibreOffice 3.6.

Hi, I appreciate the work you have done making BASE actually useful. I used it to construct a biological medium database for work use, now I'm messing around with a recipe database and ran into what may have been a small oversight. I tried to look at your code, but I don't have the necessary comprehension of the BASE programming models, to attempt fixing it myself.

I am unable to set database fields to Null in an Edit/Update Loop, in both text and numerical fields. The error message is "Error #1513 (Value '[NULL]' is invalid for property 'Value') occurred in a call to function 'Field.setValue'"

Here is the test code. Putting in any string here, the code runs fine, or if it is a numerical field, any number. However it would be useful to be able to return a field to Null so you can avoid workarounds like a space character, or using an update query later. Thanks.

Code: Select all   Expand viewCollapse view
Dim frmRecipes As Object
Dim numID As Object
Dim strSQL1 As String
frmRecipes = Forms("frmRecipes")
numID = frmRecipes.Controls("numID")

strSQL1 = "SELECT ""tblRecipeIngredients"".* FROM ""tblRecipeIngredients"" WHERE ""RecipesChild"" = " & numID.Value

Dim rstRecipeIngredients As Object
Set rstRecipeIngredients = CurrentDB.OpenRecordset(strSQL1)

With rstRecipeIngredients
   If Not .BOF Then   ' An empty recordset has both .BOF and .EOF set to True
      Do While Not .EOF
         .Edit
         'Null, "Null", dbNull, NULL, [NULL], [EMPTY], EMPTY do not work below.
         .Fields("TotalQtyFraction").Value = Null
         .Update
         'Go to next record
         .MoveNext
      Loop 'Do While Not .EOF
   End If 'If Not .BOF
.mClose()
End With
Last edited by KurtJ on Wed Aug 27, 2014 1:20 pm, edited 1 time in total.
LibreOffice 3.6 on Win 7
KurtJ
 
Posts: 3
Joined: Tue Aug 26, 2014 7:21 pm

Re: [BASE] Access2Base - version 0.9.1 released

Postby JPL » Wed Aug 27, 2014 8:45 am

@KurtJ,

can you confirm that the field "TotalQtyFraction" may receive a Null value, i.e. the attribute "Entry required" is set to 'No' when you design/edit the table ?

BTW you might use next statement:
Code: Select all   Expand viewCollapse view
strSQL1 = "SELECT [tblRecipeIngredients].* FROM [tblRecipeIngredients] WHERE [RecipesChild] = " & numID.Value

which is more readable, if you prefer.

JPL
Windows 7 / LibO 5.1 / OOo 4.1
Ubuntu 14.04 / LibO 5.1
The Access2Base extension for LibO/AOO Base can be downloaded from the LibO/AOO extension centers. It is embedded in LibreOffice >= 4.2.
Its documentation is on http://www.access2base.com.
JPL
Volunteer
 
Posts: 76
Joined: Fri Mar 30, 2012 3:14 pm

Re: Trouble setting fields to Null in Edit/Update loops

Postby KurtJ » Wed Aug 27, 2014 1:19 pm

Yes, the field can receive a null value. My workaround was to use an update query after the loops, setting the field to Null by SQL and that works fine.

I wanted to add that the two types of fields I have tested with this code are VARCHAR and DOUBLE. The code below is for the DOUBLE field.

Code: Select all   Expand viewCollapse view
'Run update query to replace 0 with null, doesn't work inside edit/update loop.
Dim strSQL3 As String
strSQL3 = "UPDATE [tblRecipeIngredients] SET [TotalQtyWhole] = Null WHERE [RecipesChild] = " & numID.Value & " AND [TotalQtyWhole] = 0"
DoCmd.RunSQL(strSQL3)
LibreOffice 3.6 on Win 7
KurtJ
 
Posts: 3
Joined: Tue Aug 26, 2014 7:21 pm

Re: [Base] Access2Base - version 0.9.1 released

Postby JPL » Thu Aug 28, 2014 6:57 pm

@ KurtJ

nice to see the SQL workaround working better than the Value property ...
Indeed it seems to be a bug ... that is not even corrected in Access2Base 1.1.0 :(

To correct the code manually:
In module Fields of the Access2Base library, replace line 485
Code: Select all   Expand viewCollapse view
               If Column.IsNullable = com.sun.star.sdbc.ColumnValue.NULLABLE Then Column.updateNull() Else Goto Trace_Null

with next lines:
Code: Select all   Expand viewCollapse view
               If Column.IsNullable = com.sun.star.sdbc.ColumnValue.NULLABLE Then
                  Column.updateNull()
                  Goto Exit_Function
               Else
                  Goto Trace_Null
               End If

I will prepare a patch for the LibreOffice 4.2 and 4.3 users.
 Edit: (31-aug-2014) A correction has been inserted in LibreOffice 4.2.7 and 4.3.2+ releases. 


Thanks for your contribution to a better software.
JPL
Last edited by JPL on Sun Aug 31, 2014 4:00 pm, edited 1 time in total.
Windows 7 / LibO 5.1 / OOo 4.1
Ubuntu 14.04 / LibO 5.1
The Access2Base extension for LibO/AOO Base can be downloaded from the LibO/AOO extension centers. It is embedded in LibreOffice >= 4.2.
Its documentation is on http://www.access2base.com.
JPL
Volunteer
 
Posts: 76
Joined: Fri Mar 30, 2012 3:14 pm

Setting Null issue fixed.

Postby KurtJ » Thu Aug 28, 2014 7:53 pm

JPL,

This fixed the issue, and now I'm setting Null in both types of fields.

I could mention here also that edit/update loops only work on single table queries with all fields selected. On multi-table queries or if only a few fields are selected in the recordset, an error is generated. It took me a while to puzzle this one out, since it isn't mentioned in your help file. I had been guessing it was a limitation in the BASE programming model.

It always impressed me you did all this work for no pay or recognition. That may be a sign of an outstanding character.
LibreOffice 3.6 on Win 7
KurtJ
 
Posts: 3
Joined: Tue Aug 26, 2014 7:21 pm

Re: [Base] Access2Base - Sample Code Using a Global Variable

Postby Steve R. » Sun Sep 28, 2014 3:40 pm

After a bit of experimentation, I was able to incorporate the global variable "intStoryNUM" into the SQL string.
Both examples accomplish the same task.
Code: Select all   Expand viewCollapse view
Openform strAddAuthorForm, , ,  """StoryIDNUM""  = '" + intStoryNUM + "'", acFormReadOnly


Code: Select all   Expand viewCollapse view
Openform strAddAuthorForm, , , ,acFormReadOnly,   
strSQL = "SELECT * FROM [tblStoryList] WHERE [StoryIDNUM]  = '" + intStoryNUM + "'"
Forms(strAddAuthorForm).RecordSource = strSQL

I hope that you can include these code snippets in your website as additional examples. Thank-you JPL for developing Access2Base.
Ubuntu 16.04 and Windows 10
User avatar
Steve R.
 
Posts: 162
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: [Base] Access2Base - version 0.9.1 released

Postby estelondono » Sat Jun 10, 2017 2:34 am

Hi there!

First, thank you for all your great work with Access2Base. It really makes everything more easy for people like me coming from windows to Linux and from Windows Office to LibreOffice (and not coming back ;) ). I'm using LibreOffice 5.3 in Linux Lite 3.4 (Ubuntu 16.04) and I'm having a problem passing some info from one form to another.
I'm in a form and when I click in a text field it opens another form with info. In this form you can change everything. Even add new records. Then, you click in the field with the value you want and pass to the form of origin. The value is shown in the text field in that first form. The value I'm passing is and Id (key record) type BIGINT. In both tables are the same type.
The problem is when I try to save this record. LibreOffice tells me
Error registering record in the Data Base
An entry is needed in the field <<id_tipo>>. Write the value

(I translated it from Spanish :? )
So I have to (re)type the value where the same value is shown for the record to be saved. What am I doing wrong?
The Basic Code that passes the value from one form to the other is
Code: Select all   Expand viewCollapse view
Sub pasaDatoInfoCliente(poEvent as Object)
   Dim oForm as Object, dForm as Object
   Dim oControl as Object, dControl as Object, valor as String
   
   Set oForm = Events(poEvent).Source.Parent   'Form of origin
   Set dForm = Forms("formDATOS_CLIENTES")   'Form of destiny
   Set oControl = oForm.Controls("id_tipo_dato")   'Control of origin
   Set dControl = dForm.Controls("fmtid_tipo_dato")   'Control of destiny
   valor = oControl.Value                  'Value to pass to dForm | I tried with oControl.Text also but doesn't solve the problem
   
   dControl.Setfocus   'This was to see if it solved my problem. No success
   DoCmd.mClose(acForm, "formTIPOS_DATOS")   'No matter where I put this line, it doesn't solve my problem.
   dControl.setProperty("Value", valor)         'I'm using this syntax because dControl.Value = valor doesn't work
   dForm.Refresh      'This was also to solve the problem but also no success.
   
End Sub


If you can help me, it would be of great help to me.

Thank you again for your great work!

PS: I hope my English is good enough for you to understand me :D
OpenOffice 5.3 on Ubuntu 16.04
estelondono
 
Posts: 1
Joined: Sat Jun 10, 2017 1:11 am

Re: [Base] Access2Base - version 0.9.1 released

Postby paco » Mon Sep 04, 2017 7:16 pm

As everybody who knows your work, I want to congratulate you heartily for it: it is magnificent and very useful.

After that, I just want to report a minor quirk that I have experienced, just in case it is of some general interest. I am just trying to write a little macro for getting information about the versions of the application that is being used and the database engine also in use. This should be pretty easy as

MsgBox Application.Version
and
MsgBox CurrentDb().Version

should be enough. The surprising thing is that whereas the first instruction works as expected as well in OpenOffice 4.1.3 as in LibreOffice 5.2.3.3, the second one does it only in OpenOffice, LibreOffice providing an error message to the effect that the *Version* property or method is not found.

On a similar vein I have been also unable to get the name of the current database by way of *CurrentDb().Name* in LibreOffice (it just says *database*), although I understand that *Name* is a valid property of such an object.

Kind regards
Windows 7 + Apache OpenOffice 4.1.3.
paco
 
Posts: 3
Joined: Wed Nov 30, 2016 12:01 pm


Return to Extensions

Who is online

Users browsing this forum: No registered users and 2 guests