David Carter
Consultant
David Carter
Share this content
0
13
4072

Access database with 1.8 million addresses

Access database with 1.8 million addresses

Needing to do some cold calling to drum up a bit of business I've bought myself the Yell UK Business Directory - amazing value at only £79 with 1.8 million addresses, phone number, no of employees, ISIC codes etc ( http://www.yell123.com/index.php?event=detail&id=12 )

My problem is that it comes in an Access database, and at 1.4 gigabytes and 1.8 million records my PC is grinding to a halt trying to handle it.

To make it manageable I'd like to chop it up into separate files - one for each county. But whatever I try, it simply locks up.

Can any Access expert tell me the simplest way of achieving this? Thanks

David Carter

Replies

Please login or register to join the discussion.

avatar
11th Jun 2009 22:36

Yes
It's a good quality database, pretty accurate so far. It gives phone number, name of senior person etc. My cold calling technique is pretty crappy but you can't blame that on the database.

I've spent some time segmenting it by company size (number of employees) and type of business (ISIC code).

The only problem for me has been the time spent in trying to handle 1.4 million records. In the end I managed to pull out all the businesses in each county (about 35000 records apiece) and saved them as individual Excel files. Then started the segmenting exercise.

But at £84 or whatever it's a gift, Buy it.

Thanks (0)
avatar
By Anonymous
12th Jun 2009 09:49

Astounding!
You mean there are chaps out there cold calling the clients...of other firms !!!
(said with absolutely no sense of irony!)

Thanks (0)
avatar
By Anonymous
09th Jun 2009 13:52

David
Are the addresses accurate? have you had a good experience calling these companies?

Thanks (0)
avatar
15th Apr 2009 10:24

the old Copy and Paste did it
Thanks for the suggestions, guys. I have a 512mb machine, so I suppose a 1.4gb database is bound to be a problem. Every suggestion including downloading SQL Express ended up with the PC grinding to a halt due to lack of memory.

In the end I managed to sort the database by County. Then slowly, ever so slowly, I managed to highlight about 25,000 records a time and copied and pasted them into separate Excel files. Took most of the evening but I only wanted 3 counties plus London. Thanks again, and maybe I ought to spend that £30 on 4gig of RAM (I can't believe this - now in the old days........). David

PS Looking at the Yell site, I notice that they now break up the UK directory into 3 separate CSV files, so maybe other people have been complaining. Great value though.

Thanks (0)
avatar
15th Apr 2009 09:48

and...
it is likely your database will comprise a lot of text fields, which will make it large and unwieldy. To speed up queries you could normalise it a bit.

As an example you could create a table of Counties with an index, and then a linking table between the addresses and the Counties. The linking table will be considerably smaller than the address table because it is simply a table of index numbers, and the indexes will work better because it is easier to sort numbers. And so it will speed up the process of creating extracts. By extending this table you can apply the technique to other fields you are interested in.

If you are then interested in further refining to get an extract list, you could make use of yes/no fields to user select records. Yes/Nos give tickboxes in the query view.

Of course you could further refine it to prove contact management...

You will need to make extensive use of the Access query stuff to do this. It is accessible if you are prepared to invest the time in learning it. And having a live example is a good way to learn. If you want any examples I would be happy to provide - leave a reply to this with contact details.

Thanks (0)
avatar
14th Apr 2009 16:49

boxing clever
use JCs trick and create a new access mdb and link to the tables in the yell source. That is simple enough - in 2003 its file, get external data, and link tables.

Opening a table with 1.8 million records is OK, but if you try and navigate it the PC will have to do a lot of work, and it will be slow. It is usually better to create queries to return a smaller subset of data - say just records for county = "Northumberland". You can use the query wizard for this. If you want to be clever use parameters [a county] rather than hard coded "Northumberland". You can use wildcards in the query.

If the queries are slow, and the records they are returning are small, then you might check the indexes in the source tables. In the example it the county field is indexed it will run much faster. You can check the indexes from the table properties.

Access has a very good query engine, and at 1.8 million records your target database is not particularly large.

And its query wizard is simple enough to use. As well as the various "how to " books, the help is actually quite helpful.

Thanks (0)
avatar
By Anonymous
14th Apr 2009 14:57

Suggested reading ....
Access 2007 - limits
http://office.microsoft.com/en-us/access/HA100307391033.aspx

2.0Gb minus system objects(?Gb) & data (1.8Gb) leaves little head room (say approx 10%) & once you get to that level there may be response issues.

see: http://bytes.com/groups/ms-access/648526-2-gb-size-limit-fix

Thanks (0)
avatar
By neileg
14th Apr 2009 13:29

Acces specs
Access 2000 up to 2003 has a file size limit of 2gb. I know A2007 is bigger but not sure how much. So you're not hitting Access limits here. Any half decent PC should be able to handle 1.4gb albeit slowly if it's poverty spec. So far as I am aware, moving to a different database platform will make little or no difference to the execution of simple SQL queries.

Look into replacing/upgrading/fixing your PC.

Excel has no chance of handling the data files, by the way (I know David knows that, but other readers may not).

Thanks (0)
avatar
14th Apr 2009 11:53

re Bob
I think you may be getting your numbers mixed up a bit there.

I would imagine that any half decent PC purchased in the last year or two would have enough RAM to run the database. You mention 2 and 4mb but I think that is going back to the early nineties in terms of what you would expect.

Most PC's now comes with a couple of gig of RAM. If David Carter does not have such capacity then I would recommend he purchase some cheap RAM for this and other purposes. 4gb of ram can be purchased for about £30!

Try that and see how you get on.

Also, perhaps contact Yell and ask them what there minimum system requirements are compared to the setup on which the database is being used.

Good luck.

Thanks (0)
avatar
By Anonymous
08th Apr 2009 11:44

Alternatively - try linking ...
If you don't want to migrate to SQL Server Express (free) with the upsizer then the following might help

You can get around the Access size limits by using linked tables

a. use your original db as the 'source'

b. create a new db as 'target' - to receive your county tables

c. create a Master db which is used to link to both your 'source' & 'target' db's - and link the tables

d. run SQL selects against your 'source' to extract the required info & insert results into 'target' table

OR

try the following single statement
INSERT Table2 SELECT * FROM Table1 WHERE [Conditions] - you may have to fully qualify the db's

Thanks (0)
avatar
07th Apr 2009 17:45

Not sure if it will work, but.
Hi David

It may not work but try doing a data query in Excel for each county (one will do to start). If this works you can then re-import each county as a table in access, keeping in mind how much disk space you have available

Give it a go; I will be interested to know the outcome.

Thanks (0)
avatar
By 3569787
03rd May 2016 19:36

I think your stuck!

Thanks (0)
avatar
By Anonymous
07th Apr 2009 08:59

Try SQL Server Express ....
Depending upon version of M$ Access you might be getting close to the limits

http://www.databasedev.co.uk/access_specifications.html

Try moving the whole thing to SQL Server Express (believe Express has a 4Gb limit?)
http://www.microsoft.com/express/sql/download/
(google search - 'migrate access to sql server express') - there is an 'upsizer' routine

http://www.sqlmag.com/Article/ArticleID/96299/sql_server_96299.html

Thanks (0)