Queries on MS Access Source Tables

  • Hello,

    I am using the Native OLEDB Microsoft Jet 4.0 OLEDB Provider in a connection manager to an Access 2003 database. There are two tables in that database that I'd like to run the following query against:

    SELECT     CASE

                     WHEN COUNT(CountResult) = 1 THEN 'True'

                     WHEN COUNT(CountResult) > 1 THEN 'False'

                     END AS CountResult

    FROM         (SELECT     SettleDate AS CountResult

                           FROM          [Transaction(CBL)]

                           GROUP BY SettleDate

                           UNION

                           SELECT     SettleDate AS CountResult

                           FROM         [Transaction(Wells)]

                           GROUP BY SettleDate) AS th

    It looks like the Jet provider will not support this query. What can I do to run this kind of a SQL Statement against an Access database source without using something like a staging table in SQL Server?

    Thank you for your help!

    CSDunn

  • Are you getting an error message? no results? anything? What are your symptoms? Any hints at all?

  • Thank you for your response. I actually moved away from this solution, and I've tried to do something using variables which worked much better.

Viewing 3 posts - 1 through 2 (of 2 total)

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