error 3128 in query

[Lavu]

I use an ACCESS 2003 front end to SQL server 2000.

I am passsing a simple SQL statement

SQLText1 = "Delete A from dbo_tblBook_inventory A ,dbo_tblrhd_inventory b "
& _
" where A.drive_num = b.drive_num And b.date_verified Is
Not Null"

DoCmd.RunSQL SQLText1

This gives me the error 3128 - Specify the table containing the records that
you want to delete.

But the same statement works when I try it in SQL server's Query Analyzer.

I also tried changing the query to

Delete A from dbo_tblBook_inventory A inner join dbo_tblrhd_inventory B on
A.drive_num = B.drive_num where B.date_verified Is Not Null

but the same error 3128 occurs from ACCESS and works fine directly on SQL
server.

Any help will be appreciated.

Ps: I posted this in the DAO,ADO group also . Apologies for those who read
both and find duplicate posting.

 

[John Spencer (MVP)]

Try specifying a field or the entire set of fields in the SQL string.

DELETE A.*
FROM ...

DELETE A.[SomeField] FROM ...

You may also have to use the DISTINCTROW predicate as in

DELETE DISTINCTROW A.[SomeField] FROM ...

If you want to use T-SQL statement syntax, you might have to use pass-through queries.