Access (Oh Oh my files got big!)

[notreallyme]

I have created a Access DB that allows me to do what needs to be done.

Right now I get the data from the client and use that.

I recently found out I can get the data directly from the Gov't. Which means instead of having one clients data for one year I have everyones data for 6 years.

There are approximately 20 text files that are 1 GIGABYTE each. What is the best (or a way that will work) to access these DB's (text files).

Can I query them through Access to pull data as needed? (That should be somewhat managable, but I am concerned about the time to run the queries, and I would have to figure out how to query off a TXT file )

We do have a SQL server for the RMS stuff, that is an option if it needs to be. Then I'd have to deal with ODBC connections. Not sure how fast or slow that would be.
I ma somewhat familiar with linking to EDM's and RDM's from RMS, Could I set it up like that? (Not that I know that much about the background of those things).

I need to query off the data often and run analyses from the queried data, the queries vary but are Generally by State and a LOB.

Any help is appreciated.

[[If necessary I will hire a temp to set up the DB, we should only get new data each year]]

[E. Blackadder]

If you can't link tables, then you still have some options.

You could try reading the big files one line at a time with VBA, and spliting out (or just selecting) the inputs based whether the record is interesting to you. I could try digging up some code where I did this a few years back.

SAS is an option, if it's an option. Foxpro might be an option, too.

[notreallyme]

Originally Posted by E. Blackadder

If you can't link tables, then you still have some options.

I think I could link tables. Link from Text Files? I have no idea how, link from Access tables?

Would that mean create 20 Access Db's?

[IMP]

don't forget to compress your database files...there's tons of unused wasted space in each file/database

[notreallyme]

Originally Posted by IMP

don't forget to compress your database files...there's tons of unused wasted space in each file/database

They are text files at the moment, and still over 2 GIG (in some cases).

[MountainHawk]

I think you can link to a text file, as long as it's in 'database format'.

Are they csv?

[notreallyme]

Originally Posted by MountainHawk

I think you can link to a text file, as long as it's in 'database format'.

Are they csv?

Txt and they don't have headers. (Though I do have the 'key'). i.e. Characters 1-2 are Field1 Format String, Characters 3- 6 are Numerical, etc.

[Random Poster]

Originally Posted by notreallyme

I think I could link tables. Link from Text Files? I have no idea how, link from Access tables?

Would that mean create 20 Access Db's?

You may be able to link to all of the files within a single Access database. Working with files this large could be more than Access can handle though.

To create a linked table, just right-click anywhere in the white space of the Tables tab in the database window and select Link Tables. Change the file type to Text Files, select a file and then follow the wizard. This will work with delimited (CSV) or fixed width files.

Access 97 had a file size limit of 1GB. This limit doubled with newer versions. I do not think you will be able to directly link to a file that exceeds this limit.

There is a way around this though. If you create the link using a smaller file that has the correct structure and then replace the small file, the link should still be valid.

Running queries off of these tables could be painfully slow though.

HTH,
RP

[Steve Grondin]

Be stingy in your data types. If you have a nine byte number with decimal places, use integer and "remember" the decimal places elsewhere.

If you have a date-time field and you only care about the date, you can use integer for YYYYMMDD if you don't do anything other than simple comparisons. Day-differencing rountines would become difficult then.

You certainly won't get a 20 to 1 compression with this, but it can help. SQL has more data-type options to do these kind of tricks with (not to mention no problem with the 2 gig limit).

[vchagas]

There are several different ways to do what you want, with Access or with other DBMS.

I'm assuming you don't want to redo your access queries.

With Access, my approach would be to import each TXT table into a separate Access DB and then link them to the master Access DB (that way you can leverage you size limit from 2GB to 2GB for each MDB file.
Cons: it will probably be to heavy for Access to handle (too slow)

With other DB, import the TXT files into SQL Server (easiest way to avoid the 2GB size limit) and link each table to Access.

Redoing your queries in another commercial software (that i'm used to work with):
1st choice : SAS
2nd choice : Visual Fox Pro (2GB limit for each DBF file)

Redoing your queries in another free/open source software (that i'm used to work with):
1st choice : SQLite
2nd choice : MySQL
you can use these two as a backbone for access instead of SQL Server.