error 3128 for the 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.

 

[Douglas J. Steele]

Try

SQLText1 = "DELETE FROM " & _
"dbo_tblBook_inventory A " & _
"WHERE A.drivenum IN (" & _
"SELECT drivenum FROM " & _
"dbo_tblrhd_inventory " & _
"WHERE date_verified Is Not Null)"

Alternatively, try

SQLText1 = "DELETE dbo_tblBook_inventory " & _
"dbo_tblBook_inventory A INNER JOIN " & _
"dbo_tblrhd_inventory B " & _
"ON A.drive_num = B.drive_num " & _
"WHERE b.date_verified Is Not Null"

 

[Lavu]

Thanks. The first one worked, but the second option did not. Just curious why
ACCESS would not handle some perfectly valid SQL statements.

Thriveni

 

[Douglas J. Steele]

There are many dialects of SQL. Access doesn't accept all of them.

Out of curiosity, what error did you get with the second option I suggested?

 

[Lavu]

The same error as before - error 3128.

There are many dialects of SQL. Access doesn't accept all of them.

Out of curiosity, what error did you get with the second option I suggested?

 

[Douglas J. Steele]

Actually, now that I think about it, I'm not sure that the 2nd statement is
valid SQL in any dialect.

You could try:

SQLText1 = "DELETE 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"