Import data (result from a query) using ODBC

[frontside]

I´m running some queries using linked tables, the customer complains about that it takes to long to run the different reports, therefor I´m planning on importing the tables and then run the reports(the update needs to be done every morning or ones every two days).

Theres no problem importing the tables using macro (havent tried vb yet) but I would want to import the whole structure of the table and only the data (between two dates from a form). not like now when i get the whole table with all the data.

Can I find a tutorial or example of how I can do this? or any ideas (I guess I´m not the only one that has trouble with queries taking to long when using linked odbc tables?

Micke
"Sweden

 

[jsteph]

First, what you want to do can be done by importing (one time only) the table--using 'structure only' option. You should know that there may be ways to make the odbc linked tables work well as a report or query source, but I'll start by going with your import-locally method.

You create an append query based on the odbc-linked table appending into this structure you imported. (You obviously clear the local table first before appending).

When ODBC linked tables are slow, look at the indexing of the backend db. What db is it? If it's Oracle or SQL server, then see if you can add an appropriate index based on how you select your data (you had said between dates--so which date field is this).

Also, don't sort in the append query, this can be done locally once the data is there. Don't join on local tables, don't use Access function calls in a field expression in the query. All these things slow it down.

Once the append query is done, you can do all sorts of stuff locally that will run much faster. But you may find that a few of the hints above--such as adding indexes, etc, will allow you to run queries on the raw odbc data with good performance.
--Jim