querying a sorted large table

[pierre]

Hello,

I would like to extract data from a 3 million records text
file linked table. Off course, doing a simple query takes
a lot of time. Since the records are already sorted (by
alphabetical order on two colums), I hope I could use this
feature to speed up the filtering.

Does someone know how to do this ?

Thank you very much for your help

 

[Jeff Boyce]

Pierre

One time or regularly? If you'll need to do this repeatedly, could you
import the data? You could add indexing to speed up query/filter if you had
Access tables.

Good luck

Jeff Boyce
<Access MVP>

 

[pierre]

the table will be changed weekly. I thought about importing and indexing, but that in itself would be very long. I hope there is a way to tell access that it can search/sort, etc the data very fast because it is already sorted.

 

[John Nurick]

the table will be changed weekly. I thought about importing
and indexing, but that in itself would be very long. I hope
there is a way to tell access that it can search/sort, etc
the data very fast because it is already sorted.

Access doesn't have a built-in way of doing this. If it's a fixed-width
file textfile you can open it using VBA's random access file i/o
statements; you could then do a binary search (e.g. if the current
record is after the one you are searching for, go to the record halfway
between the start of the file and your current position; if that's
before the record you want, jump half way back towards where you
started; and so on until you find the right one).

With a delimited file, you could builds an Access table containing an
index of the file (the primary key of each record, its offset from the
start of the file and the length of the record); having done this you
can treat the text file as a random access file and find individual
records quickly.

But either of these is more trouble and may not be faster than simply
importing the file to an Access table.