Genealogy project

[angeb]

I have a large text file that I wish to import to an access database. The information has been extracted from the Victorian, Australian births CD's. It has 2,007,951 entries. Column headers are year (four digit yyyy only), RegNo (5 digit), Gender (m/f with many fields blank), Surname, First Names, Father1st, Father2nd, Father3rd, Father4th, MotherSurname, Mother1st, Mother2nd, Mother3rd, Mother4th, Birth Place, RegInfo, Parish, Denom.

Of all these, Year, Gender, Birth Place, Parish and Denom can be in their own sub tables.

I am starting to think that access may not be suitable for this size project and this particular file is the smaller of 3 that I wish to do the same thing with. As I mentioned in my intro, I am pretty much a beginner in access, I have some awareness of what can be achieved, but the actual 'how' is something I need assistance with. I am running access 2007.

Any help would be appreciated. Thanks

 

[plog]

We can usually help with the how, but first you must provide the what.

Purpose? Goal? Expectations?

 

[angeb]

The original information from the 3 text files, covers births, deaths and marriages in victoria, and is contained on 8 desperate discs. I found myself constantly changing out discs after searching for just 1 person. After finding a copy of these discs in text files, I decided to try to put it all into a database to make searching much easier, preferably with all 3 text files incorporated into the one database.
When running a search you need reasonably flexible parameters, ie surname brook could be misspelled brok, brooke, brooks etc, same goes for each field. I have already worked out how to run the search, and ran a test using a small section of the data in a test database, but due to the sheer amount of information I keep running into problems.
Ie, I have successfully exported my births text delimited file into a single table, but when I try to split off the separate table for birth place, I am told that the query exceeds the 2gb limit etc.
I was going to try leaving the table as is, but it is painfully slow when searching, sorting or filtering the information.
Would I be best to set up the database first, with the required tables, then export the text file? I wasnt sure that could work as the data already exists.
Sorry if i sound like a total noob, but that's cos I am lol.

 

[plog]

Ie, I have successfully exported my births text delimited file into a single table, but when I try to split off the separate table for birth place, I am told that the query exceeds the 2gb limit etc.

That's full of confusing jargon and ambigous terms. I still don't know exactly what you are trying to achieve with access. My educated guess is you are trying to get all the data from the discs into Access tables.

If that is the case, you should set up what you think the table structure should be, import 1 disc's worth of data into a temporary table, then move that data to the appopriate table in your structure using APPEND queries. Once you have moved the data into your structure, test it. See if you can get the data out that you want. Once you have proven your structure works, start on the rest of the discs and load all your data.

If I was wrong in what you want, please explain again and avoid any database jargon--talk to me like a 3rd grader who has no idea what any database terms mean.

 

[JLCantara]

Oh là là! What a case...

1 - Split your app so that you have a front end (FE) that contains everything except the table.
2 - Create as many BE as needed: only transfer chunks of 500 000 records.
3 - Link the FE to the BEs.
4 - Learn to use VBA to access tables.

Why do you want to split your main table?
Since you have plenty of text fields, make sure they have minimum size: good deal of space can be saved!

 

[jdraw]

Just to emphasize what plog has asked/suggested.
What exactly do you want to do with the data? The answer(s) to that will have a major impact on the design of your tables. And the design of tables will have a big influence on how you do data manipulation to import/place your disk-based data into your tables.
If you are looking to do queries/reports of family hierarchies, then investigate design of hierarchical data structures before you get too deeply into Access.

If, as you said, the 2GB limit is an issue, you could go to SQL Server Express (free) for your tables and still use Access as front end.

The key is to identify WHAT you expect to do with the data (regardless of where and how it is stored).

Good luck.