So we have migrated our backend to Teradata. Very painful to use this with Access. I have beat my head against the wall trying to get my queries into Teradata because leaving them in Access and just ODBC to the tables and then alter the queries the response time is very slow. I have tried passthrough queries, but I get errors that the object does not exist on my year_table and I know it does. I have QCd everything. After doing some research, I found I can create a view in Teradata which is like a query. I went ahead and did this. Oddly enough, the speed is no different. Leaving the query in Access it processes in about 2mins. (I am VPN into server, then the server has to query through 500k records and return data. Not bad if you ask me). Placing the queries in Teradata as a view, same thing. 2mins. Now, on to my problem.
I have dataentry and dataupdate forms. I can put the query in the view in Teradata, but it does not like the pointing to the forms portion. I have tried to get help from the Teradata folks and they say this is an Access question. OK. Think they are wrong, but here is my code:
Code:createview dl_qpt_cqe.qryQualMain1ASSELECT qualmain.prog_id, qualmain.stid, qualmain.contact_id, qualmain.yr_id, qualmain.mth_id, qualmain.freq_id, qualmain.bus_id, qualmain.lob_id, qualmain.prod_id, measures.measures_id, comm_lvl.comm_lvl_id, communication.comm_id, qualmain.attachment, qualmain.prog_entdt, qualmain.qidFROMDL_QPT_CQE.year_tableINNERJOIN DL_QPT_CQE.stateINNERJOIN DL_QPT_CQE.programINNERJOIN DL_QPT_CQE.productINNERJOIN DL_QPT_CQE.month_tableINNERJOIN DL_QPT_CQE.measuresINNERJOIN DL_QPT_CQE.lobINNERJOIN DL_QPT_CQE.frequencyINNERJOIN DL_QPT_CQE.contactsINNERJOIN DL_QPT_CQE.communicationINNERJOIN DL_QPT_CQE.comm_lvlINNERJOIN DL_QPT_CQE.bus_unitINNERJOIN DL_QPT_CQE.qualmain ON bus_unit.bus_id = qualmain.bus_idONcomm_lvl.comm_lvl_id = qualmain.comm_lvl_idONcommunication.comm_id = qualmain.comm_idONcontacts.contact_id = qualmain.contact_idONfrequency.freq_id = qualmain.freq_idONlob.lob_id = qualmain.lob_idONmeasures.measures_id = qualmain.measures_idONmonth_table.mth_id = qualmain.mth_idONproduct.prod_id = qualmain.prod_idONprogram.prog_id = qualmain.prog_idONstate.stid = qualmain.stidONyear_table.yr_id = qualmain.yr_idWHERE qualmain.prog_id=[forms]![frmProgramsEntry]! [ListQuality];
The error is logic error and it has that on the qualmain.prog_id=[forms] portion. If I remove that the query will run but then it does not know to point to this form. Maybe I should try removing it anyway and then place that query in the form?