Error with SQL statement

Question

Error with SQL statement

thriveni

Ten Centuries

Points: 1104
More actions
August 5, 2005 at 12:12 pm

#66790

I use an ACCESS 2003 front end to SQL server.
I am passsing a simple SQL statement
SQLText1 = "Delete dbo_tblBook_inventory 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 ontaining the records that you want to delete.
But the same statement works when I try it in Query Analyzer.
Any help will be appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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

thriveni Ten Centuries Points: 1104 More actions · Answer 2

Tried that. Gives me the same error message as before.

Any other ideas ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

you run the statement directly on the server?

replace dbo_ with dbo.

thriveni Ten Centuries Points: 1104 More actions · Answer 4

The errors occurs when I run from ACCESS where these are linked tables and have a dbo_ prefixed. These statements run without any error when run directly on the server. But the use has an ACCESS frontend to run them.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Can you run the statement while the profiler is running? I'd like to know if the error is sql or access generated.

thriveni Ten Centuries Points: 1104 More actions · Answer 6

I have never used the profiler but there is no error generated in SQL server when I run it through query analyzer. Only when I run the statement throught aCCESS.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

have you tried to rebuild the query with the wizard?? maybe the delete join in access is different than the one on sql server.

Cory Perkins SSChasing Mays Points: 638 More actions · Answer 8

Try changing the first part of your SQL statement to DELETE a.*

I think ACCESS needs the .* along with the table name.

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 9

AFAIK, RunSQL (in MDB ODBC connections) uses Access SQL to run an action query, not T-SQL. Therefore you have to modify the T-SQL for Access (JET) syntax:

The syntax is:

DELETE [table.*]

FROM table

WHERE criteria

i.e., You can write: DELETE FROM table WHERE criteria

Your T-SQL will run in QA, but JET will not understand it. Of course, you can also do a pass through query to use T-SQL.

thriveni Ten Centuries Points: 1104 More actions · Answer 10

Thanks for all your input. What finally worked was the sub-query method:

Delete dbo_tblBook_Inventory.* FROM dbo_tblBook_Inventory

WHERE dbo_tblBook_Inventory.Drive_Num in

(select Drive_Num from dbo_tblRHD_Inventory where Date_Verified Is Not Null)

Thriveni