Using VBA in Libre

English
,
#1

Im building an app on my laptop in Libre 6.4.4.2
i just curiously tried the below code ive often used in my Excel VBA Tools
this deals with creating recordsets not connected to a Database

Option VbaSupport 1

Sub vbax

Dim rstADO As ADODB.Recordset
Dim fld As ADODB.Field

Set rstADO = New ADODB.Recordset
With rstADO
.Fields.Append “ID”, adInteger, , adFldKeyColumn
.Fields.Append “FirstName”, adVarChar, 10, adFldMayBeNull
.Fields.Append “LastName”, adVarChar, 20, adFldMayBeNull
.Fields.Append “Email”, adVarChar, 64, adFldMayBeNull
.Fields.Append “Include”, adInteger, , adFldMayBeNull
.Fields.Append “Selected”, adBoolean, , adFldMayBeNull

.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockPessimistic
.Open

For r=1 To 10
rstADO.AddNew
    rstADO("email").Value = r
    rstADO.Update

Next r

rstADO.filter= "email='1'"

msgbox rstADO.recordcount

 application.cursor= xlwait

End With
End Sub

this worked fine
Now the question

Is this anything to do with Excel already being installed on my Laptop or is it normal Libre working ?
I do not have any kind of Database system in my Laptop
The tool im building will be deployed on a client system that has only Libre same version installed and No Excel and No JRE
so just need to know if its safe to assume that the code will run the same way on that system too - or are there other factors involved
the above code is just an example
my actual code will later have several complex recordset manipulations
If you are not aware of how powerful this vba feature is and why I should use it etc. lets not worry about that but just let me know if these sort of vba codes can work on my Laptop then will they run on any other system having the same Libre version

#2

Nope. With this code you stick to MS Office. This has not even to do with Excel. IIt has to do with data access from some kind of database. I can’t see any reference to an Excel.Application object.

#3

this runs on my Libre and no database in my Laptop whatsoever
to be more specific this code uses the MS Access database engine

msgbox application.name
that gives “Excel”

i have no clue how all this works but will be great news if i can run this on another system too. will have to wait a while to be able to check that

#4

The first line
Dim rstADO As ADODB.Recordset`
requires a Windows system with certain data access features installed. Whatever you try to do here, it needs to be done differently with any environment other than Windows. The problem is that you do not tell us what you are after.

#5

i think i’d better wait to get an opportunity to test it on that system
im after only 1 thing - to be able to use this Recordset object thats all
lot of data manipulation done with it
it is far more convenient and cleaner to use than other methods
mainly sorting filtering searching when working with data
i always use this in my macros rather than worksheets and their functions
i know what to do with it and no help needed there.
what i dont know is - and all I want to know is - what is it that may restrict this from running on a different system with the same Libre version.
my client runs on windows too

#6

Simpy expressed: LibreOffice knows as much of ADO as Microsoft Office knows of UNO-Objects.
While the same language may be used to access the libraries, they are completly different in design. Second problem: Access was not bundled to every Windows, but sold for additionsl cost. So you may have to check what is available on every system.

To my knowledge no direct porting possible.
There are libraries to make this easier, but this doesn’t seem to be your question.

If you use this in all your VBA-Tools stay with Microsoft/.NET and maybe Powershell.

1 Like
#7

should we read VBA-Toys ?! :wink:

1 Like
#9

thanks i get it . this is more like the answer i was looking for
so then what is the difference in Access being bundled into windows and Access separately installed ?
coz i know my target system has no separate Access application installed
so Lets say this code doesn’t work there as it is directly.
then is there any way i can get it to work without installing any plugins etc. ?
i guess not

#13

thanks - the recordset object worked fine on the target system

#8

LibreOffice Base provides recordset object on Windows, Mac and Linux.

1 Like
#10

that seems good news then
im not able to use the database in my client Libre coz when i tried creating a new database through the wizard it gave me the JRE error - JRE not installed
some google search told me thats a common known problem
i cant implement the database without being able to use the wizard to create tables etc. so i’m using an alternate method to mimic a database but that’s a different matter.
im good even if im able to use this recordset thing there
I shall try this anyway

#11

If you can’t create a database in LibreOffice bcause of a missing JRE, you won’t be able to do anything non-trivial with this office suite. As a matter of fact, you do not even need to create a database. You can use an existing one. Apart from the report wizard, all the Base wizards are useless anyway because they cover only a tiny fraction of the possible functionality. Without any Java installed on your system you can connect a non-Java database to a Base document, create queries and forms but no reports.
You can even create a new database without Java. Connect a new Base document to an empty dBase folder.

#12

Good news for me Villy the recordset object does work on the target system just like on my laptop !! thanks for all ur inputs
now the database thing u said - u say its possible to use the Libre database system without JRE but im missing something then.
the 1st thing i want to do in that case is open the database and create some tables
but File > New > Database > create database
gives that error message

#14

Depends on your understanding of “the system”.

I can use my Sqlite-Databases with LibreOffice without Java (but I have to install an odbc-driver at the level of the OS, and manage the connections.)

I you consider the embedded database HSQL as part of the system, you need java, because HSQL is written in Java itself.

You may use MySQL/MariaDB, but if you use the jdbc-driver you will find j is for java… So you have to choose another connctor…

As before: You can’t ( better should not) assume all this works out of the box on any system. Neither for Windows ( upgrade-installations differ from clean installs, and lots of variants in Versions), nor on linux with its free choice of distribution and desktop manager.

Usually you need to test a lot and create an installer to get all necessary packages , if you plan something bigger…

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.