size

[Guest]

I have a database in CSV format (1 Gigabyte in size) rougly 15 M records.

I need to pull it in access and run queries by postcode / name / ID. but
access keeps freezing... and too slow....


any solutions?

 

[Gina Whipp]

What version of Access? I can't remember what versions have what size
limitations. To find out yourself just go to Help and type "Access
Specifications". If your version lists a 1gig size limitations you may have
to 'clean' your data and import, which probably wouldn't be a bad idea to
begin with. AND if your Access version does have a 1 gig size limitation
then you might want to consider doing something else.

HTH

 

[John Vinson]

I have a database in CSV format (1 Gigabyte in size) rougly 15 M records.

I need to pull it in access and run queries by postcode / name / ID. but
access keeps freezing... and too slow....


any solutions?

Are you Importing this into a native Access table, or linking to the
csv file? The text file cannot be indexed, and Access must do a lot of
extra work to search it, so it will indeed be slow. I'd suggest
starting with a completely empty Access .mdb file; creating a table,
empty, with the desired field names and field types; and putting
nonunique Indexes on the postcode and name fields, and (if the ID is
in fact unique) making it the Primary Key.

Then use File... Get External Data... Import to import the textfile
into the table.

The indexes on the field should improve performance, but fifteen
million records is a pretty hefty size file!

John W. Vinson[MVP]