Delete SQL qry Syntax

[topcat99]

I get a '3219' (Monsters Inc?) runtime error on opening the following SQL VB6 string. It connects to Access 2003 MDB.

strSQL = "DELETE tblA01.* " _
& "From tblA01 " _
& "WHERE ((tblA01.Bin)=(SELECT tblSSE.BIN " _
& "FROM tblSSE));"

Set rstFDtable = ATLAS.OpenRecordset(strSQL, dbOpenDynaset, dbConsistent, dbOptimistic)


Debug.print result:-

DELETE tblA01.* From tblA01 WHERE ((tblA01.Bin)=(SELECT tblSSE.BIN FROM tblSSE));

Error occurs on execution of Set command. I'm sure it is a syntax prob. but can I find it ..... aghh!

Any help appreciated. Thanks TC

[Shuja Ali]

Only Select statements can be used to open a recordset. Action queries do not require a recordset. You can use connection objects execute method to execute the delete statement.

Also your query will not work. Instead of using = in the where condition use In.

[topcat99]

Thankyou. That's a fundamental aspect I never knew.

[FunkyDexter]

Also you don't need to specify tblA01.* in the first line and some DBs will refuse the statement if you do. The reason is that a DELETE works across entire rows so you can't specify a field list. Instead just use DELETE FROM.

[salvelinus]

You also need spaces around your = sign (or IN, if you changed that). Some dbs may accept it, some may not.