I have been trying to convert MS Access databases to Base with HSQLDB back end. The first step using a Base front end and an Access back end was not too bad apart from the usual syntax peculiarities.
Getting INSERT queries to work from macros with the HSQLdb back end was another matter, until I found this useful post. Here is my modification of whalleys code above:
Sub doSql (s as string)
Rem Runs an SQL command, including INSERT as well as SELECT
Dim oStatement as object
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute(s)
end sub
'and a little test to show the syntax of the command:
Sub testdoSQL
dim str as string
str = "INSERT INTO ""T1"" (""ID"", ""Val"") VALUES (3,'c')"
doSql(str)
end sub
Now all you have to do is pop the required sql statement into a string variable and use the doSql sub to run it.
Sorry if this is a bot late for allan, but it might help someone else.