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 spreadsheet (lets call it file a) used by 100 ish people each
with their own local copy. This spreadsheet has code to retrieve data
from another spreadsheet (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 sheet 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,