Having an issue with my ODBC connection to Teradata within Access

Third Party Software
Enthusiast

Having an issue with my ODBC connection to Teradata within Access

I built an Access DB and front-end application. My application and everything in Access worked perfectly fine. The problem is no one else could use it unless I emailed it or put it on our shared drive. So, the mgmnt team decided to migrate the back-end to Teradata Labs. I finished migrating all my tables and data, however still using the Access front-end. All my applications work fine but my Data Entry and Data Update forms. I have queries that are to pull the data from the existing tables so the end-users can see what is there before they enter something new or make changes to existing. Both forms are separate and perform separate functions. I am going to put the code I have and not sure how to alter it to make it work. The error I get everytime is ODBC failed but it does not give any sort of this is how you fix it or go to this article to see how to fix it. My ODBC connection is there and working. I verified that I could pull up all the tables in my linked area of Access. So, it has something to do with how I have the query within the two forms. Here is the one for the Data Entry form:

[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.qid, DL_QPT_CQE_qualmain.prog_entdt, DL_QPT_CQE_qualmain.attachment

FROM DL_QPT_CQE_qualmain

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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.qid) Like "*" & [forms]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.prog_entdt) Like "*" & [forms]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.attachment) Like "*" & [forms]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[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]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.qid) Like "*" & [forms]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.prog_entdt) Like "*" & [forms]![frmProgramsEntry].[txtSearch] & "*")) OR (((DL_QPT_CQE_qualmain.attachment) Like "*" & [forms]![frmProgramsEntry].[txtSearch] & "*"));

[/code]

To give a bit more information, prior to the migration a table name for example was QualMain. With the migration, the IT department created a database within the data labs area and our database is dl_qpt_cqe, however it is capitalized and not sure if that matters or not. When I look at my ODBC it is DL_QPT_CQE_QUALMAIN. If I can get someone to tell me what I am doing wrong that would be great. Thanks.

1 REPLY
Senior Apprentice

Re: Having an issue with my ODBC connection to Teradata within Access

Without formatting this is hardly readable (I know it's Access).

But a qualified table name looks like databasename.tablename, so it should be DL_QPT_CQE.QUALMAIN