andybrooke
asked on
microsoft access and royalmail PAF file running very slow on search
I have purchased the royalmail PAF file. It came on disc and is basicaly a text file (CSV)
From this i created a database and linked to the text file as a table. then i created 2 forms:
1st form is were you enter the postcode and then when you press the command button it uses a simple query i created to filter the linked table and then it displays the found address in the 2nd form.
It all work perfect except for a big problem "SPEED" as the CSV file has 1.7 million postcodes and 13 columns it takes a good 3 min to search the table.
What can i do to make it as quick as i see other peoples postcode programs work?
have i done this right in using a query to search the tables? or should i use VBA. my database is a basic access MDB type. I have also tried importing the data instead of linking but this doesnt make any difference.
I have thought about can you index a table, if so how? also maybe spliting the tabel into many tables but then how would i get the query to search many tables?
Any help would be great
From this i created a database and linked to the text file as a table. then i created 2 forms:
1st form is were you enter the postcode and then when you press the command button it uses a simple query i created to filter the linked table and then it displays the found address in the 2nd form.
It all work perfect except for a big problem "SPEED" as the CSV file has 1.7 million postcodes and 13 columns it takes a good 3 min to search the table.
What can i do to make it as quick as i see other peoples postcode programs work?
have i done this right in using a query to search the tables? or should i use VBA. my database is a basic access MDB type. I have also tried importing the data instead of linking but this doesnt make any difference.
I have thought about can you index a table, if so how? also maybe spliting the tabel into many tables but then how would i get the query to search many tables?
Any help would be great
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.