How to store queries

Database
Enthusiast

How to store queries

I have all my tablles in teradata and I have a front-end in access. I have all my VBA, forms and queries set up in access but want to move the queries to the teradata side and then link to those queries as you do the tables. Is there a way to do this? I went thru functions and stored procedures but do not see how to perform this function.

I am asking because the tables are not large but the forms utilize some intricate querying and big queries. I have 1 form that has 11 listboxes and those listboxes output a report. When I do a simple ODBC to the teradata tables, it takes 15-20 minutes to link. My largest table is only 509k rows. Everything is indexed. When I load the 1 form that has 11 listboxes, it takes 10-15 minutes to show up on the screen. When I click for results that takes about 5 mins. When I reset that takes about 5 mins. I cannot run my update or data entry forms at all because I have a datasheet subform to show what is already in the database so users can see and then enter new info if needed or update info and that times out everytime. I have set ODBC to 0 and still times out.

Have meeting with teradata person later today.

2 REPLIES
Enthusiast

Re: How to store queries

45 people have viewed and no answers? Well I continued my research and I see that I can create a view. I can set the view up to look like my query. Infact, I just copied my SQL code from Access for the query into Teradata SQL Express and then put create view and did AS Select and my query and now I have a view and when I ODBC, I can see it and can use it for the record source in the properties field. I cannot however dothis with my listboxes. I have 11 on this form. I have to have a single select query in each listbox rowsource because I have to ASC and cannot do an order by with a view. I need for example my states to be CA, CO, CT, etc. I tried to do a view for my state query in Teradata Express and it began how the data loaded with my batch load. So, WI ended up first and that is not how they want it. I will continue to research to see if I can bypass this.

Enthusiast

Re: How to store queries

I'll start by saying that I have no experience with Access, so I do not know what a listbox is. However, views are intended to be queried. So, if you can specify query text and you needed to order by "col3" and your view is "someview", you could write SQL like this:

SELECT Col1,Col2,Col3
FROM SomeView vw
ORDER BY Col3 ASC;