SQL question

[Po]

I Have an Access Database with a autonumber for primary key.
When I want to delete a row with the primary key in the where clause. It
desn't work

There is my syntax:
DELETE FROM TABLE WHERE (No = 1);

The number 1 is for the exemple. So its seem that I can't do nothing with a
primary key because I can't do a Query with the same clause.

So if anyone can help me, please let me know
thanks for all who reply to me
Eric

[Jared 323]

That's not true. You have an error in your command. What is the command you are sending and what is the error you get?


TABLE has to be the actual table name. No has to be the actual name of the column. Otherwise your above statement will work fine.

Did it work in Access before you brought it over to labview? This will cause you a lot of headaches because Access doesn't always use SQL syntax. Like Access uses '*' for a wildcard when SQL uses '%'. A wildcard query working in Access will not work correctly through SQL. So, make sure it works in Access first, then look for things that are Access specific (you will learn as you go). And buy an SQL book. I suggest 'The Practical SQL Handbook' Thrid edition. List price about 40.00.

Jared

[Alberto]

Another tip:

Don't ever put spaces in your column (field) names.

Alberto

[Po]

I have no error exiting the sql statement execute.
I have tried to delete by another column in my table, and its work!
so I guess that its a problem with the autonumber with access or a problem
with a primary key

Eric

[Dennis_Knutson]

I just ran the command DELETE * FROM Table1 WHERE (Id =2) and it worked just fine.

[Jared 323]

Ah ha! I think i know the problem. When you get no error message back it means there was no error. i.e. the command executed. So lets look closely. What you told Access to do was delete where a condition was true. But the problem is, you told Access to delete nothing! So Access says 'OK, i deleted nothing where your id = 1' Done, no error.

Here is what i was talking about with Access being tricky. The command DELETE with nothing after it in Access means the same as DELETE * ('*' meaning all) But through SQL delete with nothing after it means just that, delete nothing from table.

Program with Access for about a month and you will learn to hate it!

Make your statement this : DELETE * FROM tablename WHERE columnname = value. This should work in both
Access and through SQL.

Jared