Retrieve selected data from Network file

Hi,

I have posted about this a few months ago and never really found a
solution and am having to make do, however the problem is getting
larger and i really would appreciate some help.

I have a spread*** (lets call it file a) used by 100 ish people each
with their own local copy. This spread*** has code to retrieve data
from another spread*** (file b) stored on a network drive. File b on
the network drive has over 60000 rows of data. However the data
required by each user is only approx 3000 rows. The network connection
speed is slow.

I need to retrieve the data without opening file b. Each user
retrieves different data, filtered by a unique code for each user
which is is column c of file b.

I currently use the code below to retrieve the data.

Sheets("Sheet1").Range("A1").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=Excel Files;DBQ= " & PATHANDNAMEANDTYPE &
";DefaultDir= " & PATH &
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
..CommandText = Array( _
"SELECT `" & FILNAMES & "`.`This Id`, `" & FILNAMES & "`.Outlet, `" &
FILNAMES & "`.`Street Address`, `" & FILNAMES & "`.Locality, `" &
FILNAMES & "`.Town, `" & FILNAMES & "`.County, `" & FILNAMES &
"`.`Outer Postcode`, `" & FILNAMES & "`.`Inner Postcode`, `" &
FILNAMES & "`.`Phone No#`, " _
, _
"`" & FILNAMES & "`.`Outlet Status`, `" & FILNAMES & "`.Tenure,
`" & FILNAMES & "`.`Primary Streetmap`, `" & FILNAMES & "`.`Last BDE
Visit`, `" & FILNAMES & "`.Owner, `" & FILNAMES & "`.Operator, `" &
FILNAMES & "`.`BDE Territory`, `" & FILNAMES & "`.Region, `" &
FILNAMES & "`.Division" & Chr(13) & "" & Chr(10) & "FRO" _
, _
"M `" & PATHANDNAME & "`.`" & FILNAMES & "` `" & FILNAMES & "`" &
Chr(13) & "" & Chr(10) & "WHERE (`" & FILNAMES & "`.`BTerritory`='" &
BNO & "')" _
)
.Refresh BackgroundQuery:=False
End With

However this takes around 9 minutes!

I have tested and i can retrieve a named range (eg. a1:p5000) from the
same file in 70 seconds (obviously no querying / where clause to
filter records involved). It is because of this I feel i should be
able to improve the time it takes to query the records and retrieve
the matching ones.

I did think about copying the whole *** of file b and filtering once
copied into file a but this causes out of memory errors.

I have been previously pointed towards ado however I cannot see how i
can filter the records using this method.

This is seriously hindering the use of this file and i would
appreciate ANY help or suggestions.

Thanks,
.

Loading