How to Tell If Any Table Is Modified

[Guest]

Hi all:

I'm a newbie to MS Access. I have an Access 2000 database with many tables
and users can insert/update/delete rows to any of these tables. Is there any
way I can programmatically know which tables have been modified? Please help.

Thanks,

Tom

 

[John W. Vinson]

Hi all:

I'm a newbie to MS Access. I have an Access 2000 database with many tables
and users can insert/update/delete rows to any of these tables. Is there any
way I can programmatically know which tables have been modified? Please help.

No.

That's one of many, many reasons why users should be given Forms as the *only*
tool to update tables; you can put tracking code on the BeforeUpdate event of
a form to detect and (if you desire) log changes. Tables have no usable events
and Access doesn't support table triggers, so if users have the ability to
edit tables directly, you have no control and no record.

John W. Vinson [MVP]

 

[Jamie Collins]

No.

That's one of many, many reasons why users should be given Forms as the *only*
tool to update tables

Are you proposing the OP revokes privileges from users and groups and
instead granting them to the 'application', a bit like SQL Server's
concept of an 'application role'? If so, I don't know of any native
support for this functionality either in Access/Jet so can you please
clarify how you propose the OP implements your proposal e.g. how to
ensure the 'application role' is indeed being used only by the
application. TIA.

Jamie.

--

 

[George Nicholson]

I suspect John is suggesting that the application be built in such a way
that user's have no way of getting to the tables directly (i.e., they never
see the database window, and special keys are disabled: both are startup
options). Users can only make changes to the data in tables via the forms &
queries provided by the developer, where any or all changes can be tracked
as desired.

This can be done even if user-level security isn't being used, so privileges
are immaterial.

HTH,

 

[Jamie Collins]

I suspect John is suggesting that the application be built in such a way
that user's have no way of getting to the tables directly

No doubt. So why isn't he telling us how to achieve that...?

(i.e., they never
see the database window, and special keys are disabled: both are startup
options). Users can only make changes to the data in tables via the forms &
queries provided by the developer, where any or all changes can be tracked
as desired.

This can be done even if user-level security isn't being used, so privileges
are immaterial.

Consider this, posted in the Excel groups by Access groups regular,
Dale Fye

http://groups.google.co.uk/group/microsoft.public.excel.misc/msg/ee3ea406d49b4c03
Excel as data entry form?
"I will ultimately need to pull this data into an Access database."

It is very common to use Excel to query data Access data. Take a
further look in the Excel groups! If you are not using 'user level
security' then the same Excel users can execute SQL statements to make
updates? Put another way, how are you going to force these Excel users
to "only make changes to the data in tables via the forms & queries
provided by the developer"?

Automating Access requires much VBA programming, whereas Excel users
are provided with an environment called MSQuery that makes this really
easy to executing SQL against Access in Excel user interface. I'm not
alluding to malicious usage: many people are told by their managers to
use Excel for Access data and thus given permissions; how would they
know that not using your forms to update data is verboten?

And that's just Excel. What about all the other ways of connecting to
the Access data: DAO, OLE DB, ODBC, etc?

Jamie.

--