Protecting a field (column) in an Access table

J

Jo

Is it possible to protect a field (column) in an Access master table? I have
a database that has certain columns that I want protected from editing.
 
J

Jeff Boyce

Jo

How ... "resourceful" ... are your users? If you are concerned about casual
lookie-lou's, don't show them the tables at all, use forms.

If you have data that cannot, under any circumstances, be edited, you may
need to engage in some serious security design, perhaps even by using a
SQL-Server-based back-end for the data.

Where in this continuum does your situation fall?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jo

No one but my supervisor and I will be entering data into the master table.
My supervisor just wants certain columns protected so that neither him or I
accidently change data in columns from past years. Is this at all possible?
 
D

Dale Fye

Jo,

Sounds like poor database design. Data from "past years" should be in
records that are denoted by the year, not in columns as in a spreadsheet.

My recommendation, besides normalizing your data, would be to create a data
entry form, with only those columns that you want to be able to edit. If you
and your boss are going to be the only ones using this form, you might make
it a datasheet, so that you can look at your data as if it was in a table,
but I generally frown on making datasheet forms visible to my users.

HTH
Dale
 
J

Jamie Collins

Dale Fye said:
Sounds like poor database design. Data from "past years" should be in
records that are denoted by the year, not in columns as in a spreadsheet.
Click to expand...

I read it as "data from past years" and you read it as "each column models
different years".

Even so, Ithink the requirements have been inadequately specified e.g. they
enter data on 31 December and the next day they can't amend it...? Perhaps
the OP could kindly clarify.
If you
and your boss are going to be the only ones using this form, you might make
it a datasheet, so that you can look at your data as if it was in a table,
but I generally frown on making datasheet forms visible to my users.
Click to expand...

Huh? A table is a logical entity and all data ultimately resides in a table,
so what do you mean by "look at your data as if it was in a table"? TIA.

Jamie.

--
 
J

Jo

Let me clarify...There is one master table in which I have columns for 2006,
2007 and 2008 (check marks in the columns denote whether the property is
active in that year). There are also 2006, 2007 and 2008 expense columns and
the same for work done and type of work anticipated for all three years. To
easily see what has happened from year to year, I thought this format was
easiest, but maybe I was wrong? It seems easy enough to run queries, etc. if
all the info is in one table. I want to protect the columns from 2006 so no
one accidently changes the info.
 
J

John W. Vinson

Let me clarify...There is one master table in which I have columns for 2006,
2007 and 2008 (check marks in the columns denote whether the property is
active in that year). There are also 2006, 2007 and 2008 expense columns and
the same for work done and type of work anticipated for all three years. To
easily see what has happened from year to year, I thought this format was
easiest, but maybe I was wrong? It seems easy enough to run queries, etc. if
all the info is in one table. I want to protect the columns from 2006 so no
one accidently changes the info.
Click to expand...

This format is appropriate for a spreadsheet, but it is WRONG for a relational
database. For one thing, it requires that you change the design of your
tables, your forms, reports, queries, etc. every year.

A "tall thin" design with a field for the year and just additional records
would be much easier to maintain. You could use Queries to select just the
current year's data in an editable form, and previous years' data as a
snapshot query which would not be updateable. You could also create a
(non-updateable) Crosstab query to recreate the spreadsheet look. You could
still open the table directly and edit there (absent some extensive database
security) but one shouldn't be opening table datasheets routinely in any case.

John W. Vinson [MVP]
 
Top