Front-end Access Backend Teradata

[tmcrouse]

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:

create
view dl_qpt_cqe.qryQualMain1 
AS
SELECT 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.qid
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 bus_unit.bus_id = qualmain.bus_id
ON
 comm_lvl.comm_lvl_id = qualmain.comm_lvl_id
ON
 communication.comm_id = qualmain.comm_id
ON
 contacts.contact_id = qualmain.contact_id
ON
 frequency.freq_id = qualmain.freq_id
ON
 lob.lob_id = qualmain.lob_id
ON
 measures.measures_id = qualmain.measures_id
ON
 month_table.mth_id = qualmain.mth_id
ON
 product.prod_id = qualmain.prod_id
ON
 program.prog_id = qualmain.prog_id
ON
 state.stid = qualmain.stid 
ON
 year_table.yr_id = qualmain.yr_id
WHERE 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?

[June7]

I never use dynamic parameterized query. I use VBA to pass filter criteria to form or report.

But I am not using VPN or Teradata.

[Missinglinq]

The problem is here is running Access across VPN, not the Teradata. Albert Kallal is a well known authority on the networking of MS Access, and has an often quoted article on the subject of attempting to run Access across WANs/VPNs:

http://www.kallal.ca/Wan/Wans.html

Sadly, the bottom line is that speed across a VPN is about 100 times slower than running Access on a typical LAN! To make it worse, connections frequently get dropped across WANs/VPNs, and Access simply does not tolerate dropped connections, the end result of which is usually a corrupted database!

But take heart; Albert does offer some possible workarounds!

Good Luck!

Linq ;0)>