CODEWITHSUNDEEP
sqlms-accessasp-classic
Ben Hamilton
Ben Hamilton

Reputation: 975

MS Access Query won't work in Classic ASP

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.

Upvotes: 2

Views: 506

Answers (3)

Ben Hamilton
Ben Hamilton

Reputation: 975

This was resolved by putting the word Exception in square brackets.

Upvotes: 2

tgolisch
tgolisch

Reputation: 6744

MikeY is right. Make sure all of your IIS proceses have read/write (NTFS) permissions to the folder containing the database. Typically, that would be the IUSR_[machinename] account. Depending on which version of IIS you are using, it would be different. In IIS, have a look at which account the "Anonymous" user is mapped to.

For a quick-check, just set the folder to read/write for "Everyone". It has to be at the folder level, not just the file, because MSAccess needs to create/destroy a .LDB file for maintaining locks, etc.

Upvotes: 1

mikeY
mikeY

Reputation: 519

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.

Upvotes: 2

Related Questions