Delete Query Not Working after Upgrading To SQL Server

[iheartvba]

Hi,
I have upgraded to SQL Server Express 2005 and now my Delete Querey which was made in MS Access Query Builder is not working. It doesn't give any error it just doesn't work:

If its of any use the SQL in the query is :

DELETE tblCollections.MDCollectionID, tblCollections.Delete
FROM tblCollections
WHERE (((tblCollections.Delete)=-1));

Any Help Would be Greatly Appreciated, I am truly desperate to get this resolved, Thanks.

[ajalwaysus]

Hi,
I have upgraded to SQL Server Express 2005 and now my Delete Querey which was made in MS Access Query Builder is not working. It doesn't give any error it just doesn't work:

If its of any use the SQL in the query is :

DELETE tblCollections.MDCollectionID, tblCollections.Delete
FROM tblCollections
WHERE (((tblCollections.Delete)=-1));

Any Help Would be Greatly Appreciated, I am truly desperate to get this resolved, Thanks.

I don't understand how this query worked in Access, it should be

DELETE
FROM tblCollections
WHERE (((tblCollections.Delete)=-1));

or in some cases

DELETE *
FROM tblCollections
WHERE (((tblCollections.Delete)=-1));

because you are deleting an entire row, you need not write the column name(s).

-AJ

[iheartvba]

Well, This had been created from Access Query builder, and you're right, I thought the same thing, but suprisingly it did work. I will try your other suggetions, hopefully they might work.

Thx

[NeoPa]

If [Delete] is a Boolean I would try :

DELETE
FROM tblCollections
WHERE [Delete]

I'm not sure SQL would like the parentheses around the field name or working a Boolean as if it were a numeric (even though that's certainly how it's stored in Access).

PS. Access built SQL is never great, and it's far from guaranteed portable. That's not even mentionning it looks like it was created after a particularly heavy evening at the pub.

[missinglinq]

It should also be noted that having a field named Delete is very poor practice, as it is a Reserved Word in Access.

Linq ;0)>

[iheartvba]

its working

1. I have changed [Delete] to [Del]
2. I have changed the first line to

DELETE tblCollections.*

3. but the thing that actually made it work was changing the last line to

WHERE (((tblCollections.Del)<>0));

strange :S

Thanks Guys

[ajalwaysus]

1. I have changed [Delete] to [Del]
2. I have changed the first line to

DELETE tblCollections.*

3. but the thing that actually made it work was changing the last line to

WHERE (((tblCollections.Del)<>0));

strange :S

Thanks Guys

Well hold on there, if -1 didn't work, but <>0 did, what data type is your field? Because I don't see why -1 wouldn't work.

-AJ

[NeoPa]

1. I have changed [Delete] to [Del]

Good practice as Linq says, but not actually part of any problem as the forms TableName.field Name and [FieldName] are both explicitly unambiguous so avoid this problem. Nevertheless still good practice and always advisable to avoid any such names already in use elsewhere.

2. I have changed the first line to

DELETE tblCollections.*

This will work, but is technically superfluous and (although Access uses it heavily) quite illogical. It indicates that fields are being deleted when actually it is the record being deleted. This is only properly (logically) indicated by the FROM clause.

3. but the thing that actually made it work was changing the last line to

WHERE (((tblCollections.Del)<>0));

This is essentially what is meant by :

WHERE [Del]

As suggested earlier.

Booleans are strange creatures and, because most people know that the value TRUE resolves to -1, and that Booleans are stored numerically, they assume that testing for True should be like comparing a Boolean with -1. This is wrong. You see it everywhere but it is wrong. Any expression is treated as true if it is not 0. The only correct way to check this numerically is (X<>0). There is however, a perfectly good way to express this as a Boolean result, and you will see this everywhere in code (only hiding as every expression is ultimately treated this way) as (X). Parentheses unnecessary for resolution of result, but included for clarity.

[NeoPa]

Well hold on there, if -1 didn't work, but <>0 did, what data type is your field? Because I don't see why -1 wouldn't work.

See earlier response, but remember also that Boolean true values are never changed to True (-1) when they are determined to be true. They are simply resolved as they are and treated as true unless they are actually False (0).

This means there are almost a full set of all the possible numbers (storable in a Boolean result) which are true (not False) and yet not True (-1). It's also why code which tests (X=True) is both logically wrong and quite superfluous.