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.