How do I delete files from a table in MSDE 2000 that is selected by 3 joins?

Question

I have a VB6 program that uses a n Access backend. The query that I am currently using is

sQuery = "DELETE tblResultNotes.* " & _
             "FROM (tblJobs INNER JOIN tblResults ON tblJobs.JobID=tblResults.JobID) INNER JOIN tblResultNotes ON tblResults.ResultID=tblResultNotes.ResultID " & _
             "WHERE (tblJobs.CreateDate)< #" & strDate & "# " & _
             "AND tblResults.StartTime < #" & strDate & "#;"

I have changed my backend to MSDE 2000 and now this query is giving me a syntax error near '*'. Could someone help me out?

Thanks, Tom

possible duplicate of [How to delete in MS Access when using JOIN's?](http://stackoverflow.com/questions/5585732/how-to-delete-in-ms-access-when-using-joins) — ypercubeᵀᴹ, Dec 14 '11 at 20:59
Not a dupe. OP has changed from Access to MSDE so now it's not a delete from Access issue anymore. — squillman, Dec 14 '11 at 21:21

squillman · Accepted Answer · 2011-12-14T21:34:27.647

1

Try changing your SQL to this:

sQuery = "DELETE FROM tblREsultNotes " & _
"FROM " & _
"    tblJobs" & _
"    INNER JOIN tblResults ON tblJobs.JobID=tblResults.JobID" & _
"    INNER JOIN tblResultNotes ON tblResults.ResultID=tblResultNotes.ResultID" & _
"WHERE tblJobs.CreateDate < '" & strDate & "'" & _
"AND tblResults.StartTime < '" & strDate & "'"

Note the date delimiter change to ' instead of #.

edited Dec 14 '11 at 21:34

answered Dec 14 '11 at 21:21

squillman

13,363
3
41
60

Fantastic!! Thank you very much for your swift response squillman! – MagnumPI Dec 15 '11 at 14:02

How do I delete files from a table in MSDE 2000 that is selected by 3 joins?

1 Answers1