How to fix 'ODCB call failed during OpenRecordSet'

Question

I am currently working on some changes to an existing Access(vba) application. But suddenly I am running into an ODCB call failed error while running a QueryDef. The tables are linked to a SQL server database.

I've tried resetting the links to the tables via the linked table manager. Also debugged to see if the query was indeed defined and that the SQL within is correct.

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

The makePassThroughQuery function looks like this:

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

The call should work since this is the same way we do most calls to the database, but somehow this query is not working.

--EDIT By request This Is the SQL that is placed within the 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';

Share the passthrough query SQL. That's where your error likely is. — Erik A, Sep 17 '19 at 14:56
@ErikA Do you mean that it is likely the SQL that is not valid SQL? — Mathias Kiekens, Sep 17 '19 at 14:58
Yup, but the whole situation is a bit unclear to me. Your passthrough query creation code sets `curQdef.Connect = ConnectString '& ";Returns Records=false;"`, which means you're not returning records, but you're trying to create a recordset anyway? And instead of using `QueryDef.OpenRecordset` you're querying off the query, which is odd as well — Erik A, Sep 17 '19 at 15:14
yes the sole purpose of this function is to create a QueryDef to be used later. It is quite strange but It's being used to many times to start messing with it I'm afraid. I'm having a look at how I can share the SQL without sharing to much details. — Mathias Kiekens, Sep 17 '19 at 15:32
Never use this `On Error Resume Next` or `On Error GoTo 0` as you suppress errors. And yes check if *SQLString* is valid SQL Server query. — Parfait, Sep 17 '19 at 15:39
@Parfait This is just a hotfix of an existing application before we replace it with a new one, but thanks or the advice — Mathias Kiekens, Sep 17 '19 at 15:42
That's an epic query. Hard to find syntax issues. Make sure it runs OK in your SQL Server console: Management Studio or `sqlcmd`. — Parfait, Sep 17 '19 at 15:42
@Parfait yes it is quite hard to find anything in there, it does run in server studio and does return the wanted results — Mathias Kiekens, Sep 17 '19 at 15:43
How are you reading or building that huge string in VBA? Via concatenation? — Parfait, Sep 17 '19 at 15:45
@Parfait partially via concatenation but there is also a access query in there that I put the contents in a select query because I would not make sense otherwise — Mathias Kiekens, Sep 17 '19 at 15:48

score 1 · Accepted Answer · answered Sep 17 '19 at 16:04

Essentially, you are conflating SQL dialects. Using DAO's Database.OpenRecordset calls the Access engine which can only read MS Access SQL dialect (not SQL Server). This action will fail on dbo., CASE, CHARINDEX, and others.

Instead, consider as @ErikA comments to use the QueryDef.OpenRecordset underlying recordset which will decide which backend SQL engine to run:

Dim qdef As QueryDef
Set qdef = DB.QueryDefs("qryName")
Set rsNew = qdef.OpenRecordset(dbSeeChanges)
...

Further reading: SQL Server Passthrough query as basis for a DAO recordset in Access .

How to fix 'ODCB call failed during OpenRecordSet'

1 Answers1