Access Query Using Criteria In Linked Excel Table (1 Viewer)

A

Atwoodworker

Guest
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.

I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.

The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.

When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:31
Joined
Aug 11, 2003
Messages
11,696
Query-ing over many records is going to take some time.

Faster may be:
1) Do it directly in access
2) have the right Indexes set up (for fields you mostly use)

Hope it helps...

Greetz
 

Users who are viewing this thread

Top Bottom