A bug in MS Access

[rajeev2005]

Hi,
I noticed a bug in MS Access long back. But I couldn't report that to
Microsoft as I was not knowing whom to report. So, I am using this message
board thread for that.

The details of the bug :

I created a table ADDRESSDETAILS with following Columns
ADDRESSID Number(3),NAME TEXT(50),EMAILADDRESS TEXT(100)

I inserted many rows and I tried the delete query "DELETE * FROM
ADDRESSDETAILS WHERE ID=10".
All the rows from the table were deleted. If you try to delete * using a
condition with a non existing column all raws are getting deleted. I seen
this in many versions of MS Access.
I haven't tried this with MS Access latest version. Please do the needful if
this is a valid bug.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...4eb7&dg=microsoft.public.access.modulesdaovba

 

[Allen Browne]

rajeev2005, I am unable to reproduce this bug.

I tried using exactly what you described, and received an error message that
the delete query was invalid because it required a parameter. That makes
sense, becuase the WHERE clause includes the ID field, but there is no field
with that name in the query.

Your example also was unclear about how to create a field of type Number and
size 3.

I do not believe you have a valid bug here. You may have had a corruption or
other issue. I include this kind of DELETE query statement in almost every
database I write (as do many other developers) and have never seen this
issue.

(BTW, the * is actually spurious in the DELETE query statement, even though
Access uses it.)

 

[TC]

Sorry, I'd have to see that with my own eyes, to credit it. How about
you post an example database on a public binary newsgroup? Include a
public sub, which, when run, will demonstrate the problem you describe.

HTH,
TC

 

[rajeev2005]

I found this bug accidentally when I was developing an application 6 months
before and I couldn't find a forum to report this bug. I got this link from
Gotodotnet Message board to report this bug.

I tried the following steps just before to reconfirm this. And the result
was same.
I created a table in design mode "Details"

Columns DataType

ID AutoNumber
Name Text
Address Text


Inserted many rows.

and executed the following query

"delete * from Details where empid=10". Here empid is a non existing column.

All rows got deleted. Why to report a bug without purpose?

The MS Access version: Microsoft Access2002(10.2616.3501)SP-1

 

[rajeev2005]

Hi TC,
I found this bug accidentally when I was developing an application with
Microsoft Access 2002(10.2616.3501)SP-1. Because of a wrong column name in
the query all the rows got deleted. I didn't find any place to report this
bug. As I posted about this in gotdotnet messageboard, I got a link to this
forum. I am extremely sorry, if it is wrong to post in a public forum about
the bug which I experienced. I am a Microsoft developer and I believe in
transparency in everything. I just before confirmed the bug with above
mentioned MS Access version. In my post I mentioned that I didn't tried this
in the MS Access latest version. So,I don't think anything wrong in my post.
I just wanted to confirm whether bug is existing there in the latest access
version or not. I always trust Microsoft products.
Thanks and regards,
Rajeev

 

[rajeev2005]

Hi Allen,
I got this bug when I was developing an application with ADO.Net and MS
Access. What actually happened was because of the wrong column in the delete
query all the rows got deleted. I just tried the same thing with MS access
queries with an invalid column name, it prompted to enter value for the
invalid column name and if you enter some value, a message will show that
'n' rows in the table will be deleted and it will delete all rows. I think
there is nothing wrong in my post. This bug is relevant and the MS Access
version which I found was 'Microsoft Access 2002(10.2616.3501)SP-1'. I just
published this in this forum to know whether the bug exists in the latest
version of Access.

 

[Allen Browne]

Rajeev2005, you are very welcome to post your experiences with Access here.
But I still cannot demonstrate the problem you describe.

The only cases where I can imagine this to be the case would be:

a) You executed the query, and it popped up a dialog asking for the value
for empid. You typed in 10 as the value. In this case, Access will
understand emp10 to be equal to 10 for all records, and will therefore
delete all records.

b) You previously had a field that was named or captioned empid in this
table. You have not disabled Name AutoCorrect, and so Access still has that
name associated with something else in this table.

c) The database is corrupt (such as a faulty index), leading to the loss of
records.

 

[rajeev2005]

Hi Allen,
Thank you for clarifying internal working of Access. I faced this problem
when I was using ADO.Net and MS Access. In the delete query condtion, my
friend used a non existing column mistakenly("empid" instead of "emp_id").
It didn't throw any exception for the invalid column name and deleted all
rows. When I tried in this MS Access directly it was prompting for the value
for invalid column and the if I am inputing value all columns got deleted. I
thought this is a bug and searched for the stage to report this.Atlast, I
posted this in gotdotnet messageboard and I got this link from one of the
reply. I thought this will be useful for others if they face this problem and
posted this. Thanks a lot for clarifying.

Thanks and regards,
Rajeev

 

[rajeev2005]

Hi,
Adding on to my previous comments. I still feel it as a bug. If a query
with a non existing column is executed why should it prompt for entering
value for a column which is not valid in the table? It should throw an
exception that column is not existing or a message that the column is
invalid, right. In ADO.net also it should throw an Exception for invalid
columns. Suggest..

Thanks and regards,
Rajeev

 

[Allen Browne]

So you are suggesting the bug you experienced has to do with ADO.NET, and
cannot be reproduced with Microsoft Access?

 

[rajeev2005]

I haven't tried this in MS Access latest version. It can be resolved in that
one. In Access, if it is prompting to enter a value for a non existing
column, for me it is a bug in Access too. Can you give me the latest version
number of MS Access? If it is available with me, I will try with MS Access
latest version and ADO.Net. I will also update the details in this thread.

Thanks and regards,
Rajeev

 

[TC]

It is not a bug - it is expected behaviour in the circumstances that
you now describe - and these are not what you described originally.

IIRC, if Jet (not Access) detects an undefiune symbol in an SQL
statement, it will treat it as an undefined parameter, and prompt for
the parameter value. For example, if you say:

DELETE FROM MyTable WHERE ID>0

and ID is not a valid field, it will prompot for the value of the
undefined ID paraneter. If you type 5, for example, Jet will then
execute the following SQL:

DELETE FROM MyTable WHERE 5>0

which will of course delete all the rows from your table.

It is not a bug. You misunderstand what is actually happening.

HTH,
TC

 

[TC]

It is not a bug - it is expected behaviour in the circumstances that
you now describe - and these are not what you described originally.

IIRC, if Jet (not Access) detects an undefined symbol in an SQL
statement, it will treat it as an undefined parameter, and prompt for
that parameter's value. Then the value that you enter will be
substituted into the statement, in place of the parameter name. What
happens then, will depend on how the statement is written.

For example, if you wrote:

DELETE FROM MyTable WHERE ID>0

and ID was not a valid field, Jet will prompt for the value of the
undefined 'ID' parameter. If you then typed 5, for example, Jet would
execute the following SQL:

DELETE FROM MyTable WHERE 5>0

which would delete all the rows from your table.

It is not a bug. You misunderstand what is actually happening.

HTH,
TC

 

[rajeev2005]

Hi TC,
Thanks for the information provided and it cleared my doubts. In my
original post I missed the prompting for undefined value from mentioning. I
am accepting that this is not a bug as I was not aware of the internal
working of Jet and Access. I misinterpreted this as a bug as I lost data from
my table because of a delete statement with an undefined column name. I was
expecting an Exception from ADO.Net, that made to query directly from Access
and my misunderstanding of the behavior.

I came to the conclusion from this discussion that while using delete
statement in ADO.Net with MS Access, developer should take care of using
valid column name for delete statement. If he uses invalid column name in
delete statement's condition, all data will be deleted from the table
without any Exception.

Thanks a lot for the information and I should take care before posting these
types of behaviors as bugs in public.


Thanks and regards,
Rajeev

 

[TC]

No problems, Rajeev. I'm glad that I could help you with this.

Cheers,
TC

 

[rajeev2005]

Hi TC,
As per the information I got from this discussion forum. I updated the
gotdotnet MessageBord too.

You can find the link for the thread below.

http://www.gotdotnet.com/community/messageboard/home.aspx
"Off Topic Discussions > A bug in MS Access. whom to report?"


Thanks and regards,
Rajeev

 

[Tim Ferguson]

@g49g2000cwa.googlegroups.com:

Hiya TC

IIRC, if Jet (not Access) detects an undefiune symbol in an SQL
statement, it will treat it as an undefined parameter, and prompt for
the parameter value. For example, if you say:

It's actually the the other way round. Running the DELETE command from the
Access GUI gets you the typically confusing inputbox. Running the DELETE
command from VBA using the db.Execute method returns the trappable error
"Too few parameters, expected 1". DoCmd.RunSQL produces the same input box
as the GUI.

I agree it's a hard to spot error and not one I'd have expected without
thinking about it. Notch up another reason to use .Execute rather than
RunSQL!

B Wishes for the New Year



Tim F

 

[TC]

Yes, I'm sorry, you're quite right. It's clearly Access (not Jet) that
prompts for the parameter. OTOH, I imagine it would be Jet (not Access)
that actually detects the parameter initially (after parsing the sql).
So there's probably some too'ing & fro'ing behind the scenes, befor the
dialog gets displayed

Cheers,
TC

 

[TC]

Thanks rajeev. Well done.

TC

 

[rajeev2005]

Hi TC,
Actually you made confusion in me. Now it seems as a bug in MS Access. And
also a bug in ADO.Net because of the bug in MS Access. I used OleDbCommand,
OleDbCommandParameter, OleDbConnection Objects and executed the delete query
with a wrong column Name. No exception thrown and all rows got deleted. The
case here can be, it taken the value I given for the non existing column name
and deleted all the rows from the table. So, it seems a valid bug. The
problem is in MS Access right, not in Jet.

Thanks and regards,
Rajeev