How to run a Query from a button on a formordi

Running LO 4.4.4.3 on 64 bit Windows 7.
I am creating a database for a Church Parish Directory which holds data such as Name, Address, Phone Number, Mobile Number and Email Address and also Boolean check boxes to allow publication of these data. There are two main tables, tbl-FullDetails and tbl-AllowedDetails. The first table contains all the details known, the second table is generated from the first by a series of SQL statements (DROP TABLE, CREATE TABLE, INSERT INTO which uses a WHERE clause to restrict the data inserted to those giving permission to publish, and several UPDATE statement to change to NULL those fields fields where publication permission has not been granted). These SQL statements are all contained in a text file that can be copied and pasted into the Tools>SQL window and then executed. All this works perfectly well, but is rather cumbersome and not suited to a third party using the database. I have put a button on the main input form with the intention of running a macro which would execute all the SQL statements that work so well by copying and pasting as described. Looking at the documentation for Access2Base there is a command (DoCmd.RunSQL(…)) which looked promising, so I experimented by creating a macro to test how this might work. The macro is

Sub UpdateTable
DoCmd.RunSQL(_
"DROP TABLE tbl-AllowedDetails IF EXISTS")End Sub

This was linked to the button in the form but, unsurprisingly, this failed when the button was pressed. The system error message was “basic run time error, Object Variable not set”

As is obvious, my knowledge of macros is minuscule and I am now seeking advice from those far more knowledgeable than me to show me how the macro I’ve described should be formulated.

Hi

I had not needed to use this library yet but, anyway, it would be necessary to load it into memory for use.
That said, it is not necessary to make what you want. You can use a procedure like:

Sub UpdateTable()

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

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

oQuery = oConn.createStatement()
oQuery.executeQuery(SQL)

End Sub

Note the double "" around the table name (necessary because this string is inside a string)

If this answers the question, thank you to click on the :heavy_check_mark: to mark it ANSWERED.

Regards

I tried out the macro but got a BASIC syntax error saying
expected ".

So I was rather stuck at that point.

This error is probably a mismatch quotes. I put a note in my answer to insist on it. Use two " before & after the table (fiedds) names. Pay attention also to the necessary spaces if you concatenated strings.

In order to carry out actions on a Database Object using a LO Basic Macro you need to create an Object Variable that points to the Database. As @PYS showed in his code the variable is oConn -


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

The SQL query is contained in the Text Variable SQL in the code -


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

As you get Basic Syntax Error expected " check that you have the “” where indicated.
If you change your code -


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

adding the msgbox will pop up a Message Box which should show -

DROP TABLE “tbl-AllowedDetails” IF EXISTS

the SQL code that will be submitted by the Macro.

Thanks for the info. Tried it and it worked. My problem was essentially finger trouble and not properly checking what I’d keyed in.

This is not an answer, but is clarification. The full SQL that I hope to be able to execute fro a macro via a push button is

// Create table for allowed information

DROP TABLE "tbl-AllowedDetails" IF EXISTS

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 
);

// 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
;

//Remove non-allowed fields from tbl-AllowedDetails
//

UPDATE "tbl-AllowedDetails"
SET "StreetNum" = NULL
WHERE"AllowAddress" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "District" = NULL
WHERE"AllowAddress" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "Town" = NULL
WHERE"AllowAddress" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "County" = NULL
WHERE"AllowAddress" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "PostCode" = NULL
WHERE"AllowAddress" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "PhoneNum" = NULL
WHERE"AllowPhone" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "MobileNum" = NULL
WHERE"AllowMobile" = FALSE
;

UPDATE "tbl-AllowedDetails"
SET "EmailAddr" = NULL
WHERE"AllowEmail" = FALSE
;

here is some help for the long sql translation
look at this link
link text
There is not much that is needed to convent their sql to basic
and read RPG posts and try his file open form sql translator