StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Additionally, add a column Validation Rule (or <code>CHECK</code> constraint) to ensure the 'timestamp' column is updated when the table is being updated other than via your form. The SQL DLL (ANSI-92 Query Mode syntax) would look something like this:</p> <pre><code>CREATE TABLE MyTable ( key_col INTEGER NOT NULL UNIQUE, data_col INTEGER NOT NULL ) ; ALTER TABLE MyTable ADD my_timestamp_col DATETIME DEFAULT NOW() NOT NULL ; ALTER TABLE MyTable ADD CONSTRAINT my_timestamp_col__must_be_current_timestamp CHECK (my_timestamp_col = NOW()) ; </code></pre> <p>Another approach when using Jet 4.0 (pre-Access 2007 i.e. before user level security was removed from the engine) is to create a 'helper' Jet SQL <code>PROCEDURE</code> (Access term: stored Query object defined using an SQL 'Action' statement, as distinct from a SQL <code>SELECT</code> query) that automatically updates the 'timestamp' column then remove 'update' privileges from the table and grant them instead on the <code>PROC</code> e.g. SQL DDL/DCL something like:</p> <pre><code>CREATE PROCEDURE MyProc ( arg_key INTEGER, arg_new_data INTEGER ) AS UPDATE MyTable SET data_col = arg_new_data, my_timestamp_col = NOW() WHERE key_col = arg_key ; REVOKE UPDATE ON MyTable FROM PUBLIC ; GRANT UPDATE ON MyProc TO PUBLIC ; </code></pre> <p>The advantage here is all updates must go via the <code>PROC</code> and therefore is under the developer's control; the disadvantage is Access/Jet SQL is that your form will also have to use the <code>PROC</code>, which means a paradigm shift away from the standard 'data bound forms' approach for which Access is famous.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload