Como corrigir 'ODCB chamada falhou durante OpenRecordSet'

Atualmente, estou trabalhando em algumas mudanças para um existente do Access(vba) de aplicação. Mas, de repente, eu estou correndo em um ODCB erro de falha de chamada durante a execução de um QueryDef. As tabelas são ligadas a um banco de dados SQL server.

Eu tentei repor os links para as tabelas através do gerenciador de tabelas vinculadas. Também depurado para ver se a consulta foi, de fato definido e que o SQL dentro está correto.

    Call makePassThroughQuery("qryName", qSQL, True)
    Dim SQL As String
    SQL = "select * from qryName"
    Set rsNew = DB.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    ' continue to use rsNew 

O makePassThroughQuery função se parece com isso:

Function makePassThroughQuery(qryName As String, SQLString As String, 
             Optional isSQL As Boolean = True, 
             Optional ConnectString As String = "string", 
             Optional timeout As Integer = 120)

   Dim isTestingEnvironment As Boolean
   isTestingEnvironment = True
   If isSQL = False Then
     With CurrentDb
         On Error Resume Next
         .QueryDefs.Delete (qryName)
         On Error GoTo 0
         Dim qdfNew As Object
         Set qdfNew = .CreateQueryDef(qryName, SQLString)
         .Close
     End With

   Else
     Dim curDB As Database, curQdef As QueryDef
     '    Dim ConnectString As String
     'ConnectString = "string"
     Set curDB = CurrentDb
     On Error Resume Next
     CurrentDb.QueryDefs.Delete (qryName)
     On Error GoTo 0
     Set curQdef = curDB.CreateQueryDef(qryName)
     curQdef.Connect = ConnectString '& ";Returns Records=false;"
     curQdef.ODBCTimeout = timeout
     curQdef.sql = SQLString
     curQdef.Close

   End If
End Function

A chamada deve funcionar uma vez que este é o mesmo caminho que fazer mais chamadas ao banco de dados, mas, de alguma forma, esta consulta não está funcionando.

--EDIT Por solicitação Esta É a SQL que é colocado dentro da QueryDef.

SELECT IIf([Brand]='Somebrand','SO',IIf(left([Brand],6)='SomeOtherBrand',IIf(CHARINDEX('168/2013', [WVTA])=0,'FOOBAR','BARFOO'),IIf([Brand]='Anotherbrand','BAR',IIf([Brand]='YetAnotherBrand','Foo','ander')))) AS Company,  
qtmpgetVINApprovalData.VIN, qtmpgetVINApprovalData.WVTA, qtmpgetVINApprovalData.Variant,  qtmpgetVINApprovalData.Version, qtmpgetVINApprovalData.NKS, qtmpgetVINApprovalData.productionDate AS Production, 
qtmpgetVINApprovalData.color, qtmpgetVINApprovalData.actualMass,  qtmpgetVINApprovalData.NKS_YYYY,qtmpgetVINApprovalData.NKS_SSSS, '' AS engine, qtmpgetVINApprovalData.EmisTestMassWLTP, 
qtmpgetVINApprovalData.FrontalAreaWLTP, qtmpgetVINApprovalData.RoadLoadCoefF0WLTP,  qtmpgetVINApprovalData.RoadLoadCoefF1WLTP, qtmpgetVINApprovalData.RoadLoadCoefF2WLTP, 
qtmpgetVINApprovalData.WLTPCOMBINED_REPORTING_CO2 AS CombinedCo2ConditionA,  qtmpgetVINApprovalData.WLTPCOMBINED_REPORTING_CO2 AS CombinedCo2ConditionB, 
qtmpgetVINApprovalData.WLTPCOMBINED_REPORTING_FC AS CombinedFuelConsConditionA,  qtmpgetVINApprovalData.WLTPCOMBINED_REPORTING_FC AS CombinedFuelConsConditionB, 
qtmpgetVINApprovalData.OVCEnergyConsPureElectric AS ElectricEnergyConsConditionA, qtmpgetVINApprovalData.OVCEnergyConsPureElectric AS ElectricEnergyConsConditionB,  
qtmpgetVINApprovalData.ElectricEnergyConsPureElectric , qtmpgetVINApprovalData.ElectricRange, OVCEnergyConsPureElectric, OVCRange, OVCRangeCity,  
qtmpgetVINApprovalData.OVCRange AS ElectricRangeExternChargeable, qtmpgetVINApprovalData.WLTPLowCO2, qtmpgetVINApprovalData.WLTPMediumCO2, qtmpgetVINApprovalData.WLTPHighCO2,  
qtmpgetVINApprovalData.WLTPExtraHighCO2, qtmpgetVINApprovalData.WLTPCombinedCO2, qtmpgetVINApprovalData.WLTPWeightedCombinedCO2, qtmpgetVINApprovalData.WLTPLowFuelConsumption,  
qtmpgetVINApprovalData.WLTPMediumFuelConsumption, qtmpgetVINApprovalData.WLTPHighFuelConsumption, qtmpgetVINApprovalData.WLTPExtraHighFuelConsumption, 
qtmpgetVINApprovalData.WLTPCombinedFuelCons,  qtmpgetVINApprovalData.WLTPWeightedCombinedFuelCons, 0 as WltpElecEnergyConsPureElectric,0 as WltpElectricRangePureElectric,
0 as WltpElectricRangeCityPureElec,0 as WltpElectEnergyConsExternCharg,  0 as WltpElectricRangeExternCharg,0 as WltpElectricRangeCityExtCharg, qtmpgetVINApprovalData.EMISSIONSTAGE, 
colorahosid, UrbanConditionsCO2, UrbanConditionsFuelConsumption, ExtraUrbanConditionsCO2,  ExtraUrbanConditionsFuelCons, CombinedCO2, CombinedConditionsFC, WeightedCombinedCO2, 
WeightedCombinedFuelCons, ElectricEnergyConsWeightedComb, ElectricRangeNEDC, ElectricRangeCity  FROM (SELECT DISTINCT     


CASE WHEN isnull(WLTPLowFuelConsumption,0)>15
THEN 'Error: Fuel consumption value too high'
ELSE
    CASE WHEN isnull(WLTPMediumCO2,0)>800
    THEN 'Error: WLTP Medium CO2 value too high'
    ELSE
        case when isnull(RoadLoadCoefF1WLTP,0)>1000
        THEN 'Error: RoadLoadCoeff too high'
        ELSE        
            case when isnull(qvtvv.tvvid,0)=0 
                THEN 'ERROR: TVV (PCOC) data missing' 
                ELSE                   
                case when isnull(qvtvv.eurolevel,'X')='X' 
                        THEN 'ERROR: EmissionLegislation Missing' 
                        ELSE                   
                        CASE when isnull(cocVINList.prodDate,'1900-01-01')='1900-01-01' 
                                THEN 'ERROR: Production Date Missing' 
                                ELSE                      
                                CASE when isnull(qvTVV.colorAHOSID,999)=999 
                                        THEN 'ERROR: Color missing' 
                                        ELSE
                                            CASE when isnull(nkstvv.wvta,'X')='X' 
                                                then    --NKS                    
                                                    case when isnull(nkstvv.wvta,'X')='X' 
                                                            then --NKS   
                                                            case when isnull(dbo.cocVINList.[actualMass],0)=0 
                                                                    THEN 'ERROR: Actual Mass missing'
                                                                    else 'OK'
                                                            END                       
                                                            else --BASE VEH  
                                                            case when isnull(NKS_PCOC.NKS_MassRunningOrder,0)=0 
                                                                    THEN 'ERROR: Actual Mass missing' 
                                                                    else 'OK'
                                                            END
                                                        end                       
                                                ELSE  
                                                    case when [WVTACOCLayoutVersions].description like '%%2018%%WLTP%%' 
                                                            THEN 
                                                            case when isnull(EmisTestMassWLTP,0)=0 
                                                                    THEN 'ERROR: WLTP data missing' 
                                                                    else 'OK'
                                                            end
                                                            ELSE 'OK' 
                                                    END                    
                                            END                  
                                END                     
                        END              
                END
            END
        END
    END
END
as VALIDATION,   

dbo.cocVINList.VIN,        
dbo.cocVINList.NKS,             
case when isnull(nkstvv.wvta,'X')='X' then dbo.qvTVV.WVTA else nkstvv.wvta end as WVTA,           
case when isnull(nkstvv.Variant,'X')='X' then dbo.qvTVV.Variant else nkstvv.Variant end as Variant,          
case when isnull(nkstvv.Version,'X')='X' then dbo.qvTVV.Version else nkstvv.Version end as Version,             
case when isnull(nkstvv.wvta,'X')='X' then dbo.cocVINList.[actualMass] else NKS_PCOC.NKS_MassRunningOrder end as actualMass,             
case when isnull(nkstvv.wvta,'X')='X' then cocVINList.prodDate else NKS_PCOC.COC_Datum end as productionDate,             
case when isnull(nkstvv.wvta,'X')='X' then dbo.qvTVV.wvtadate else nkstvv.wvtadate end as WVTADate,             
qvTVV.color, qvTVV.colorAHOSID,              
qvTVV.Type_watermark_paper as Brand,     
case when isnull(nkstvv.wvta,'X')<>'X' THEN 
    case when charindex('2007/46',nkstvv.wvta)= 0 then qvtvv.NKS_YYYY END
END as NKS_YYYY,
case when isnull(nkstvv.wvta,'X')<>'X' THEN 
    case when charindex('2007/46',nkstvv.wvta)= 0 then qvTVV.NKS_SSSS END
END as NKS_SSSS,

 [EmisTestMassWLTP],             
 [FrontalAreaWLTP],             
 [RoadLoadCoefF0WLTP],             
 [RoadLoadCoefF1WLTP],             
 [RoadLoadCoefF2WLTP],             
 [CombinedConditionsCO2],             
 [CombinedConditionsFC],             
 [OVCEnergyConsPureElectric],             
 [OVCRange],             
 [OVCRangeCity],           
[ElectricEnergyConsPureElectric],    
         [ElectricRange],
         ElectricRangeCity,    
          [WLTPLowCO2],   

[WLTPMediumCO2],           
  [WLTPHighCO2],       
[WLTPExtraHighCO2],      
[WLTPCombinedCO2],   
[WLTPWeightedCombinedCO2],           
[WLTPLowFuelConsumption],     

[WLTPMediumFuelConsumption],            
[WLTPHighFuelConsumption],    
[WLTPExtraHighFuelConsumption],         
[WLTPCombinedFuelCons],       

[WLTPWeightedCombinedFuelCons],
ElectricEnergyConsWeightedComb,ElectricRangeNEDC,  
UrbanConditionsCO2, 
ExtraUrbanConditionsCO2, 
CombinedConditionsCO2 as CombinedCO2,    
UrbanConditionsFuelConsumption, 
ExtraUrbanConditionsFuelCons, 
WeightedCombinedCO2, 
WeightedCombinedFuelCons,             
case when [WLTPCombinedFuelCons]=0 
     THEN 
        CASE WHEN WLTPWeightedCombinedFuelCons=0 
             THEN NULL 
             ELSE WLTPWeightedCombinedFuelCons
        END 
     ELSE [WLTPCombinedFuelCons] 
END AS WLTPCOMBINED_REPORTING_FC ,             
case when [WLTPCombinedCO2]=0 
     THEN 
        CASE WHEN WLTPWeightedCombinedCO2=0 
             THEN NULL 
             ELSE WLTPWeightedCombinedCO2 
        END 
     ELSE [WLTPCombinedCO2]
END AS WLTPCOMBINED_REPORTING_CO2,              
case when isnull(EmisTestMassWLTP,0)>0 
     THEN 'WLTP' 
     else 
        case when [WVTACOCLayoutVersions].description like '%%2018%%WLTP%%' 
             THEN 'WLTP' 
             ELSE 'NEDC' 
        END 
END AS EMISSIONSTAGE,             
qvtvv.EmissionLegislation  , qvTVV.fname
FROM dbo.qvTVV 
inner join cocvinlist on qvTVV.vin=cocvinlist.vin      
left join qvtvvshort NKSTVV on nkstvvid=nkstvv.tvvid      
left join [WVTATVV] on [WVTATVV].tvvid=qvtvv.tvvid      
left join [WVTACOCLayoutVersions] 
    on [VersionNr]=case when isnull(nkstvv.cat,'X')='X' 
                        then dbo.qvTVV.LayoutVersion 
                        else nkstvv.LayoutVersion 
                   end      
    and [WVTACOCLayoutVersions].cat=case when isnull(nkstvv.cat,'X')='X' 
                                         then left(dbo.qvTVV.cat+'   ',2) 
                                         else left(nkstvv.cat+'   ',2) 
                                    end      
    and [WVTACOCLayoutVersions].completion=case when isnull(nkstvv.cat,'X')='X' 
                                                then dbo.qvTVV.completion 
                                                else nkstvv.completion 
                                            end      
left join NKS_PCOC on NKS_PCOC.VIN=qvTVV.VIN 
WHERE 
dbo.cocVINList.vin in (select VIN from codap_exportvins where cuser ='USER.NAME')  
and case when [WVTACOCLayoutVersions].description like '%%2018%%WLTP%%' then case when [EmisTestMassWLTP]=0 then 0 else 1 end else 1 end=1) as qtmpgetVINApprovalData where validation ='OK';

0
2019-09-17 16:50:46
origem
1 respostas

Simplesmente use input()

Algo como:

answer = input("Do you like to continue? y/n")
if answer == 'y':
    # continue
    pass
elif answer == 'n':
    # someting else
    pass
else:
    print("unknow answer")
+0
2019-09-17 18:04:27

Veja mais perguntas por marcas