Access VBA to Import Oracle Table: ODBC–Call Failed

I have written a VBA code in Access 2007 to run a SQL query on Oracle and insert results in an Access table using a Pass-Through query. I am getting the following error: “ODBC–Call Failed“. Below is the code:

If TableExists("" & TblName) = True Then DoCmd.DeleteObject acTable, TblName

CurrentDb.Execute "CREATE TABLE " & TblName & "(Merch_Nbr Text, Month_End_Date Text, Passer Text, PasserMonth Text, Volume Long, DiscountRevenue Long)"

'Create a Pass through query to extract MID List from Oracle table
Dim PassThruQuery As QueryDef
If QueryExists("Query_oracle") = True Then DoCmd.DeleteObject acQuery, "Query_oracle"
Set PassThruQuery = CurrentDb.CreateQueryDef("Query_oracle")

'SQL query to be executed on Oracle database
sql_stmt = "SELECT M.MERCH_NBR as Merch_Nbr,CM.MONTH_END_DATE as Month_End_Date,PSR.PASSER as Passer,PSR.MONTH as PasserMonth,sum(F.MERCH_BILLED_ASSESSMENTS) as Assessments,"
sql_stmt = sql_stmt & "sum(F.SALES_VOLUME-F.RETURNS_VOLUME) as Volume,sum(F.DISCOUNT_REVENUE) as DiscountRevenue "
sql_stmt = sql_stmt & "FROM WH_TARGET.RPSI_MONETARY_FACT F,WH_TARGET.RPSI_MERCHANT_DIM M,WH_TARGET.RPSI_CARD_TYPE_CHARGE_TYPE_DIM CT"
sql_stmt = sql_stmt & ",WH_TARGET.RPSI_PRICING_CATEGORY_DIM PC,WH_TARGET.RPSI_CALENDAR_MONTH_DIM CM,WH_TARGET.RPSI_BACK_END_VENDOR_DIM BEV"
sql_stmt = sql_stmt & ",WH_TARGET.RPSI_SALES_GROUP_DIM SG_H,WH_TARGET.RPSI_SALES_REP_DIM SR_H,WH_TARGET.RPSI_AGENT_BANK_DIM AB_H"
sql_stmt = sql_stmt & ",WH_TARGET.RPSI_ASSOCIATION_DIM ASSC_H,WH_TARGET.RPSI_SALES_GROUP_DIM SG_C,WH_TARGET.RPSI_SALES_REP_DIM SR_C"
sql_stmt = sql_stmt & ",WH_TARGET.RPSI_AGENT_BANK_DIM AB_C,WH_TARGET.RPSI_ASSOCIATION_DIM ASSC_C,dw_biz.portfolio_mgmt PSR "
sql_stmt = sql_stmt & "WHERE Month_End_Date >= '01-OCT-2014' AND PSR.MONTH = '01-JAN-2015' "
sql_stmt = sql_stmt & "AND psr.TYPE = 'MON' AND CT.CARD_TYPE in ('1','2','5') AND PSR.MID = M.MERCH_NBR "
sql_stmt = sql_stmt & "AND F.MERCHANT_SID_FK = M.MERCHANT_SID_PK AND F.CARD_TYPE_CHARGE_TYPE_SID_FK = CT.CARD_TYPE_CHARGE_TYPE_SID_PK "
sql_stmt = sql_stmt & "AND F.PRICING_CATEGORY_SID_FK = PC.PRICING_CATEGORY_SID_PK AND F.CALENDAR_MONTH_SID_FK = CM.CALENDAR_MONTH_SID_PK "
sql_stmt = sql_stmt & "AND F.BACK_END_VENDOR_SID_FK = BEV.BACK_END_VENDOR_SID_PK AND F.SALES_GROUP_SID_FK = SG_H.SALES_GROUP_SID_PK "
sql_stmt = sql_stmt & "AND F.SALES_REP_SID_FK = SR_H.SALES_REP_SID_PK AND F.AGENT_BANK_SID_FK = AB_H.AGENT_BANK_SID_PK "
sql_stmt = sql_stmt & "AND F.ASSOCIATION_SID_FK = ASSC_H.ASSOCIATION_SID_PK AND M.SALES_GROUP_SID_FK = SG_C.SALES_GROUP_SID_PK "
sql_stmt = sql_stmt & "AND M.SALES_REP_SID_FK = SR_C.SALES_REP_SID_PK AND M.AGENT_BANK_SID_FK = AB_C.AGENT_BANK_SID_PK "
sql_stmt = sql_stmt & "AND M.ASSOCIATION_SID_FK = ASSC_C.ASSOCIATION_SID_PK "
sql_stmt = sql_stmt & "GROUP BY M.MERCH_NBR,CM.MONTH_END_DATE,PSR.PASSER,PSR.MONTH"


PassThruQuery.SQL = sql_stmt

PassThruQuery.Connect = "ODBC;DSN=RDW1;UID=dw_biz;PWD=dw_biz"
PassThruQuery.ReturnsRecords = True

'Insert the Query results into Access Table
CurrentDb.Execute "INSERT INTO " & TblName & "(Merch_Nbr, Month_End_Date, Passer, PasserMonth, Volume, DiscountRevenue) " & _
"SELECT Query_oracle.Merch_Nbr, Query_oracle.Month_End_Date, Query_oracle.Passer, Query_oracle.PasserMonth, Query_oracle.Volume, Query_oracle.DiscountRevenue  From Query_oracle;"


Exit Sub

I exported this query to a text file and ran it on Oracle, it is running fine! Also, I created a temp table on Oracle with the above SQL query and replaced sql_stmt in VBA with “select * from new_oracle_table”. Strangely, this seems to be working fine as well. Do you think it’s something to do with the length of the SQL query in sql_stmt itself? Any ideas on how to fix this would be greatly appreciated.

-SS

Answer

I was able to solve this error. Just copy the below code into your error handler and it will give you a more detailed description of the ODBC–Call Failed error

'DAO Error Handler
Dim MyError As Error
Debug.print Errors.Count
For Each MyError In DBEngine.Errors
With MyError
MsgBox .Number & " " & .Description
End With
Next MyError

This told me that my ODBC connection was being timed out due to the large size of the SQL query. Just follow the steps in the link below to resolve the error.

http://its.uiowa.edu/support/article/101855