I do not know about clustering behavior
See:
New Features in Microsoft Jet Version 3.0
http://support.microsoft.com/kb/137039
"New compacting method. Compacting the database now results in the
indices being stored in a clustered-index format. While the clustered
index isn't maintained until the next compact, performance is still
improved... The new clustered-key compact method is based on the
primary key of the table."
Defragment and Compact Database to Improve Performance
http://support.microsoft.com/kb/288631
"If a primary key exists in the table, compacting restores table
records into their primary key order. This provides the equivalent of
Non-maintained Clustered Indexes, and makes the read-ahead
capabilities of the Microsoft Jet database engine much more
efficient."
I can't make an informed decision about the air
pressure in my car's tires, either, because I don't understand the physics
involved. However, people who know about such things have suggested the
correct air pressure. I don't give it a lot of thought beyond that.
Here's the bare bones: you've have this thing (PRIMARY KEY) which
fulfils two main functions (null-free uniqueness and clustering
respectively) where one function sometimes compromises the other (a
sole unique column makes a poor cluster; multiple columns makes a
clustered compound index look like a verbose key) but you are also
given a second thing (NOT NULL UNIQUE) which performs one of the
functions (null-free uniqueness), which leaves the other function
(clustering) exclusive to the first thing (PRIMARY KEY).
I can't think of anything car tire/tyre pressure related that fits the
above so try this: Onedaywhen Cottage has central heating with zones,
each zone has a portable digita thermostat with integral clock. Mrs
Onedaywhen likes to keep the bedroom zone's thermostat on her bedside
table/nightstand because it makes a handy bedside clock. I know enough
to deduce she's chosen a lousy position for the thermostat e.g. the
heat from the bedside light sometimes causes the heating to switch off
prematurely. "Here," I say, "If you want to tell the time, use this
alarm clock. Then we can put the thermostat in a more appropriate
location." But we're at an impasse because people who know about such
things have suggested to her that the clock's timekeeping is accurate
by design because the boiler needs to know when to come on and she
does not understand the physics of printed circuit boards and oil-
fired boilers. But I keep telling her, "It's not just a clock, it's a
thermostat too. Use something else to tell the time." She replies, "I
do not know about thermostats." I've now given her some links to
articles on the manufacturer's website so the ball's in her court.
Again, I apply security so that the database cannot be changed outside of
the parameters I choose as the developer. The security can be circumvented
by a determined and knowledgeable user, but for my situation it is adequate.
It never occurred to me that somebody would take the Excel or some such
similar route, which sounds to me like a skilled and determined attempt to
undermine the data integrity. If the user can get through the security, no
approach to indexing is going to help. The answer, I'm afraid, is that I
don't see your point.
Again, I'm not referring to malicious attacks. If you give people
permissions on database object then in my experience sooner or later
someone will think it's a good idea to access those objects via Excel
e.g. to help them do their job better. This doesn't give them the
right to act irresponsibly, though. Perhaps they thought it would be
OK to correct some data directly in the table and are unaware you have
some data integrity correction code in one of your forms that has now
been inadvertently circumvented.
If I've been given a key to the stationary cupboard and I need to sign
some paper copy then I'll help myself to a pen. I still know that
having a key doesn't mean it's OK for me to go home today with ten
pens in my pocket. If you don't want me to have one of the pens, take
away my key; if you want to allocate pens yourself, keep them locked
in your desk drawer so that I *must* go through you to get a pen. It's
pretty clear that picking locks is illicit behaviour. But if I've used
my key to acquire a pen for my work, I don't see myself as having
successfully made a skilled and determined attempt to undermine your
stock control system -- I'm just trying to do my job.
Perhaps there is some middle ground e.g. you provide communal pens in
handy locations *but* keep them attached to chains, like they do in
banks, so that I don't do my usual trick of sticking it behind my ear
then forgetting and walking off with it.
Providing a SQL PROC or VIEW in the SQL DBMS is the provision of pens
(i.e. facilities me in doing my work without too much inconvenience),
and integrity constraints and data validation rules at the data engine
level are the chain (i.e. stops me messing up the system for my own
sake and others). Saying I have to always use your application's forms
is like saying I have to always use your pen, IMO too restrictive:
what if your pen has the wrong color/colour ink, doesn't work as well
as I like, you're not in the office today etc. And before you got
upset that I found a pen myself, had you mentioned that you are
considered to 'own' all the pens? If you did mention it, why do I have
a key to the pens cupboard?
Again, if you've found a way of granting privileges to the application
rather than to users (the latter being the most commonly seen
scenario) then please share your techniques, there is an Access MVP in
another thread wanting such information.
I want the
user to be informed by words of my choosing rather than default error
messages. I want to intercept the problem before then. The front end is
for the user's convenience, and I want it to be as convenient as is
possible.
Me too. Why not trap failures in the front end and in the data engine?
i.e. attempt to trap validation rules violations in the front end but
also have a validation rules in the data engine as a last line of
defence against things the front end guy missed, VBA code bugs, etc.
Errors generated by the engine can be caught so even when the front
end does encounter an 'unexpected' validation rule failure you can
trap the constraint name (or custom validation text, etc) to provide a
decent error message e.g. (pseudo code):
Private Sub btnOK_Click()
On Error Goto Err_Handle
' Do front end validation:
<<rules tests here, show message and exit on failure>>
' Attempt to write data
<<configure command here>>
On Error Resume Next
command.Execute
If Err.Number <> 0 Then
' Handle SQL errors here:
If InStr(Err.Description, "salary_periods_overlapping") > 0 Then
<<failure message for overlapping periods here>>
ElseIf InStr(Err.Description...
...
Else
<<handle unexpected SQL errors here>>
End If
End If
On Error Goto Err_Handle
<<clean up here>>
Exit Sub
Err_Handle:
Because there are no tables in the front end.
In the example, the SUVs table is not in the front end, yet you are
proposing make the test for vehicle_type='SUV' in the front end.
Some things are not so convenient to test in the front end and are a
lot easier to test with a database round trip e.g. duplicate entity
testing: easy to trap a unique key failure message but more work to
cache the entire table in memory to test for duplicates locally. Even
if testing for duplicates locally you wouldn't omit the unique
constraint from the database, so why omit the test vehicle_type='SUV'
from the SUVs table?
You know, I have actually tried to agree with you on some points.
Indeed. Did you want me to acknowledge that we're agree (or otherwise)
on certain points? I tend to concentrate efforts on the *unresolved*
points.
Jamie.
--
