Running LO 18.104.22.168 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.