[AccessD] Access Reserved words

jm.hwsn jm.hwsn at gmail.com
Wed Mar 9 14:29:09 CST 2011


Thanks, that makes a lot of sense.
I'll review where I've used them and make adjustments as needed.
Thanks again,
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, March 09, 2011 2:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Reserved words

Jim,

<<One performance hit was my use of domain functions.  I like domain
functions.  However, I'm told that they could possibly slow my database down
and that I should create an "equivalent saved query."  From my research
there are people on both sides of this issue.  Some say it makes no
difference and in some cases it's the best choice.  Other say never use
domain functions.  I say if it works - don't fix it.>>

  Couple of comments:

1. Domain functions are often slower, but not always. There was a very old
Smart Access article with timings that showed in some cases that domain
function could be faster then other methods. If in doubt, test.

2. There are custom procedures out on the net to replace the domain
functions, which give more flexibility and typically the same speed.

3. Domain functions should never be used inside of a SQL statement (query).
Reason being is that they cannot be optimized by the query parser.  Your
better off with a sub select (and after all, all a Domain function really is
is an encapsulated SQL statement).  Even better yet, use another query as a
base "table" in your query and do a join on it.

4. Use them like they were intended to be used; places where a VBA
expression is allowed but a SQL statement or recordset is not.   Or with
Dlookup, which is used to fetch one value; don't use it to fetch five
different fields from the same record.  Instead, open a recordset, find the
record, and have all the fields available.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn
Sent: Wednesday, March 09, 2011 03:03 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Reserved words

Here's my take on the entire suite with an emphasis on the analyzer...

There are 12 modules (separate software packages) in the suite.  If
purchased individually, the cost would be about $4,000.  The modules are
priced differently so splitting up the price will not give you a true
depiction of their worth.

Four modules utilize either an Active X control or an executable that needs
to be installed on each user's machine.  That said, and installation
"package" needs to be developed and then run by an administrator to install
them on the user's machines.  Without the Active X control or executable on
the user's machine they don't work and the controls that use them will be
unrecognizable.

Three modules can be used on ONE machine and by ONE person.  The one person
must have administrative rights to the network.  Needless-to-say we will not
be using these three modules. 

Five of the modules are specifically designed to be used on a single
developer's machine.  Three of them deal with writing code, with suggestions
on how to write code, standardize code and how to adhere to "best practices"
when writing code.  One module compares database files and lists the
differences between them, making suggestions on which option is best, and if
needed merge them.

The fifth and last module is probably the most powerful.  The Analyzer
module reviews the code, the structure of the database and makes
recommendations on how to improve functionality (if possible) of the
database and to help mitigate any possible errors.  I ran the analysis on
last Friday. I was given 244 errors, 3,042 suggestions and 830 performance
suggestions.  I know about most of them, but the complexity of the database
and how we need to show, modify and manipulate the data is more important
that squeezing out a few nanoseconds when running a query, showing a form or
report.

Most of the errors reported that a field did not exist... well I connect to
an Excel spreadsheet and import several rows of data.  When I connect to
Excel it is linked to the database and then the link is destroyed.  Of
course, it does not exist, but there is no way the analyzer can know that.

One suggestion is to "show hyperlink hand on hover" for buttons, etc. I do
that with code in the "on mouse move" event.  But what I didn't know is in
A2007 one of the properties for buttons is "Cursor on Hover" which is set to
default. The other option is "hyperlink hand."  So do I want to change the
386 instances (or about 10% of the total number suggestions) to comply with
the suggestion? 

One performance hit was my use of domain functions.  I like domain
functions.  However, I'm told that they could possibly slow my database down
and that I should create an "equivalent saved query."  From my research
there are people on both sides of this issue.  Some say it makes no
difference and in some cases it's the best choice.  Other say never use
domain functions.  I say if it works - don't fix it.

So far, I've deleted two tables that were not used; deleted 27 duplicate
indexes, modified several queries in the hope of speeding things up; and
analyzed several errors, suggestions and performance tips.  I still have a
lot of work to do to go through all their recommendations.

Do I think it's worth the money.  Yes. It costs $299 US as an individual
package.  I'm always looking for ways to slim down my code and to ensure
error messages are kept to a minimum.  If by going through this and 2 or 3
calls from users are NOT received... it's worth it.

Two other packages that I like are the Speller and the CodeTools.  The
Speller is used by the programmer.  It looks at the LABELS and CAPTIONS of
objects to ensure everything is spelled correctly.  It uses the dictionary
from the Office package on the machine.  For some reason, I misspelled Excel
at least 10 times in the labels/captions.  There were several other words
that were misspelled that I didn't catch and during the usability testing
were detected.  I like it too.

CodeTools is good and saved me several hours.  I typically don't put in
error trapping when a button only closes a form or quits the application.
Using CodeTools, it verified and put in error trapping in the appropriate
places if it was missing.

So far that's my thoughts on the tools.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Tuesday, March 08, 2011 3:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Reserved words

try "be interested to know more FROM a real user."

__________________________________
From: accessd-bounces at databaseadvisors.com
[accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
[Darryl.Collins at iag.com.au]
Sent: Wednesday, 9 March 2011 7:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Reserved words

Jim,

I would like to know more about your thoughts on this product.  It looks
rather useful, but i note it is also rather pricey.  Now that maybe ok as it
might still be great value for money given what it can do.  Or it may not
be...

be interested to know more for a real user.

cheers
darryl.

________________________________________
From: accessd-bounces at databaseadvisors.com
[accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn
[jm.hwsn at gmail.com]
Sent: Wednesday, 9 March 2011 6:28 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Access Reserved words

My customer of the Access 2007 project I'm working on wanted some
functionality that is not native to Access.

For example, they wanted full Rich Text Formatting for several fields.
These fields are used as feedback papers for the users.

They purchased the FMS Total Access Ultimate Suite to be used with this
project.

One of the components is the Total Access Analyzer.

I received the suite after I essentially finished the majority of the work.

I ran the analyzer and it told me that I have 954 objects documented.  It
has 85 tables, 451 queries, 125 forms, 74 reports, 1 macro and 25 modules.
It also informed me that there was a table that wasn't being used and a
field in two queries that didn't belong to any table.  It also told me it
found 244 errors, 3042 suggestions on improving it and 830 performance
issues.

I haven't worked through most of them yet, but many are small things such as
"transaction log" for a query is turned on and should be turned off.

One item identified is:  a column "conflicts with future SQL Server reserved
word."

I did some research and found out the reserved words it talks about have
been on the list since at least 2000 and is still on list for SQL Server
2010.

Another item was:  a column "conflicts with Jet reserved word."  In the
description it says it's a "minor issue."

I am aware of the problems of using reserved words.

BUT. I used "language" and "comp" as field names.  I should have known
better, but I also used "order" and "default."

I have had NO issues with these reserved words - yet.

My question is:  Should I spend the time to change the field names?

If I don't change them, what kind of problems will it create?



Thanks,
Jim


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list