Stop editing data in tables directly. Use always forms

[nagyf]

How can I force that users can enter/modify data from a certain table T using only form T? I want to prevent that users can edit data of table T directly because form T makes some logging of changes to table LOG_T.

Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"

 

[GBall]

make all your controls unbound and have a confirm change button.
In the onclick event of the button, perform any validation and manually update the tables from there.
It's what I always do to retain control of my database.

regards,
Graham

 

[nagyf]

My another idea:

Change to False And ('Original Rule')[/color] the validation rule of the table in the login form.
The form will cut the 'False And (' and from the beginning and the ')' from the end.

I can similarly embed the sekelection conditions WHEN clauses of the dangerous queries in False And ('Original Selection Condition').

The form will the a string function to normalize the selection condition before the execution of the query
the it will reset it after it with another str function:

qdf.sql = remove_false_and(qdf.sql)
'before the execution of the query
on error resume next
qdf.execute
qdf.sql = insert_false_and(qdf.sql)
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"

 

[MichaelRed]

For your first problem (always use the Form). Split the db (if not already done). "Hide" the back end where the users will not (at least easily) find it. Remove the links to the Back end. Connect to all of the tables through a second dbengine.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[nagyf]

> "Hide" the back end where the users will not (at least easily) find it. Remove the links to the Back end. Connect to all of the tables through a second dbengine.

How?
Too complicated.
What happens if somebody finds the back end with does not contain any forms?
What is the place of the functions used in te queries and the row sources of the fields of the tables: font or back end? Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"

 

[nagyf]

"Split database to front end and back end."
Ezzel baszogat egy nagyokos évek óta.
Sorry for the Hungarian phrase, but I have my slang dictionary at home on the bookshelf.
I am afraid of this:
1. I lose referential integrity.
2. The back end remains naked. Smart users will find and use it directly.
3. So it is impossible to have a clean data file without any modules.

....
My next idea dreamed on the bus downtown.

A. Check "hidden" attribute of the sensitive forms.
B. Hide database window.
C. Disable "Unhide..." -->"database window" on the toolbar.
D. Disable "Toolbars"-->"Customize"
...
Write my "Own Database Window" which makes the following when Table X or Query Xis selected.

If exists Macro X then open that
else if exist Form X then open that
else if description of Table/Query X contain "do not use directly then exit
else open Table/Query X. Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"

 

[nagyf]

I had started to work out a solution.

Its essence:
A hidden form is running in the background.
In its timer event (once in every minute) it asks for the name and the type of the active data object in the screen.
If the active data objects is not a table then it does nothing.
If it is table in design view then it does nothing either. If it finds that the active data object is in normal view then it makes the following checks.
1) It looks for the sentence beginning with 'You must not use the table directly' and ending with a full stop in the description of the table.
2) If the above sentece is found then it check the sentence for the continuation 'but only with {form|macro} &quot;<name>&quot;'.
3) Displaying of an assistant balloon with bullets:
&quot;We do not recommend to use this table directly. Use {form|macro} &quot;<name>&quot; instead.
[ul][li]Close the table.
[/li] [li] Close the table and open it in read-only mode.
[/li] [li] Use it with the recommended handler.
[/li] [li]Ignore and use the table for writing.&quot;
[/li] [/ul]
The third case is unavailable if the handler form does not exist.
The last case is available only for Admin users.
---------------------------------------------------------
My remaining problems in the accurate coding of this idea:
1) I can check from the container &quot;Scripts&quot; whether macro &quot;M&quot; exists. How can I check whether macro &quot;M.A&quot; exists?
2) How can I see that the table in active screen object is opene din read only mode? In this case the algorithm should not display the balloon.
3) Is there any collection containing info about all windows within the MDI environment of the Access 97?
An ugly way to get info about the windows:
Set CurrentObject = Screen.ActiveDataObject
Do
SendKeys &quot;^{F6}&quot;
Set OtherObject = Screen.ActiveDataObject
'Evaluate properties of the OtherObject.
Loop Until OtherObject=CurrentObject

----------------------------------------------------------
4) See other thread: &quot;Message boxes with more specific buttons than Ok, Cancel&quot;.

I will send the code with mesages and variable translated
from Hungarian to English only if anybody is interested in it.
Regards
Frank
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' &quot;The goal of the life is the struggle itself&quot;

 

[MichaelRed]

... and you were afraid of my suggestion ... ?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[nagyf]

Mike,

I admit that my solution is complicated.
Where can I learn the tricks of using two dbengines simultaneously?

Please send me a very simple example application consisting of three files:
File#1 having no tables just a form as its starter form,
using table#1 in file#2, and using table#2 in file#3 in its subform. The form must work in the second dbengine.

This should model the simple case when all objects are in the same file#0.mdb, and
table#1 and table#2 are related with checked referential integrity and cascaded update and unchecked cascaded delete.

Anyway, users will learn that if they open file#2 then they can quicker delete a record form table#1 than waiting for the time-consuming logon procedure. Next time the less impatient user will find 100 detail records in table#2 without the corresponding master records in table#1.

-----------------------------------------------------
Anyway, I have a simpler idea to my original problem:
Set to &quot;true&quot; the &quot;hidden&quot; attribute of the tables which must not be used directly.
...
Write a form with buttons and/or combo boxes holding the names of the hidden tables. On Click over Table#1 the corresponding handler will start (RunMacro/OpenForm Table#1_Hander) instead of the OpenTable.

Regards
Frank Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' &quot;The goal of the life is the struggle itself&quot;