How to run a Query from a button on a form (part 2)

I received help a couple of weeks ago relating to running a query from button on a form and was provided with the following macro

Sub UpdateTable()

REM Deletes the old table

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

SQL =  "DROP TABLE ""tbl-AllowedDetails"" IF EXISTS"

oQuery = oConn.CreateStatement()

oQuery.executeQuery(SQL)

End Sub

Which worked perfectly. I have been trying, with no success, to add to this macro the code to run the following sql

CREATE TABLE "tbl-AllowedDetails"

( "ID"        INTEGER NOT NULL PRIMARY KEY
 ,"Surname"   VARCHAR(100)
 ,"FirstName" VARCHAR(100)
 ,"Initials"  VARCHAR(100)
 ,"Title"     VARCHAR(100)
 ,"StreetNum" VARCHAR(100)
 ,"District"  VARCHAR(100)
 ,"Town"      VARCHAR(100)
 ,"County"    VARCHAR(100)
 ,"PostCode"  VARCHAR(100)
 ,"PhoneNum"  VARCHAR(100)
 ,"MobileNum" VARCHAR(100)
 ,"EmailAddr" VARCHAR(100)
 ,"AllowAddress" BOOLEAN
 ,"AllowPhone"   BOOLEAN
 ,"AllowMobile"  BOOLEAN
 ,"AllowEmail"   BOOLEAN 
)

And also the sql

// Insert data into table
//

INSERT INTO

"tbl-AllowedDetails"


("ID"
,"Surname"
,"FirstName"
,"Initials"
,"Title"
,"StreetNum"
,"District"
,"Town"
,"County"
,"PostCode"
,"PhoneNum"
,"MobileNum"
,"EmailAddr"
,"AllowAddress"
,"AllowPhone"
,"AllowMobile"
,"AllowEmail"
)

SELECT

 "ID"
,"Surname"
,"FirstName"
,"Initials"
,"Title"
,"StreetNum"
,"District"
,"Town"
,"County"
,"PostCode"
,"PhoneNum"
,"MobileNum"
,"EmailAddr"
,"AllowAddress"
,"AllowPhone"
,"AllowMobile"
,"AllowEmail"

FROM

"tbl-FullDetails"

WHERE

"tbl-FullDetails"."AllowSurname" = TRUE

which follow each other.
Delving into BH3509-Macros gave me some ideas but, no matter what I tried, I ended up with syntax errors galore. I suspect the problem lies with my inability to correctly code for concatenation of strings. Therefore I need to ask the advice, once again, of those who know how this should be formulated.

As was said in the answers to your previous question it is necessary to get the syntax for the string SQL correct.

If I create this code :-

SQL = “Test”“Again”""

msgbox SQL


it works. But if I do :-


SQL = “Test"Again”"


it fails with the error :-


Basic Syntax Error.

Unexpected Symbol : Again


and in the Basic Editor "Test"will be highlighted.

A further useful point is that when you create the string SQL in the Basic Editor, if the syntax for the string is creating a valid string (not necessarily valid SQL) all of the string will be in RED text.

In the case above where the error occurred in the Basic Editor the first part of the SQL string “Test” is in RED text but Again is in GREEN text. This will enable you to create a valid text variable SQL.

Thanks, once again, for the info which has given me further food for thought

The following macro does exactly what I required. Writing the macro was only possible with the great help I received from pierre-yves and doug to whom I owe my grateful thanks. One point regarding the macro is that it must only be run after the current record is saved otherwise an error is created (this caused me much angst until I finally sorted out what was happening).

REM  *****  BASIC  *****

Sub UpdateTable()

oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQuery = oConn.CreateStatement()

REM Delete the old table

SQL =  "DROP TABLE ""tbl-AllowedDetails"" IF EXISTS"

oQuery.executeQuery(SQL)

REM Create a new table

SQL = "CREATE TABLE ""tbl-AllowedDetails"" " & _
"( ""ID""        INTEGER NOT NULL PRIMARY KEY" & _
",""Surname""   VARCHAR(100)" & _
",""FirstName"" VARCHAR(100)" & _
",""Initials""  VARCHAR(100)" & _
",""Title""     VARCHAR(100)" & _
",""StreetNum"" VARCHAR(100)" & _
",""District""  VARCHAR(100)" & _
",""Town""      VARCHAR(100)" & _
",""County""    VARCHAR(100)" & _
",""PostCode""  VARCHAR(100)" & _
",""PhoneNum""  VARCHAR(100)" & _
",""MobileNum"" VARCHAR(100)" & _
",""EmailAddr"" VARCHAR(100)" & _
",""AllowAddress"" BOOLEAN" & _
",""AllowPhone""   BOOLEAN" & _
",""AllowMobile""  BOOLEAN" & _
",""AllowEmail""   BOOLEAN" & _ 
" )"

oQuery.executeQuery(SQL)

Rem Insert allowed data into table

SQL = "INSERT INTO ""tbl-AllowedDetails"" " & _
"(""ID"" " & _
",""Surname"" " & _
",""FirstName"" " & _
",""Initials"" " & _
",""Title"" " & _
",""StreetNum"" " & _
",""District"" " & _
",""Town"" " & _
",""County"" " & _
",""PostCode"" " & _
",""PhoneNum"" " & _
",""MobileNum"" " & _
",""EmailAddr"" " & _
",""AllowAddress"" " & _
",""AllowPhone"" " & _
",""AllowMobile"" " & _
",""AllowEmail"" " & _
") " & _
"SELECT" & _
" ""ID"" " & _
",""Surname"" " & _
",""FirstName"" " & _
",""Initials"" " & _
",""Title"" " & _
",""StreetNum"" " & _
",""District"" " & _
",""Town"" " & _
",""County"" " & _
",""PostCode"" " & _
",""PhoneNum"" " & _
",""MobileNum"" " & _
",""EmailAddr"" " & _
",""AllowAddress"" " & _
",""AllowPhone"" " & _
",""AllowMobile"" " & _
",""AllowEmail"" " & _
"FROM ""tbl-FullDetails"" " & _
"WHERE ""tbl-FullDetails"".""AllowSurname"" = TRUE"

oQuery.executeQuery(SQL)

REM Remove non-allowed fields from table

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
" SET ""StreetNum"" = NULL " & _
"WHERE ""AllowAddress"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""District"" = NULL WHERE ""AllowAddress"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""Town"" = NULL WHERE ""AllowAddress"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""County"" = NULL WHERE ""AllowAddress"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""PostCode"" = NULL WHERE ""AllowAddress"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""PhoneNum"" = NULL WHERE ""AllowPhone"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""MobileNum"" = NULL WHERE ""AllowMobile"" = FALSE"

oQuery.executeQuery(SQL)

SQL = "UPDATE ""tbl-AllowedDetails"" " & _
 "SET ""EmailAddr"" = NULL WHERE ""AllowEmail"" = FALSE"

oQuery.executeQuery(SQL)

End Sub

There are three kinds of execute statements: SbxOBJECT executeQuery ( SbxSTRING ) ; SbxLONG executeUpdate ( SbxSTRING ) ; SbxBOOL execute ( SbxSTRING ) ; Per the LO 5.0 API probably executeUpdate is cleaner choice. It also may be helpful dispose of the recordset and cursor after repetitive queries by cancel and/or close, would need to reinitiate.