Solved

microsoft access and royalmail PAF file running very slow on search

Posted on 2009-05-13
1
309 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:andybrooke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
andybrooke earned 0 total points
ID: 24376860
OK i must be having a totally thick day!!!! i found indexing and when i indexed it worked in milli seconds. sorry to waste your time!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question