VBA连接Oracle数据库的两种连接字符串的区别：MSDAORA-对象关闭时不允许操作

VBA连接数据库有两种连接字符串：

  dataSource = Chr(34) & "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.101)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=mesdb)))" & Chr(34)
  '//连接字符串1
  cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Password=pc;User ID=pcuser;Data Source=" & dataSource & ";Persist Security Info=true"
  '//连接字符串2
  cnn.ConnectionString = "Provider=MSDAORA.1; user id=pcuser; password=pc; data source = " & dataSource & "; Persist Security Info=True"

使用ADO查询数据库时，set rs=cnn.execute(sql)

我写了一个比较复杂的查询，用到了CTE，结果在PL/SQL中可以正常执行，使用连接字符串1也没问题。但切换到连接字符串2时就会出错，提示“对象关闭时不允许操作”，可是在执行没有CTE的Sql时是完全没有问题的。数据库oracle 11g R2

sql写的有点长：

with tempNo 
as 
(select B.No from 
   zg_aoi_dlist B，
   dm_wafer C
 where 1=1 
   and replace(upper(C.filename),'.CSV','')=B.waferid
   and C.product_Name like '%0620%'
   and C.load_seq=0
   and substr(C.specification,1,1)='C'
   and c.gross_die>1000
   and B.EndTime>='2017/01/06 08:00:00' and B.EndTime<'2017/01/08 08:00:00' )
select F.X,F.Y,E.CNT,F.SIO2PEEL,F.EMITTERCONTAM,F.FINGERSCRATCH,F.NFINGERPEEL,F.COLORDIFF,F.SIO2PEEL1,F.METALRESIDUE,F.DOUBLECHIP,
  F.PFINGERPEEL,F.ESDPOINT,F.COLORDIFF1,F.DOUBLECHIP1,F.NPADCONTAM,F.CHIPPING,F.COLORDIFF2,F.DOUBLECHIP2,F.PPADCONTAM,F.ITOPEEL,F.DOUBLECHIP3,F.PADPEEL,F.EPIDEFECT
from 
   ( select chipx,chipy,count(no) as CNT from zg_aoi_dlist_die D,
    (
      select max(A.rowid) as maxrow from zg_aoi_dlist_die A,
        tempNo B
        where 1=1
        and A.no=B.No
        group by A.no,chipx,chipy) C
    where D.rowid=C.maxrow
    group by D.chipx,D.chipy ) E,
  (
    select * from
    (   select to_number(ChipX) as X,to_number(ChipY) as Y, Defect_category,Count(*) as CNT 
        from zg_aoi_dlist_die A,
           TempNo B       
        where 1=1 
           and A.No=B.No
        group by to_number(ChipX),to_number(ChipY),Defect_category
           )
    pivot (sum(CNT) for( Defect_category) in  ('SiO2 Peeling (fx)' as SiO2Peel,'Emitter Contamination' as EmitterContam,'Finger Scratch' as FingerScratch,
    'N-Finger Peeling' as NFingerPeel,'Color Difference' as ColorDiff,'SiO2 Peeling(fx)' as SiO2Peel1,'Metal Residue' as MetalResidue,
    'Double Chip' as DoubleChip,'P-Finger Peeling' as PFingerPeel,'ESD Point' as ESDPoint,'NA' as NA,'Color different' as ColorDiff1,
    'Duoble chip' as DoubleChip1,'N-Pad Contamination' as NPadContam,'Chip Chipping' as Chipping,'Color differrent' as ColorDiff2,'Duoble Chip' as DoubleChip2,
    'P-Pad Contamination' as PPadContam,'ITO Peeling (fx)' as ITOPeel,'Double chip' as DoubleChip3,'Pad Peeling' as PadPeel,'Epitaxy Defect' as EpiDefect)) 
  ) F
where to_number(E.ChipX)=F.X and to_number(E.ChipY)=F.Y

虽然不知道为什么，感觉微软的 MSDAORA支持的查询语句可能有些限制。以前出现过系统配置不当导致MSDAORA无法连接数据库的问题，不过又见到一次OraOLEDB.Oracle无法通过VBA连接的问题，而PLSQL可以正常连。电脑不是我自己的，所以也没法去研究了