Results 1 to 7 of 7
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    ODBC Failed connecting Access to Teradata DataEntry/Update Forms

    I created an Access DB that works perfectly with the exception that I have to email it or put it on a server and end-users have to bring it to their computer, make changes then reload it to server for me to grab the updates. This was a temporary fix and we now have our permanent fix which is the Teradata labs. I have migrated the backend and all tables to the Teradata labs and now am left with my front-end application in Access. Everything works great but my data entry and my update form. I took the query that was set up prior using Access as the back-end and updated it to point to the linked tables from Teradata. An example of the table before in Access QualMain. With Teradata the link is DL_QPT_CQE_QualMain. So, here is my code using the linked tables but when I click on the form data entry for example, it tells me ODBC failed. I know the links work because I can open each one alone and see the data. So, the problem is my query and I am not sure exactly what is wrong with it. Even though I get this ODBC failed error, if I click ok, it does take me to the form, but the subform that has the data pulled based on my query does not show up.

    Code:
    SELECT IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[HEDIS_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID])) AS HEDISMeasure, IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[SUB_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID])) AS SubMeasure, IIf(IsNull([dl_qpt_cqe_QualMain].[YR_ID]),Null,DLookUp("[YR]","[dl_qpt_cqe_Year_Table]","[dl_qpt_cqe_Year_Table].[YR_ID]=" & [dl_qpt_cqe_QualMain].[YR_ID])) AS YR, IIf(IsNull([dl_qpt_cqe_QualMain].[MTH_ID]),Null,DLookUp("[MTH]","[dl_qpt_cqe_Month_Table]","[dl_qpt_cqe_Month_Table].[MTH_ID]=" & [dl_qpt_cqe_QualMain].[MTH_ID])) AS Mth, IIf(IsNull([dl_qpt_cqe_QualMain].[PROG_ID]),Null,DLookUp("[PROG_NM]","[dl_qpt_cqe_Program]","[dl_qpt_cqe_Program].[PROG_ID]=" & [dl_qpt_cqe_QualMain].[PROG_ID])) AS ProgNm, IIf(IsNull([dl_qpt_cqe_qualmain].[bus_id]),Null,DLookUp("[BUS_UNIT]","[dl_qpt_cqe_Bus_Unit]","[dl_qpt_cqe_Bus_Unit].[BUS_ID]=" & [dl_qpt_cqe_QualMain].[BUS_ID])) AS BusUnit, IIf(IsNull([dl_qpt_cqe_qualmain].[contact_id]),Null,DLookUp("[CONTACT]","[dl_qpt_cqe_Contacts]","[dl_qpt_cqe_Contacts].[CONTACT_ID]=" & [dl_qpt_cqe_QualMain].[CONTACT_ID])) AS Contact, IIf(IsNull([dl_qpt_cqe_qualmain].[freq_id]),Null,DLookUp("[FREQ]","[dl_qpt_cqe_Frequency]","[dl_qpt_cqe_Frequency].[FREQ_ID]=" & [dl_qpt_cqe_QualMain].[FREQ_ID])) AS Freq, IIf(IsNull([dl_qpt_cqe_QualMain].[STID]),Null,DLookUp("[STCD]","[dl_qpt_cqe_State]","[dl_qpt_cqe_State].[STID]=" & [dl_qpt_cqe_QualMain].[STID])) AS ST, IIf(IsNull([dl_qpt_cqe_QualMain].[LOB_ID]),Null,DLookUp("[LOB]","[dl_qpt_cqe_LOB]","[dl_qpt_cqe_LOB].[LOB_ID]=" & [dl_qpt_cqe_QualMain].[LOB_ID])) AS LOB, IIf(IsNull([dl_qpt_cqe_QualMain].[PROD_ID]),Null,DLookUp("[PROD_NM]","[dl_qpt_cqe_Product]","[dl_qpt_cqe_Product].[PROD_ID]=" & [dl_qpt_cqe_QualMain].[PROD_ID])) AS ProdNM, IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_ID]),Null,DLookUp("[COMM_TYPE]","[dl_qpt_cqe_Communication]","[dl_qpt_cqe_Communication].[COMM_ID]=" & [dl_qpt_cqe_QualMain].[COMM_ID])) AS CommType, IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_LVL_ID]),Null,DLookUp("[COMM_LVL]","[dl_qpt_cqe_Comm_LVL]","[dl_qpt_cqe_Comm_LVL].[COMM_LVL_ID]=" & [dl_qpt_cqe_QualMain].[COMM_LVL_ID])) AS CommLvl, DL_QPT_CQE_qualmain.q1update, DL_QPT_CQE_qualmain.q2update, DL_QPT_CQE_qualmain.q3update, DL_QPT_CQE_qualmain.q4update, DL_QPT_CQE_qualmain.qid, DL_QPT_CQE_qualmain.prog_entdt, DL_QPT_CQE_qualmain.attachment
    FROM DL_QPT_CQE_year_table INNER JOIN (DL_QPT_CQE_state INNER JOIN (DL_QPT_CQE_program INNER JOIN (DL_QPT_CQE_product INNER JOIN (DL_QPT_CQE_month_table INNER JOIN (DL_QPT_CQE_measures INNER JOIN (DL_QPT_CQE_lob INNER JOIN (DL_QPT_CQE_frequency INNER JOIN (DL_QPT_CQE_contacts INNER JOIN (DL_QPT_CQE_communication INNER JOIN (DL_QPT_CQE_comm_lvl INNER JOIN (DL_QPT_CQE_bus_unit INNER JOIN DL_QPT_CQE_qualmain ON DL_QPT_CQE_bus_unit.bus_id = DL_QPT_CQE_qualmain.bus_id) ON DL_QPT_CQE_comm_lvl.comm_lvl_id = DL_QPT_CQE_qualmain.comm_lvl_id) ON DL_QPT_CQE_communication.comm_id = DL_QPT_CQE_qualmain.comm_id) ON DL_QPT_CQE_contacts.contact_id = DL_QPT_CQE_qualmain.contact_id) ON DL_QPT_CQE_frequency.freq_id = DL_QPT_CQE_qualmain.freq_id) ON DL_QPT_CQE_lob.lob_id = DL_QPT_CQE_qualmain.lob_id) ON DL_QPT_CQE_measures.measures_id = DL_QPT_CQE_qualmain.measures_id) ON DL_QPT_CQE_month_table.mth_id = DL_QPT_CQE_qualmain.mth_id) ON DL_QPT_CQE_product.prod_id = DL_QPT_CQE_qualmain.prod_id) ON DL_QPT_CQE_program.prog_id = DL_QPT_CQE_qualmain.prog_id) ON DL_QPT_CQE_state.stid = DL_QPT_CQE_qualmain.stid) ON DL_QPT_CQE_year_table.yr_id = DL_QPT_CQE_qualmain.yr_id
    WHERE (((IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[HEDIS_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[SUB_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[YR_ID]),Null,DLookUp("[YR]","[dl_qpt_cqe_Year_Table]","[dl_qpt_cqe_Year_Table].[YR_ID]=" & [dl_qpt_cqe_QualMain].[YR_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[MTH_ID]),Null,DLookUp("[MTH]","[dl_qpt_cqe_Month_Table]","[dl_qpt_cqe_Month_Table].[MTH_ID]=" & [dl_qpt_cqe_QualMain].[MTH_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[PROG_ID]),Null,DLookUp("[PROG_NM]","[dl_qpt_cqe_Program]","[dl_qpt_cqe_Program].[PROG_ID]=" & [dl_qpt_cqe_QualMain].[PROG_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_qualmain].[bus_id]),Null,DLookUp("[BUS_UNIT]","[dl_qpt_cqe_Bus_Unit]","[dl_qpt_cqe_Bus_Unit].[BUS_ID]=" & [dl_qpt_cqe_QualMain].[BUS_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_qualmain].[contact_id]),Null,DLookUp("[CONTACT]","[dl_qpt_cqe_Contacts]","[dl_qpt_cqe_Contacts].[CONTACT_ID]=" & [dl_qpt_cqe_QualMain].[CONTACT_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_qualmain].[freq_id]),Null,DLookUp("[FREQ]","[dl_qpt_cqe_Frequency]","[dl_qpt_cqe_Frequency].[FREQ_ID]=" & [dl_qpt_cqe_QualMain].[FREQ_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[STID]),Null,DLookUp("[STCD]","[dl_qpt_cqe_State]","[dl_qpt_cqe_State].[STID]=" & [dl_qpt_cqe_QualMain].[STID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[LOB_ID]),Null,DLookUp("[LOB]","[dl_qpt_cqe_LOB]","[dl_qpt_cqe_LOB].[LOB_ID]=" & [dl_qpt_cqe_QualMain].[LOB_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[PROD_ID]),Null,DLookUp("[PROD_NM]","[dl_qpt_cqe_Product]","[dl_qpt_cqe_Product].[PROD_ID]=" & [dl_qpt_cqe_QualMain].[PROD_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_ID]),Null,DLookUp("[COMM_TYPE]","[dl_qpt_cqe_Communication]","[dl_qpt_cqe_Communication].[COMM_ID]=" & [dl_qpt_cqe_QualMain].[COMM_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_LVL_ID]),Null,DLookUp("[COMM_LVL]","[dl_qpt_cqe_Comm_LVL]","[dl_qpt_cqe_Comm_LVL].[COMM_LVL_ID]=" & [dl_qpt_cqe_QualMain].[COMM_LVL_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q1update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q2update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q3update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q4update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.qid) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.prog_entdt) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.attachment) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[LOB_ID]),Null,DLookUp("[LOB]","[dl_qpt_cqe_LOB]","[dl_qpt_cqe_LOB].[LOB_ID]=" & [dl_qpt_cqe_QualMain].[LOB_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[PROD_ID]),Null,DLookUp("[PROD_NM]","[dl_qpt_cqe_Product]","[dl_qpt_cqe_Product].[PROD_ID]=" & [dl_qpt_cqe_QualMain].[PROD_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_ID]),Null,DLookUp("[COMM_TYPE]","[dl_qpt_cqe_Communication]","[dl_qpt_cqe_Communication].[COMM_ID]=" & [dl_qpt_cqe_QualMain].[COMM_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_LVL_ID]),Null,DLookUp("[COMM_LVL]","[dl_qpt_cqe_Comm_LVL]","[dl_qpt_cqe_Comm_LVL].[COMM_LVL_ID]=" & [dl_qpt_cqe_QualMain].[COMM_LVL_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q1update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q2update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q3update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.q4update) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.qid) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.prog_entdt) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.attachment) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*"));


    ***I am posting an update because I removed everything but 1 column and also removed the lookup items and null information and using just my dl_qpt_cqe_qualmain join to dl_qpt_cqe_measures and just pulling in the qid and the hedis_measure, it works fine. No lookups anything. The rest of the columns just say error because I have not added them. So, I did and I get the ODBC call error. My new code is right, it has something to do with the available time to return the query. Wondering if there is a way around this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If your main concern is getting odbc connectivity between Access and Teradata, then I'd start with a few simple queries.

    IsNull is a vba function and this is something else

    Code:
    SELECT IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[HEDIS_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID])) AS HEDISMeasure, IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[SUB_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID])) AS SubMeasure, IIf(IsNull([dl_qpt_cqe_QualMain].[YR_ID]),Null,DLookUp("[YR]","[dl_qpt_cqe_Year_Table]","[dl_qpt_cqe_Year_Table].[YR_ID]=" & [dl_qpt_cqe_QualMain].[YR_ID])) AS YR, IIf(IsNull([dl_qpt_cqe_QualMain].[MTH_ID]),Null,DLookUp("[MTH]","[dl_qpt_cqe_Month_Table]","[dl_qpt_cqe_Month_Table].[MTH_ID]=" & [dl_qpt_cqe_QualMain].[MTH_ID])) AS Mth, IIf(IsNull([dl_qpt_cqe_QualMain].[PROG_ID]),Null,DLookUp("[PROG_NM]","[dl_qpt_cqe_Program]","[dl_qpt_cqe_Program].[PROG_ID]=" & [dl_qpt_cqe_QualMain].[PROG_ID])) AS ProgNm, IIf(IsNull([dl_qpt_cqe_qualmain].[bus_id]),Null,DLookUp("[BUS_UNIT]","[dl_qpt_cqe_Bus_Unit]","[dl_qpt_cqe_Bus_Unit].[BUS_ID]=" & [dl_qpt_cqe_QualMain].[BUS_ID])) AS BusUnit, IIf(IsNull([dl_qpt_cqe_qualmain].[contact_id]),Null,DLookUp("[CONTACT]","[dl_qpt_cqe_Contacts]","[dl_qpt_cqe_Contacts].[CONTACT_ID]=" & [dl_qpt_cqe_QualMain].[CONTACT_ID])) AS Contact, IIf(IsNull([dl_qpt_cqe_qualmain].[freq_id]),Null,DLookUp("[FREQ]","[dl_qpt_cqe_Frequency]","[dl_qpt_cqe_Frequency].[FREQ_ID]=" & [dl_qpt_cqe_QualMain].[FREQ_ID])) AS Freq, IIf(IsNull([dl_qpt_cqe_QualMain].[STID]),Null,DLookUp("[STCD]","[dl_qpt_cqe_State]","[dl_qpt_cqe_State].[STID]=" & [dl_qpt_cqe_QualMain].[STID])) AS ST, IIf(IsNull([dl_qpt_cqe_QualMain].[LOB_ID]),Null,DLookUp("[LOB]","[dl_qpt_cqe_LOB]","[dl_qpt_cqe_LOB].[LOB_ID]=" & [dl_qpt_cqe_QualMain].[LOB_ID])) AS LOB, IIf(IsNull([dl_qpt_cqe_QualMain].[PROD_ID]),Null,DLookUp("[PROD_NM]","[dl_qpt_cqe_Product]","[dl_qpt_cqe_Product].[PROD_ID]=" & [dl_qpt_cqe_QualMain].[PROD_ID])) AS ProdNM, IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_ID]),Null,DLookUp("[COMM_TYPE]","[dl_qpt_cqe_Communication]","[dl_qpt_cqe_Communication].[COMM_ID]=" & [dl_qpt_cqe_QualMain].[COMM_ID])) AS CommType, IIf(IsNull([dl_qpt_cqe_QualMain].[COMM_LVL_ID]),Null,DLookUp("[COMM_LVL]","[dl_qpt_cqe_Comm_LVL]","[dl_qpt_cqe_Comm_LVL].[COMM_LVL_ID]=" & [dl_qpt_cqe_QualMain].[COMM_LVL_ID])) AS CommLvl, DL_QPT_CQE_qualmain.q1update, DL_QPT_CQE_qualmain.q2update, DL_QPT_CQE_qualmain.q3update, DL_QPT_CQE_qualmain.q4update, DL_QPT_CQE_qualmain.qid, DL_QPT_CQE_qualmain.prog_entdt, DL_QPT_CQE_qualmain.attachment
    FROM DL_QPT_CQE_year_table INNER JOIN (DL_QPT_CQE_state INNER JOIN (DL_QPT_CQE_program INNER JOIN (DL_QPT_CQE_product INNER JOIN (DL_QPT_CQE_month_table INNER JOIN (DL_QPT_CQE_measures INNER JOIN (DL_QPT_CQE_lob INNER JOIN (DL_QPT_CQE_frequency INNER JOIN (DL_QPT_CQE_contacts INNER JOIN (DL_QPT_CQE_communication INNER JOIN (DL_QPT_CQE_comm_lvl INNER JOIN (DL_QPT_CQE_bus_unit INNER JOIN DL_QPT_CQE_qualmain ON DL_QPT_CQE_bus_unit.bus_id = DL_QPT_CQE_qualmain.bus_id) ON DL_QPT_CQE_comm_lvl.comm_lvl_id = DL_QPT_CQE_qualmain.comm_lvl_id) ON DL_QPT_CQE_communication.comm_id = DL_QPT_CQE_qualmain.comm_id) ON DL_QPT_CQE_contacts.contact_id = DL_QPT_CQE_qualmain.contact_id) ON DL_QPT_CQE_frequency.freq_id = DL_QPT_CQE_qualmain.freq_id) ON DL_QPT_CQE_lob.lob_id = DL_QPT_CQE_qualmain.lob_id) ON DL_QPT_CQE_measures.measures_id = DL_QPT_CQE_qualmain.measures_id) ON DL_QPT_CQE_month_table.mth_id = DL_QPT_CQE_qualmain.mth_id) ON DL_QPT_CQE_product.prod_id = DL_QPT_CQE_qualmain.prod_id) ON DL_QPT_CQE_program.prog_id = DL_QPT_CQE_qualmain.prog_id) ON DL_QPT_CQE_state.stid = DL_QPT_CQE_qualmain.stid) ON DL_QPT_CQE_year_table.yr_id = DL_QPT_CQE_qualmain.yr_id
    WHERE (((IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[HEDIS_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[MEASURES_ID]),Null,DLookUp("[SUB_MEASURE]","[dl_qpt_cqe_Measures]","[dl_qpt_cqe_Measures].[MEASURES_ID]=" & [dl_qpt_cqe_QualMain].[MEASURES_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[YR_ID]),Null,DLookUp("[YR]","[dl_qpt_cqe_Year_Table]","[dl_qpt_cqe_Year_Table].[YR_ID]=" & [dl_qpt_cqe_QualMain].[YR_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[MTH_ID]),Null,DLookUp("[MTH]","[dl_qpt_cqe_Month_Table]","[dl_qpt_cqe_Month_Table].[MTH_ID]=" & [dl_qpt_cqe_QualMain].[MTH_ID]))) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*")) OR (((IIf(IsNull([dl_qpt_cqe_QualMain].[PROG_ID]),Null,DLookUp("[PROG_NM]","[dl_qpt_cqe_Program]","[dl_qp

    What exactly did you try? And WHAT exactly was the ODBC error?

    I haven't used Teradata, but if it has/supports ODBC then I'm sure you can find some configuration examples. Do you have a DBA responsible for Teradata? That person may be most knowledgeable of your set up and needs.

  3. #3
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Ok. So on my query where I have the like "*" [forms]![frmProgramsUpdate].[txtSearch]& "*"))..............I removed that. That is a criteria piece. I thought let's move that from the query and just try running the query and not looking at the form that uses this query. I kicked the query off and it is still running. The other way I got ODBC call failed within about 2 seconds. This is because this is a data entry and the other is data update forms. The query has to grab 537k rows and return those rows to Access and I think that is why it is going to take this query quit a bit of time to return the information back to me. I am doing this via VPN and hitting the Teradata server and this Teradata server just happens to house all of our insurance claims. I think putting this database on the same server as claims was not smart but a quick fix. I'll bet the reason I get the ODBC call failed within 2 seconds when pointing to the form is because the temp space is not big enough. This happened a lot at my old job when hitting our claims system which was DB2 and I used SAS for that and I would get timeouts all the time. The only time I did not get a timeout was over the weekend. I am thinking maybe if I change this to a passthrough query that might help?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you run a simple query along these lines?

    Select EmployeeName from EmployeeTable
    Where EmployeeName like "A*"

    Are you getting ODBC to connect and return data?
    The sql query in your first post could be too long, and no one is likely to read through it.

    What exactly is the issue you are having?
    Keep it simple. Make something simple work and expand and test.

  5. #5
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    I don't get the connection ODBC call failed if doing just 1 item. Form example I did the below code and it returned the results on the form, but the rest of the form looked as the attached picture.

    Code:
    SELECT DL_QPT_CQE_qualmain.qid
    FROM DL_QPT_CQE_qualmain
    WHERE (((DL_QPT_CQE_qualmain.qid) Like "*" & [forms]![frmProgramsUpdate].[txtSearch] & "*"))
    ORDER BY DL_QPT_CQE_qualmain.qid;
    Attachment 17334

    If I add the next table which is the below code, it will attempt to run the query and it gets about 3/4th of the way through where the green bar is that shows the query is running and then responds with ODBC call failed. I suspect it is just too big which then makes me wonder why we migrated to a server? It all works great in Access alone however no way to disperse the Access DB and front-end so it is really usable because everyone would have their own version and then have to send me updates.

  6. #6
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Another issue, is I tried to put my query from Access into Teradata and just run the SQL query and see if I could just save it in Teradata and the error there is not enough spool space. So, that makes me think they have a cap on the tempspace that any user for querying.

  7. #7
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    I give up. There are so many issues here. 1) Our database was approved to be placed on a server where our claims are housed. Everyone within the organization uses this server so all users get a set amount of tempspace. This is the reason for my spool error because not 1 user has the spool space to run large queries. Interesting. 2) Teradata is set up to return maximum of 2000 rows. I attempted running the simple query from above in Teradata and received that error and I have to click yes if I want to go ahead and return the 500K plus rows. There is no way to do this on the front-end Access application to say oh by the way give me all the rows. 3) I cannot find a way to create these same queries in Teradata and then have the query ODBC to Access.

    So, I am just going to tell management based on all of this, data entry forms and data update forms are not possible using Teradata as the backend. I am not really sure Teradata was to be used like this anyway. I know at my old employer, United Health, we used Teradata and also DB2 to house our claims and the team I was on we used 10% of the orgs tempspace due to our giangantic queries and projects. That was the most of any team. I will be the only one entering new programs so no need to data entry or update forms anyway.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ODBC Call Failed - ORA 01013 in Access 2010
    By kawi6rr in forum Import/Export Data
    Replies: 2
    Last Post: 06-22-2013, 09:49 PM
  2. Connecting sybase thru ms-access odbc
    By kolokotr in forum Access
    Replies: 1
    Last Post: 08-15-2012, 12:08 PM
  3. Connecting to Access ODBC behind a router
    By justinmregan87 in forum Access
    Replies: 0
    Last Post: 02-29-2012, 10:23 AM
  4. Replies: 0
    Last Post: 11-30-2010, 12:23 PM
  5. Connecting sybase thru ms-access odbc
    By murali.kothuru in forum Queries
    Replies: 2
    Last Post: 08-01-2010, 10:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums