Why doesn't this work

[Guest]

Help
the following qry works in a MDB file but I can not do this in an ADP file

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] = dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

Is there any reason why I should us an ADP file over an MDB file? What are
the Pros and Cons to mdb/adp

thank you

 

[Tom Ellison]

Dear B Horwitz:

How about:

DELETE dbo_tbl_HoldOrderDetails_View
FROM dbo_tbl_HoldOrderDetails_View V
WHERE (SELECT H.CustomerCode
FROM dbo_tbl_HoldOrderHeader H
WHERE H.[ORDER#] = V.[ORDER#]) = '1'

For SQL Server, observe:

- use single qutoes for literal strings

- Use a different DELETE syntax (see Online Help)

The same query with an INNER JOIN syntax would be:

DELETE
dbo_tbl_HoldOrderHeader
FROM dbo_tbl_HoldOrderDetails_View
INNER JOIN dbo_tbl_HoldOrderHeader
ON dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE dbo_tbl_HoldOrderHeader.CustomerCode = '1'

Tom Ellison

 

[Guest]

I will give it a try,
Thank you for answering so quickly

Dear B Horwitz:

How about:

DELETE dbo_tbl_HoldOrderDetails_View
FROM dbo_tbl_HoldOrderDetails_View V
WHERE (SELECT H.CustomerCode
FROM dbo_tbl_HoldOrderHeader H
WHERE H.[ORDER#] = V.[ORDER#]) = '1'

For SQL Server, observe:

- use single qutoes for literal strings

- Use a different DELETE syntax (see Online Help)

The same query with an INNER JOIN syntax would be:

DELETE
dbo_tbl_HoldOrderHeader
FROM dbo_tbl_HoldOrderDetails_View
INNER JOIN dbo_tbl_HoldOrderHeader
ON dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE dbo_tbl_HoldOrderHeader.CustomerCode = '1'

Tom Ellison

Help
the following qry works in a MDB file but I can not do this in an ADP file

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] = dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

Is there any reason why I should us an ADP file over an MDB file? What are
the Pros and Cons to mdb/adp

thank you

 

[Tom Ellison]

Dear B Horwitz:

Well, I answered when I saw your post and had time. Just luck, I guess! I
didn't actually do that on purpose, so I don't get any credit for that.

Tom Ellison

I will give it a try,
Thank you for answering so quickly

Dear B Horwitz:

How about:

DELETE dbo_tbl_HoldOrderDetails_View
FROM dbo_tbl_HoldOrderDetails_View V
WHERE (SELECT H.CustomerCode
FROM dbo_tbl_HoldOrderHeader H
WHERE H.[ORDER#] = V.[ORDER#]) = '1'

For SQL Server, observe:

- use single qutoes for literal strings

- Use a different DELETE syntax (see Online Help)

The same query with an INNER JOIN syntax would be:

DELETE
dbo_tbl_HoldOrderHeader
FROM dbo_tbl_HoldOrderDetails_View
INNER JOIN dbo_tbl_HoldOrderHeader
ON dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE dbo_tbl_HoldOrderHeader.CustomerCode = '1'

Tom Ellison

Help
the following qry works in a MDB file but I can not do this in an ADP
file

DELETE dbo_tbl_HoldOrderDetails_View.[ORDER#],
dbo_tbl_HoldOrderHeader.CustomerCode, dbo_tbl_HoldOrderDetails_View.*
FROM dbo_tbl_HoldOrderDetails_View INNER JOIN dbo_tbl_HoldOrderHeader
ON
dbo_tbl_HoldOrderDetails_View.[ORDER#] =
dbo_tbl_HoldOrderHeader.[ORDER#]
WHERE (((dbo_tbl_HoldOrderHeader.CustomerCode)="1"));

Is there any reason why I should us an ADP file over an MDB file? What
are
the Pros and Cons to mdb/adp

thank you