rant: uninfomative error messages when using reserved words

[Beemer Biker]

I had a SQL select statement that worked perfectly when testing using the
access wizard. For example
SELECT EmployeeInfo.Section, EmployeeInfo.Department from EmployeeInfo WHERE
blahblahblah

this ran fine in Access 2007 query test window even though Section seems to
be a reserved word (which I didn't know at the time). It failed miserably
when

AccessDataSource1.SelectCommand = "SELECT EmployeeInfo.Section,
EmployeeInfo.Department from EmployeeInfo ";

was executed with the error message IErrorInfo.GetDescription failed with
error code (0x8...blahblahblah)

Anyway, it would have been nice to have the error handler say that "Section"
was a reserved word.



sorry for the rant

 

[Allen Browne]

Despite the pain, it sounds like you are doing quite well at
trouble-shooting. You have pinned down the specific issue, and grasped why
using reserved words for object/field names is problematic in general.

I'm not sure I expect Access to able to make sense of queries such as:
SELECT SELECT, FROM, WHERE, IN
FROM SELECT
WHERE FROM IN (WHERE);
where a table named Select has fields Select, From, Where, and In.
Realistically, I don't expect it to understand the statement enough to give
a meaningful error message either.

Access does a semi-reasonable job of adding square brackets around problem
names, but a 'semi-reasonable' job is not going to satisify you or me, nor
prevent weird and frustrating bugs.

IMHO, it would have been better to reduce the number of words that could
cause a problem. That hasn't happened -- there are way too many to
remember -- so we need a list to refer to when designing tables. Hopefully
this list will help head off future frustrations before they occur:
http://allenbrowne.com/AppIssueBadWord.html

 

[Beemer Biker]

Despite the pain, it sounds like you are doing quite well at
trouble-shooting. You have pinned down the specific issue, and grasped why
using reserved words for object/field names is problematic in general.

I'm not sure I expect Access to able to make sense of queries such as:
SELECT SELECT, FROM, WHERE, IN
FROM SELECT
WHERE FROM IN (WHERE);
where a table named Select has fields Select, From, Where, and In.
Realistically, I don't expect it to understand the statement enough to
give a meaningful error message either.

Access does a semi-reasonable job of adding square brackets around problem
names, but a 'semi-reasonable' job is not going to satisify you or me, nor
prevent weird and frustrating bugs.

IMHO, it would have been better to reduce the number of words that could
cause a problem. That hasn't happened -- there are way too many to
remember -- so we need a list to refer to when designing tables. Hopefully
this list will help head off future frustrations before they occur:
http://allenbrowne.com/AppIssueBadWord.html

I am sure you are correct Allen. However, I tend to think that the code
that handles parsing the select statement is a cryptic Regex that too many
programmers had their hands on and no one is willing to fix it for fear it
would get worse.

my 2c.