Experience with MS Access databases?

English
,
#1

This post was flagged by the community and is temporarily hidden.

1 Like
#8

Just in case, anybody feels the need to generate a database connection by means of macro code.
Arguments:

  1. URL of odb document
  2. URL of database
    2.1. sdbc:embedded:hsqldb or sdbc:embedded:firebird for a new database
    2.2. some jdbc URL
    2.3. something like sdbc:odbc:MariaDummy in case of ODBC
  3. Optional registration name
  4. Optional user name
  5. Optional if a password is required or not with the given user name
  6. JDBC only: class name
  7. JDBC only: driver path, may be missing if the driver is included in the class path of the LO java options.
Option Explicit
REM create a new embedded Firebird
Sub new_Firebird()
	CreateBinaryDB _
		dbURL:= "file:///tmp/test/NewFirebird.odb", _
		sURL:= "sdbc:embedded:firebird"
End Sub

REM connect a new document to my HSQLDB on local network 
Sub test_CreateBinaryDB()
	CreateBinaryDB _
		dbURL:= "file:///tmp/test/NewPraxis.odb", _
		sURL:= "jdbc:hsqldb:hsql://192.168.15.02/Praxis;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false", _
		sRegName:= "New Praxis DB", _ 
		sUser:= "SA", _
		bPassword:= False, _
		sClassName:= "org.hsqldb.jdbcDriver", _
		sDriverURL:= "file:///opt/hsql/hsqldb-2.7.4/hsqldb/lib/hsqldb.jar"
End Sub

REM Based on CreateBinaryDB in AdrewBase.odt by Andrew Pitonyak
REM URL of odb, URL of database, database registration, user, is passwd required, jdbc class, jdbc driver
Sub CreateBinaryDB(dbURL$, sURL$, optional sRegName$, optional sUser$, optional bPassword As Boolean, optional sClassName$, optional sDriverURL$)
Dim oDBContext
If isMissing(sRegName) then sRegName = ""
if isMissing(sUser) then sUser = ""
if isMissing(bPassword) then bPassword = False
if isMissing(sClassName) then sClassName = ""
if isMissing(sDriverURL) then sDriverURL = ""
Dim oDB
	If dbURL = "" Then Exit Sub
	If FileExists(dbURL) Then
		Msgbox "The database document already exists.",16
		exit sub
	End If
	oDBContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
	oDB = oDBContext.createInstance()
	oDB.URL = sURL
	oDB.User = sUser
	oDB.IsPasswordRequired = bPassword
	with oDB.Settings
		REM JDBC driver's capability:
		if len(sClassName)>0 then .JavaDriverClass = sClassName
		REM JDBC driver not registered in LO Java options
		if len(sDriverURL)>0 then .JavaDriverClassPath = sDriverURL
	end with
	oDB.DatabaseDocument.storeAsURL(dbURL, Array())
	if len(sRegName) > 0 then RegisterFrontend oDBContext, sRegName, dbURL
End Sub

Sub RegisterFrontend(oRS, sRegName$, sDBURL$)
	if oRS.hasRegisteredDatabase(sRegName) then
		if oRS.getDatabaseLocation(sRegName) <> sDBURL then oRS.changeDatabaseLocation(sRegName, sDBURL)
	else
		oRS.registerDatabaseLocation(sRegName, sDBURL)
	endif
End Sub
#9

I’ve also read AndrewBase documentation, so I’m able to get also the embedded jdbc to dance the way want. Just don’t lose your sense of humour while you’re coding (at your free time) :wink:

#2

If you are interested, but you don’t have much experience or you have tried to do it without success. Then I have good news for you. At first make a visit here and read/download the instructions. Then you can download a simple .rtf test file containing a few text lines and an image. Or you can create your own file with LO Writer and save it in .rtf format as: help1.rtf to your desktop. Then you can download and study this:
DaoDbTest.odt (22.2 KB)
I believe, that when you get the idea, you’ll be able to extend the functionality of your LO Basic macros powered by .NET to a level of which you didn’t believe would even be possible.

1 Like
#3

Looks like spam or advertisement. Are you a real human? What is your goal? LO Base won’t create M$ Access databases. And the purpose of Base is to avoid macros as much as possible.

#4

Who was talking about LO Base? I’m talking about creating an MS Access database with LO Basic macro by using AccessDataBaseEngine.

#5

Then the relationship with LO is rather thin. You’re only interested in LO Basic. LO main goal is not to provide a programming environment. I’d even say that macros are an “accident” to cover corner cases for specification misses. Access, Excel, AccessDataBaseEngine and W11 are all proprietary M$ tools, while LO hopes to provide an alternate free and open solution.

Moreover, many users are under MacOS and Linux. So, there are also outside your proposal?

#6

@ajlittoz: It’s just an old stubborn M$ fanboy … ignore him!

#7

Firstly, I think I clearly mentioned that this is related to Windows 11.
“LO main goal is not to provide a programming environment.”
Well, maybe so, but it seems like Python is becoming more and more a part of LO.
As for your outpouring, it just smells like holy hatred :wink:

1 Like
#10

Maybe it’s time to clarify a little. I have (a lot) picture data stored in an Access db file, which I found from an old USB-stick. The data in OleObject fields were read from image files to Byte arrays and converted to String arrays using StrConv(bytes, vbUnicode) function and then stored to the OleObject fields. I had to install Access DB Engine to be able to extract the data from that database. Unfortunately, LO Basic/UNO have limits so it’s not possible to make this work only by using LO Basic functions. I concluded that the simplest way to make this work is to create a simple ComVisible .NET .dll with StrConv function and that’s all. While doing that, I realized that I could extend the functionality of LO Basic macros in the Windows environment with simple stuff. For this, I didn’t have to install anything that isn’t included in the OS installation, except the Access DB Engine. Additionally, I had to extract 3 .NETFX tools from the Windows 11 SDK (ISO). This can be done for free (so there’s no need of $ or € or £ or anything).

#11

For those, who are looking for a platform independent Access driver: UCanAccess-A Pure Java JDBC Driver for Access (and if you are a Java developer, you should know ways to convert bytes to unicode).

1 Like
#12

Your sermon about platform independence is just BS. If it were true, for example, LO would only need one installation package which could be installed directly on any operating system. As for UCanAccess driver I tested it, i.e. added the .jars to the classpath but didn’t get it work the way it’s advertised.

#13

⇑⇑⇑ BU…SHIT ⇑⇑⇑
( but eventually possible by some tiny shellscript which installs only the huge platform-dependend-stuff ) at the end of the procedure it would not make any difference !

#14

Seemes to be hard just ignore an old stubborn…:wink:

#15

VBA function StrConv with vbUnicode

Converts the string to Unicode using the default code page of the system.

In LO Basic converting between a string and a byte array is easy:

Sub Test2
    Dim aBytes() As Byte, s as String
    aBytes="AZΑΩ"   ' aBYtes:  65, 0, 90, 0, 145, 3, 169, 3
    s=aBytes        ' s: AZΑΩ
End Sub
#16

When you read image data from file to a byte array LO Basic StrConv as well as LENB function are completely useless.

#17

@Villeroy
I have to ask how pure this “pure” finally is?
Namely, I found .mdb/.accdb files (empty.mdb, empty2003.mdb, empty2007.accdb, empty2010.accdb and empty2016.accdb) inside the jakcess.jar package :wink:

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