Ask Your Question
1

How to run a Query from a button on a formordi

asked 2015-07-18 12:05:43 +0200

BillPodmore gravatar image

updated 2015-09-27 18:26:30 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2015-07-18 14:02:16 +0200

pierre-yves samyn gravatar image

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 ✔ to mark it ANSWERED.

Regards

edit flag offensive delete link more

Comments

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

So I was rather stuck at that point.

BillPodmore gravatar imageBillPodmore ( 2015-07-21 18:20:57 +0200 )edit

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.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-07-22 08:59:52 +0200 )edit
1

answered 2015-07-22 00:09:17 +0200

peterwt gravatar image

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 @pierre-yves samyn 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.

edit flag offensive delete link more

Comments

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

BillPodmore gravatar imageBillPodmore ( 2015-07-22 08:48:39 +0200 )edit
0

answered 2015-07-21 18:24:04 +0200

BillPodmore gravatar image

updated 2015-07-22 00:17:10 +0200

doug gravatar image

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
;
edit flag offensive delete link more

Comments

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

Kridtbandit gravatar imageKridtbandit ( 2019-07-19 15:49:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-18 12:05:43 +0200

Seen: 1,274 times

Last updated: Jul 22 '15