Stupid thing in Access 2007

  • Thread starter Thomas Zapf-Schramm
  • Start date
T

Thomas Zapf-Schramm

If you create a new table in Access 2007 (with the Designer) and you try to
name one of your Fields "Name" then you get a warning message that tells you
"Name" is a reserved word in Access. You are warned that this can cause
errors.

If you use this table in the report designer, you can drop the field on the
report. Everything works. But if you reference the field in a formula in an
unbound textbox on the report, the report doesn't display the fields content
but the name of the report.

Because you are warned, when you create a field, this is a feature, not a
bug. But it is a extremly stupid feature.

How can you make "NAME" a reserved word in a DBMS? I suppose about 80% of
all databases I've seen contain fields named "Name".

TZS
 
S

Stefan Hoffmann

hi Thomas,
How can you make "NAME" a reserved word in a DBMS? I suppose about 80%
of all databases I've seen contain fields named "Name".
Click to expand...
As you have already mentioned, you get into troubles when using it with
e.g. expressions. On this level you already left the RDBMS and entered
the development frontend for the RDBMS called Jet.

Beside that, [Name] is a poor field/attribute name. This is either
Surname, FullName, LastName for persons or Caption or Label for other
entites.


mfG
--> stefan <--
 
B

BruceM

I don't know if Date is one of the fields that generates a warning, but it
is another one that often ends up being used as a field name. The trouble
is that it is also a function. Name is a property. I suppose Microsoft
could have come up with obscure names, or used prefixes or something (pName,
fDate, etc.), back when Access was first developed, but it is what it is.
Nothing is new except the warning.
In most cases you can get around the problem by enclosing field names in
square brackets (I don't know about Access 2007, but I expect this works
there), but as Stefan pointed out (in different words), "Name" is vague in
any case.
 
G

George Hepworth

Well, to each his own. I much prefer the warning that what you are about to
do (create a generic "Name" field) is nota good idea. Once warned, if you
persist in pursuing what is clearly not a very good idea, that's on you.

In addition to creating the strong possibility of internal errors, such as
the one you describe, a name like "name" is so generic as to be virtually
meaningless in a great number of cases.

I'll just sum up by pointing out that poorly designed databases (about 80%
of the ones you have seen, by your estimate) should not be used as
templates.

George
 
S

Sascha Trowitzsch

To extend that:
Access in not simply a DBMS. JET/ACE uses the vba expression service which
means it hookes into the vba project an interacts with COM. So you have to
pay attention for interferences with the vba projects typelib namespace.
THAT's a feature you would only achieve in other DBMS by writing user
defined functions or SPs on the server. Did you like thats?

Ciao, Sascha

Stefan Hoffmann said:
hi Thomas,
How can you make "NAME" a reserved word in a DBMS? I suppose about 80% of
all databases I've seen contain fields named "Name".
Click to expand...
As you have already mentioned, you get into troubles when using it with
e.g. expressions. On this level you already left the RDBMS and entered the
development frontend for the RDBMS called Jet.

Beside that, [Name] is a poor field/attribute name. This is either
Surname, FullName, LastName for persons or Caption or Label for other
entites.


mfG
--> stefan <--
Click to expand...
 
T

Tony Toews [MVP]

Thomas Zapf-Schramm said:
If you create a new table in Access 2007 (with the Designer) and you try to
name one of your Fields "Name" then you get a warning message that tells you
"Name" is a reserved word in Access. You are warned that this can cause
errors.
Click to expand...

Excellent. As others have pointed out this is a good thing but the
list is too small.

A friend came across a problem with a field name he had used call
Union. SQL queries don't like that field name unless, presumably,
it's in square brackets.
Because you are warned, when you create a field, this is a feature, not a
bug. But it is a extremly stupid feature.
Click to expand...

It is an excellent feature.
How can you make "NAME" a reserved word in a DBMS? I suppose about 80% of
all databases I've seen contain fields named "Name".
Click to expand...

Folks shouldn't though. Same with other fields such as Date.

BTW using the following will eliminate that problem anyhow.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Top