MS Access Query won't work in Classic ASP

Question

The following works in Access;

SELECT Transactions.Date, Transactions.Time, Transactions.Direction,
           Transactions.TransactionNumber, Transactions.TransactionType,
           Transactions.Exception, Exceptions.ExceptionType
FROM Transactions LEFT JOIN Exceptions 
         ON  (Transactions.TransactionNumber = Exceptions.TransactionNumber)
         AND (Transactions.Date = Exceptions.Date) 
         AND (Transactions.TokenNumber = Exceptions.TokenNumber)
WHERE (((Transactions.Date)>=20120803) 
         AND ((Transactions.Direction)=-1) 
         AND ((Exceptions.ExceptionType) Not In (43,44,45,46) 
             OR (Exceptions.ExceptionType) Is Null) 
         AND ((Transactions.TokenNumber)=6605253))
ORDER BY Transactions.Date, Transactions.TransactionNumber;

But when done in ASP as below, I get error '80004005'

SELECT Transactions.Date, Transactions.Time, Transactions.Direction,
           Transactions.TransactionNumber, Transactions.TransactionType,
           Transactions.Exception, Exceptions.ExceptionType 
FROM Transactions LEFT JOIN Exceptions 
        ON Transactions.TransactionNumber = Exceptions.TransactionNumber 
    AND Transactions.Date = Exceptions.Date 
        AND Transactions.TokenNumber = Exceptions.TokenNumber 
WHERE Transactions.Direction = -1 
        AND (Exceptions.ExceptionType Not In (43,44,45,46) 
            OR Exceptions.ExceptionType Is Null)
        AND Transactions.TokenNumber= 6605253
        AND Transactions.Date >= 20120803 
ORDER BY Transactions.Date, Transactions.TransactionNumber

I removed some of the brackets by the way, thinking it would help, but it did not.

Are you sure it is the query? support.microsoft.com/kb/306345 — Fionnuala
Ah, I think it's something to do with Exception being a reserved word in Access. Remember I had this a few days ago and forgot about it. — Ben Hamilton
Shouldn't date values be delimited with '#' in Jet queries, e.g. #20120803#? — Tony
I do not think that Exception being a reserved word is the problem in this case. A reserved word is only a problem when it is not prefixed with the table name. For that matter, date and time are also reserved words. — Fionnuala

mikeY mikeY · Accepted Answer · 2012-08-10T15:57:44

I agree with Remou's comment. 80004005 doesn't point at a bad query, I think it points at a data source name being wrong or a corrupt Access file.

I would check your connection string and verify that you can run a simple query before chasing this query.

MS Access Query won't work in Classic ASP

3 Answers