A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do you mulitply in a field?



 
 
Thread Tools Display Modes
  #51  
Old August 23rd, 2007, 02:44 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How do you mulitply in a field?

On 23 Aug, 13:03, "BruceM" wrote:
Knowledgeable people have pointed out that there are many performance
factors to be considered before physical ordering on the disk, and have
questioned whether such physical ordering is really relevant, at least in an
office-sized database. While I accept the validity of a multi-field index
(or several single-field indices), I don't accept the physical ordering
argument.


Yes, there are many factors to consider as regards performance. Yes,
different people will rate these factors differently. But here's a
link to a MSDN article about query performance in an Access database
which places physical ordering at the top of its list of "Tips to
improve query performance":

Information about query performance in an Access database
http://support.microsoft.com/kb/209126

"To improve query performance, try these tips: Compact your database.
When you compact your database you can speed up queries. When you
compact your database, the records of the table are reorganized so
that the records reside in adjacent database pages that are ordered by
the primary key of the table. This improves the performance of the
sequential scans of records in the table because only the minimum
number of database pages now have to be read to retrieve the records
that you want."

Here is another a MSDN article which has a list of "Optimization Tips
and Techniques" and physical ordering is the second one listed:

Microsoft Jet 3.5 Performance Overview and Optimization Techniques
http://msdn.microsoft.com/archive/de...baseEngine.asp

"From a performance perspective, there are many reasons to frequently
compact a database. One reason is that compacting will create a new
database that stores all table rows in a contiguous order. If a
primary key or unique index is defined, the rows will be sorted in
order of the primary key or unique index. This allows Microsoft Jet to
take full advantage of its read-ahead cache and also reduces disk I/O
when doing sequential scans of a table."

Out of interest, the first one listed is "Prevent Unnecessary Query
Recompilation" and seems to relate only to stored queries and then
only in conjunction with DAO's infamous memory leak bug (which I
understand has since been fixed).

In case it's not clear what these articles are getting at, here are a
few aircode examples of 'sequential scans' that will favour physical
ordering:

SELECT *
FROM TelephoneDirectory
WHERE last_name = 'Collins';

SELECT *
FROM SalaryHistory
WHERE employee_number = '454818181'
AND start_date
BETWEEN #2006-01-01 00:00:00# AND
AND #2006-12-31 23:59:59#;

SELECT employee_number,
COUNT(*) AS salary_review_tally
FROM SalaryHistory
GROUP BY employee_number;

When was the last time you used BETWEEN or GROUP BY on an autonumber
column?

Now, be fair, where are these knowledgeable people's lists and where
exactly does physical ordering appear in their lists? If they've
omitted physical ordering from their lists, how do we know they've
even considered it? TIA.

I grant permissions to groups, which is the most common scenario,
but is in the same category as granting permissions to individual users.


OK so you give people the permissions...

Even if their intentions are the best, they should not be able to get into
the tables directly.


Then why give you give them (via their groups) the permissions to do
so?

If they have the permissions of which you speak ...


... you just confirmed that they do...

...they are able to add
incorrect data that fulfill the table-level constraints.


So why not conclude, "I'd better put in some table-level constraints
to prevent incorrect data"?

Integrity constraints are one thing, but I don't know that my time would be
well spent devising what amount to backup validation rules at the table
level.


Funny, I start with the table-level constraints first (i.e. they are
not back ups) then sigh when I think about having to duplicate the
effort in the front end g.

You design your tables *before* you design your forms, right? Why not
the same for constraints?

I have attempted to agree with you, only to have you
fire back as if I had never said anything. That gets old in a hurry.


Oops, sincere apologies. Please believe I don't do that on purpose. I
think it must happen only when I've missed your point (or I think
you've missed my point when you haven't etc).

Jamie.

--


  #52  
Old August 23rd, 2007, 04:33 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default How do you mulitply in a field?


"Jamie Collins" wrote in message
ups.com...
On 23 Aug, 13:03, "BruceM" wrote:
Knowledgeable people have pointed out that there are many performance
factors to be considered before physical ordering on the disk, and have
questioned whether such physical ordering is really relevant, at least in
an
office-sized database. While I accept the validity of a multi-field
index
(or several single-field indices), I don't accept the physical ordering
argument.


Yes, there are many factors to consider as regards performance. Yes,
different people will rate these factors differently. But here's a
link to a MSDN article about query performance in an Access database
which places physical ordering at the top of its list of "Tips to
improve query performance":

Information about query performance in an Access database
http://support.microsoft.com/kb/209126


"To improve query performance, try these tips: Compact your database.
When you compact your database you can speed up queries. When you
compact your database, the records of the table are reorganized so
that the records reside in adjacent database pages that are ordered by
the primary key of the table. This improves the performance of the
sequential scans of records in the table because only the minimum
number of database pages now have to be read to retrieve the records
that you want."


I never argued against compacting.


Here is another a MSDN article which has a list of "Optimization Tips
and Techniques" and physical ordering is the second one listed:

Microsoft Jet 3.5 Performance Overview and Optimization Techniques
http://msdn.microsoft.com/archive/de...baseEngine.asp

"From a performance perspective, there are many reasons to frequently
compact a database. One reason is that compacting will create a new
database that stores all table rows in a contiguous order. If a
primary key or unique index is defined, the rows will be sorted in
order of the primary key or unique index. This allows Microsoft Jet to
take full advantage of its read-ahead cache and also reduces disk I/O
when doing sequential scans of a table."

Out of interest, the first one listed is "Prevent Unnecessary Query
Recompilation" and seems to relate only to stored queries and then
only in conjunction with DAO's infamous memory leak bug (which I
understand has since been fixed).


There's also something about judicious use of indexes, and something about
the use of expressions in subqueries. The latter includes the
recommendation that expressions be used in the front end (i.e. the form) to
produce the necessary information in subforms. Anyhow, it looks like a good
article. I have bookmarked it.

In case it's not clear what these articles are getting at, here are a
few aircode examples of 'sequential scans' that will favour physical
ordering:

SELECT *
FROM TelephoneDirectory
WHERE last_name = 'Collins';

SELECT *
FROM SalaryHistory
WHERE employee_number = '454818181'
AND start_date
BETWEEN #2006-01-01 00:00:00# AND
AND #2006-12-31 23:59:59#;

SELECT employee_number,
COUNT(*) AS salary_review_tally
FROM SalaryHistory
GROUP BY employee_number;

When was the last time you used BETWEEN or GROUP BY on an autonumber
column?


Gosh, I can't remember when I last did that. But what a probing and
thought-provoking question!! sarcasm
I have been saying the same thing over and over, and will stop doing so
after this message. I do not rely on autonumber to make a record unique. I
use autonumber when no "natural" field meets my needs for an Access PK.
You have not answered my objection to linking tables through fields that are
subject to change.


Now, be fair, where are these knowledgeable people's lists and where
exactly does physical ordering appear in their lists? If they've
omitted physical ordering from their lists, how do we know they've
even considered it? TIA.


I have seen the stuff about compacting. No argument. Once again, I am not
arguing against compacting. That is to say, I think compacting is a good
thing, and I have observed performance differences after compacting.
However, I have seen information about how indexing choices can degrade
performance (for instance, when using indexes on City and PostalCode
fields). As the article points out, there is no single correct answer about
indexing.


I grant permissions to groups, which is the most common scenario,
but is in the same category as granting permissions to individual users.


OK so you give people the permissions...

Even if their intentions are the best, they should not be able to get
into
the tables directly.


Then why give you give them (via their groups) the permissions to do
so?


I don't. I never said I did. Unless you know some sort of back door (or
are a member of the appropriate group), you would not be able to open the
back end directly.


If they have the permissions of which you speak ...


.. you just confirmed that they do...


Not to access the tables directly.


...they are able to add
incorrect data that fulfill the table-level constraints.


So why not conclude, "I'd better put in some table-level constraints
to prevent incorrect data"?


I wouldn't know how to limit at the table level the list of models if "SUV"
is chosen as the class of vehicle. So far I see no reason to learn.


Integrity constraints are one thing, but I don't know that my time would
be
well spent devising what amount to backup validation rules at the table
level.


Funny, I start with the table-level constraints first (i.e. they are
not back ups) then sigh when I think about having to duplicate the
effort in the front end g.


OK, fine.

You design your tables *before* you design your forms, right? Why not
the same for constraints?


Of course I design the tables (and relationships) first. However, I have
changed elements of the table design after development of forms, etc. is
well under way. I have also gone back and re-thought the design. You?


I have attempted to agree with you, only to have you
fire back as if I had never said anything. That gets old in a hurry.


Oops, sincere apologies. Please believe I don't do that on purpose. I
think it must happen only when I've missed your point (or I think
you've missed my point when you haven't etc).

Jamie.

--




  #53  
Old August 24th, 2007, 11:29 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How do you mulitply in a field?

On 23 Aug, 16:33, "BruceM" wrote:
I have been saying the same thing over and over, and will stop doing so
after this message. I do not rely on autonumber to make a record unique.


I've heard you say this over and over and I believe you. Good for you.
Neither do I. I'm glad we agree. Actually, I think we agree on most
things. BTW no sarcasm intended.

You have not answered my objection to linking tables through fields that are
subject to change.


I've not tried to *refute* your objection because I think you have
every right to hold that view. I cannot tell you whether joining your
tables on fields that are subject to change is good or bad or
indifferent because I don't know anything about their structure or
content (but, sight unseen, my money would be on 'indifferent').

However, I feel I have *answered* your objection e.g. on 16 August in
this thread when I said, "I think the SQL DBMS should store the key
value only once and use an internal surrogate." We both agree that
physically storing the key value in all the referencing tables is a
Bad Thing but we have different responses to it. My response is to
shrug: it's a 'feature' of the product and if I thought the product's
performance, CASCADE referential actions, etc were not up to the job
then the product is not fit for purpose and it's a free market. Your
response is to roll your own surrogate using autonumber. I don't feel
I've tried to push my approach down your throat though I have
suggested you to consider the effects of placing a *physical* index on
an autonumber column and given some examples of how a sole autonumber
PK for referencing in FKs can work against some design patterns such
as subclassing. I've acknowledged that your way valid but is not for
me, primarily because it makes referencing tables' data harder to
read, but have wished you luck with yours.

In the same spirit, may I press you further for detail of,
"Knowledgeable people [who] have pointed out that there are many
performance factors to be considered before physical ordering on the
disk," please? In my experience, most supposedly knowledgeable people
give it no consideration; others don't seem to believe that clustering
actually happens in Jet at all!

I think compacting is a good
thing, and I have observed performance differences after compacting.
However, I have seen information about how indexing choices can degrade
performance (for instance, when using indexes on City and PostalCode
fields). As the article points out, there is no single correct answer about
indexing.


Again, we agree there are many factors to performance. You said you'd
heard physical ordering was not at the top of some lists. I've shown
two from Microsoft where it is.

One thing I'm still not clear on. You said earlier, "I don't accept
the physical ordering argument." I thought you meant you did not see
how it can be relevant to either choice of PK or performance
implications. Did I misunderstand what you meant? Has anything I've
presented caused you to revise this earlier held view? I get how you
choose your PKs (unchanging field values, single column FKs) but do
you get how physical ordering might affect PK choice for someone else?

Then why give you give them (via their groups) the permissions to do
so?


I don't. I never said I did.


It seems I'm not understanding your approach. I see two basic
approaches:

1) Users can perform updates to your tables' data via your application
because you have granted the privileges on the tables to the users
(but does not preclude the same users from performing updates to your
tables' data via another route).

2) You have not granted the privileges on the tables to the users but
they can still perform updates to your tables' data via your
application because you have granted privileges on the application to
the users and granted privileges on the tables to your application
(the advantage here is that they can *only* perform updates via your
application).

Is your approach one of these, a variation or something completely
different? Note a common third approach is that everyone runs as an
administrator but that would not seem to apply to you.

Jamie.

--


  #54  
Old August 24th, 2007, 01:37 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default How do you mulitply in a field?


"Jamie Collins" wrote in message
oups.com...
On 23 Aug, 16:33, "BruceM" wrote:
I have been saying the same thing over and over, and will stop doing so
after this message. I do not rely on autonumber to make a record unique.


I've heard you say this over and over and I believe you. Good for you.
Neither do I. I'm glad we agree. Actually, I think we agree on most
things. BTW no sarcasm intended.

You have not answered my objection to linking tables through fields that
are
subject to change.


I've not tried to *refute* your objection because I think you have
every right to hold that view. I cannot tell you whether joining your
tables on fields that are subject to change is good or bad or
indifferent because I don't know anything about their structure or
content (but, sight unseen, my money would be on 'indifferent').

However, I feel I have *answered* your objection e.g. on 16 August in
this thread when I said, "I think the SQL DBMS should store the key
value only once and use an internal surrogate." We both agree that
physically storing the key value in all the referencing tables is a
Bad Thing but we have different responses to it. My response is to
shrug: it's a 'feature' of the product and if I thought the product's
performance, CASCADE referential actions, etc were not up to the job
then the product is not fit for purpose and it's a free market. Your
response is to roll your own surrogate using autonumber. I don't feel
I've tried to push my approach down your throat though I have
suggested you to consider the effects of placing a *physical* index on
an autonumber column and given some examples of how a sole autonumber
PK for referencing in FKs can work against some design patterns such
as subclassing. I've acknowledged that your way valid but is not for
me, primarily because it makes referencing tables' data harder to
read, but have wished you luck with yours.


I thought a PK field is indexed, no duplicates by default (and without
options to change those aspects).

In the same spirit, may I press you further for detail of,
"Knowledgeable people [who] have pointed out that there are many
performance factors to be considered before physical ordering on the
disk," please? In my experience, most supposedly knowledgeable people
give it no consideration; others don't seem to believe that clustering
actually happens in Jet at all!


In this listing physical ordering is not listed directly, but at least one
of the referenced articles discusses indexing. I don't know how indexing
and physical ordering on the disk are related.


In previous discussions several people who have demonstrated a good command
of Access and of databases in general questioned the importance of clustered
indexes for physical ordering on the disk.
http://groups.google.com/group/micro...cf0d56 a19f15

http://groups.google.com/group/micro...209 cee5d14d7

There is a variety of views within those threads.

By the way, if there is a better way to reference newsgroup discussions I
would be glad to know about it.

I think compacting is a good
thing, and I have observed performance differences after compacting.
However, I have seen information about how indexing choices can degrade
performance (for instance, when using indexes on City and PostalCode
fields). As the article points out, there is no single correct answer
about
indexing.


Again, we agree there are many factors to performance. You said you'd
heard physical ordering was not at the top of some lists. I've shown
two from Microsoft where it is.


If the listings are in order of importance, then I guess you are correct.
As I have stated several times already, I finally understand (I think) a
distinction you made between PKs and indexes.

I still doubt there is a performance issue between indexing on an arbitrary
number such as EmployeeID and and arbitrary PK. Also, I may choose to list
employees by lastname, firstname, but have many occasions to order or group
by department, startdate, or what have you.
BTW, one of the articles states that the rows will be ordered according to
the primary key OR the unique index, but leaves me guessing as to what
happens if both exist.

One thing I'm still not clear on. You said earlier, "I don't accept
the physical ordering argument." I thought you meant you did not see
how it can be relevant to either choice of PK or performance
implications. Did I misunderstand what you meant? Has anything I've
presented caused you to revise this earlier held view? I get how you
choose your PKs (unchanging field values, single column FKs) but do
you get how physical ordering might affect PK choice for someone else?


I probably did not accept the argument because I may order on one of several
fields, depending on how I wish to present the data. If one of those fields
is the unique index, but it is not the most common ordering I will use, I
wonder if there is an advantage.

Then why give you give them (via their groups) the permissions to do
so?


I don't. I never said I did.


It seems I'm not understanding your approach. I see two basic
approaches:

1) Users can perform updates to your tables' data via your application
because you have granted the privileges on the tables to the users
(but does not preclude the same users from performing updates to your
tables' data via another route).

2) You have not granted the privileges on the tables to the users but
they can still perform updates to your tables' data via your
application because you have granted privileges on the application to
the users and granted privileges on the tables to your application
(the advantage here is that they can *only* perform updates via your
application).


As far as I know, properly implemented user-level security prevents
back-door updates to the tables. I do know that there is no direct way of
opening the back end (and getting to the table data that way) without going
through the secure mdw file (although I am aware that user-level security is
not as robust as other methods available to SQL). What you described with
the Excel thing is in fact a back-door update, undertaken by somebody who is
deliberately and knowingly circumventing the intended route for entering
data. In my book that is hacking, not an innocent attempt to be more
efficient.
Users have full permissions to the table links in the FE, and limited
permissions (according to the group of which they are a member) to the
actual BE tables.
All updates are intended to happen by way of the user interface. As of now
users can still use the Shift key bypass, but there are ways to prevent
that. I haven't had the chance to implement that yet, as other matters have
my immediate attention, but plan to do so before long. As it is, unsecured
databases have been unmolested for quite some time, and very few people even
know about the Shift bypass option, so I'm not too concerned in the
immediate short term.

Is your approach one of these, a variation or something completely
different? Note a common third approach is that everyone runs as an
administrator but that would not seem to apply to you.

Jamie.

--




  #55  
Old August 24th, 2007, 04:21 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How do you mulitply in a field?

On 24 Aug, 13:37, "BruceM" wrote:
In previous discussions several people who have demonstrated a good command
of Access and of databases in general questioned the importance of clustered
indexes for physical ordering on the disk.
http://groups.google.com/group/micro...browse_thread/...


There's 101 posts in that thread and you linked to one which I wrote!
Who do you think is the knowledgeable person in this thread? I recall
David W Fenton being particularly obstructive telling he knew of lots
of things but wouldn't deign to reveal them and ironically did bother
to come back several times to tell us he couldn't be bothered g. The
only relevant post I can pick out is this:

[David W Fenton] Given that with Jet you can only have the one clustered index, I
really think this is a pretty irrelevant consideration. I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key).

So, I just don't see any practical benefit in your pointing out the
performance advantage of the clustered index.


You can only have one clustered in index period -- it's not a Jet
restriction, more like the laws of physics g! In *this* thread I
posted the salary history example where dates are part of the
candidate keys and we see history table designs cropping up regularly
in the Access groups. And he answered his own point about including
such a date in a compound key i.e. make it the first column in the
key's definition. I generally get the feeling he was looking at things
the wrong way to be able to see my point i.e. he was considering
considers the clustering resulting from his existing choice of PK,
whereas I'm proposing one identifies the columns for clustering then
choose the PK to fit. There are some later exchanges but he seems to
dismiss the whole idea ("I think your suggestion is bloody stupid", "I
think you're mis-using your RDBMS") without giving solid reasons.

http://groups.google.com/group/micro...tablesdbdesign...


That one's you and I again! And the only other person who commented on
clustering is David W Fenton again but this time he's actually
acknowledged the point:

[David W Fenton] I can't imagine a telephone number table that would *need* a PK that
didn't involve the parent foreign key.

In any event, you'd have to have hundreds of thousands of records in
the telephone table for it to make a noticeable difference.


Again, he seemingly hasn't made the shift to, "What if you chose the
PK to purposely change the clustering...?" But he does seem to see the
light. While I'm not sure he's correct when he says "hundreds of
thousands of records" (the MSFT articles don't hint at this), tables
of such proportions are hardly a rare occurrence in the Access
groups.

One thing that bothers me is there is no frame of reference in this
thread e.g. David W Fenton does not give any comparison to other
optimization techniques. The Microsoft articles rated it highly, after
all.

There is a variety of views within those threads.


Indeed. Notice how a few people needed convincing that clustering
existed in Jet at all, after which they dropped out of the discussion.

I still doubt there is a performance issue between indexing on an arbitrary
number such as EmployeeID and and arbitrary PK.


Agreed. I can't see either would offer any advantage. Clustering is
better suited to meaningful data.

Also, I may choose to list
employees by lastname, firstname, but have many occasions to order or group
by department, startdate, or what have you.


You then have to look at the relative usage frequency, importance,
etc. I didn't say optimization was easy g.

BTW, one of the articles states that the rows will be ordered according to
the primary key OR the unique index, but leaves me guessing as to what
happens if both exist.


PK takes precedence, as advised by Lyle Fairfield in the first thread
you linked to.

I do know that there is no direct way of
opening the back end (and getting to the table data that way) without going
through the secure mdw file

Users have full permissions to the table links in the FE, and limited
permissions (according to the group of which they are a member) to the
actual BE tables.

What you described with
the Excel thing is in fact a back-door update, undertaken by somebody who is
deliberately and knowingly circumventing the intended route for entering
data. In my book that is hacking, not an innocent attempt to be more
efficient.


If they've used their own credentials via your mdw (i.e. utilizing
your security mesures rather than circuventing them) to connect to the
data in the BE from within Excel then in my book that is legitimate
usage and IMO should be accommodated.

Jamie.

--


  #56  
Old August 24th, 2007, 06:52 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default How do you mulitply in a field?

"Jamie Collins" wrote in message
oups.com...
On 24 Aug, 13:37, "BruceM" wrote:
In previous discussions several people who have demonstrated a good
command
of Access and of databases in general questioned the importance of
clustered
indexes for physical ordering on the disk.
http://groups.google.com/group/micro...browse_thread/...


There's 101 posts in that thread and you linked to one which I wrote!


I was trying to point to the thread, not to a specific message. Albert
argued that there is no reason not to use PK. Presumably if a
non-maintained clustered index was important that would have been worth
mentioning. He did say that there are perfectly valid reasons to use a
natural key, too.
Amy had some observations on the subject, including regarding it as silly
(or some such) that a PK (or non-null unique index) would be chosen for
clustering reasons.

Who do you think is the knowledgeable person in this thread? I recall
David W Fenton being particularly obstructive telling he knew of lots
of things but wouldn't deign to reveal them and ironically did bother
to come back several times to tell us he couldn't be bothered g. The
only relevant post I can pick out is this:

[David W Fenton] Given that with Jet you can only have the one
clustered index, I
really think this is a pretty irrelevant consideration. I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key).

So, I just don't see any practical benefit in your pointing out the
performance advantage of the clustered index.


You can only have one clustered in index period -- it's not a Jet
restriction, more like the laws of physics g! In *this* thread I
posted the salary history example where dates are part of the
candidate keys and we see history table designs cropping up regularly
in the Access groups. And he answered his own point about including
such a date in a compound key i.e. make it the first column in the
key's definition. I generally get the feeling he was looking at things
the wrong way to be able to see my point i.e. he was considering
considers the clustering resulting from his existing choice of PK,
whereas I'm proposing one identifies the columns for clustering then
choose the PK to fit. There are some later exchanges but he seems to
dismiss the whole idea ("I think your suggestion is bloody stupid", "I
think you're mis-using your RDBMS") without giving solid reasons.

http://groups.google.com/group/micro...tablesdbdesign...


That one's you and I again! And the only other person who commented on
clustering is David W Fenton again but this time he's actually
acknowledged the point:



One thing that bothers me is there is no frame of reference in this
thread e.g. David W Fenton does not give any comparison to other
optimization techniques. The Microsoft articles rated it highly, after
all.

There is a variety of views within those threads.


Indeed. Notice how a few people needed convincing that clustering
existed in Jet at all, after which they dropped out of the discussion.


I guess that makes you the winner. After this discussion you can add me to
the list of the vanquished, because I don't feel like extending this much
longer.


I still doubt there is a performance issue between indexing on an
arbitrary
number such as EmployeeID and and arbitrary PK.


Agreed. I can't see either would offer any advantage. Clustering is
better suited to meaningful data.

Also, I may choose to list
employees by lastname, firstname, but have many occasions to order or
group
by department, startdate, or what have you.


You then have to look at the relative usage frequency, importance,
etc. I didn't say optimization was easy g.

BTW, one of the articles states that the rows will be ordered according
to
the primary key OR the unique index, but leaves me guessing as to what
happens if both exist.


PK takes precedence, as advised by Lyle Fairfield in the first thread
you linked to.

I do know that there is no direct way of
opening the back end (and getting to the table data that way) without
going
through the secure mdw file

Users have full permissions to the table links in the FE, and limited
permissions (according to the group of which they are a member) to the
actual BE tables.

What you described with
the Excel thing is in fact a back-door update, undertaken by somebody who
is
deliberately and knowingly circumventing the intended route for entering
data. In my book that is hacking, not an innocent attempt to be more
efficient.


If they've used their own credentials via your mdw (i.e. utilizing
your security mesures rather than circuventing them) to connect to the
data in the BE from within Excel then in my book that is legitimate
usage and IMO should be accommodated.


Is there documentation that what you describe is possible?

Jamie.

--




  #57  
Old September 3rd, 2007, 11:42 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How do you mulitply in a field?

On 24 Aug, 18:52, "BruceM" wrote:
If they've used their own credentials via your mdw (i.e. utilizing
your security mesures rather than circuventing them) to connect to the
data in the BE from within Excel then in my book that is legitimate
usage and IMO should be accommodated.


Is there documentation that what you describe is possible?


You serious? Try these KB articles:

ACC97: How to Use IDC Files to Query a Secure Microsoft Access
Database
http://support.microsoft.com/kb/161172

How to open a secured Access database in ADO through OLE DB
http://support.microsoft.com/kb/191754

How To Open an Access Database with User Security [using DAO]
http://support.microsoft.com/kb/163002

How to open a secured Microsoft Access database in Visual C++
http://support.microsoft.com/kb/270663

HOW TO: Connect to a Security-Enhanced Microsoft Access Database by
Using Microsoft Visual Studio .NET
http://support.microsoft.com/kb/823927

How To Use ADO to Refresh/Create Linked Table for Password Secured Jet
4.0 Database
http://support.microsoft.com/kb/240222

etc etc

Try these simple steps: Open Excel 2003. On the menu, choose: Data,
Import External Data, New Database Query. In the resulting 'Choose
Data Source' dialog, select New Data Source, and click OK. In the
resulting, 'Create New Data Source' dialog, step 1: enter a name (for
the DSN), step 2: choose Microsoft Access Driver, step 3: click the
'Connect' button. In the resulting 'ODBC Microsoft Access Setup'
dialog, choose you mdb 'Database' (I recommend you use a copy here),
choose your mdw 'System Database', click the 'Advanced' button and
enter a valid username and password combination. OK to each dialog to
get you back to the 'Choose Data Source' dialog, select the newly-
created DSN and hit OK to take you to the main MSQuery dialog (cancel
the 'Add tables' dialog if it shows on start up). Hit the SQL button,
type in a Jet syntax SQL statement (e.g. CREATE VIEW DropMe AS SELECT
1, click the OK button, continue past the odd 'query cannot be
displayed graphically' message and the SQL will be executed.

Jamie.

--



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.