Criteria for importing tables from a odbc link

[Matt Creel]

I have a odbc link to a back end database. I need to update 7 tables every
month in order to run a report. When I tried to link the tables to the
access database that I wanted to work with, the queries that I ran would take
too long (30 mins each). I also tried to import the tables and write vba
code to delete and re-import the tables every time that I want to run the
report. I then ran into the problem that it takes around 2 hours to import
all of the information. Is there a way to set criteria (ex. a date range)
that I could use when pulling the data. I do not know SQL very well but I am
guessing that I could somehow use a select-where statement.

 

[Klatuu]

Importing the data from the linked tables is a bad idea for several reasons.
As you noticed, it is a slow process with any volume of data. Also,
importing the large amounts of data then deleting them will cause you serious
bloat. It would not take long to pop the 2G size limit.

You did not say what the back end database engine is.
If it is an Access database (Jet Engine), it would be faster to do a direct
link rather than an ODBC connection.
If it is SQL Server or some other more sophisticated engine, why not do the
updates on that side before you do the reporting?

As to filtering by date, yes, you do use a WHERE clause to define what data
you want. If they are Access queries, you can enter that in the Criteria row
in query design view.

Maybe if you were a bit more specific, we could give better info.

 

[Matt Creel]

Sorry about being vague. I believe that it is an SQL database, I was not
here when it was made, nor do I have the ability to modify it. All I have
access to are very large tables stored inside of it. I solved this problem
by learning SQL and writting a VBA program to run a pass-through query with
crieria given in a form. Thank you for the help.

Matt Creel