Here it goes...
The Census dept of our lovely gov't dropped another installment on news agencies yesterday. Usually we get to test other states, but I am in NY and we got released first this time. The text files are 180 meg. compressed thus estimated exploded over 3 gig. They give us an Access 97 db with empty shells for the text files to import into. If you can see the problem, we have decided for multiple tables. More like 10 CD's each with one db containing a series of tables. (After 6 hours of FTP and another 4 hours of importing for 2 people.)
BUT what really roasts my rutabaga is that each table in the db (there are 76 tables of them) have the first 5 columns at 255 characters. In reality it was never over a 6-2-3-2-7 combination. They also pack each table to close to (as not if) 255 columns. The last 250 in integer format.
So when you go to resize the a text column in design mode, Access says ok then chokes on the save table. Both in 97 and 2000.
Anybody got any ideas how to rebuild the shell tables with the smaller field sizes...
I am thinking of programmatically (with a loop because the table name are all SF30001 through SF30076) with tabledefs to copy the table def then write an new table shell. (I have never worked with table def's...) Can you access the table attributes via table defs and if not how and samples? Then we do the data imports. Import specs are too cumbersome to build because each table has different columns after the first 5, that need to be preserved to match the incoming text files.
Many news bureaus beside myself are wondering.......
PS Great site, I have used many suggestions and have recommend it other programmers to it.
The Census dept of our lovely gov't dropped another installment on news agencies yesterday. Usually we get to test other states, but I am in NY and we got released first this time. The text files are 180 meg. compressed thus estimated exploded over 3 gig. They give us an Access 97 db with empty shells for the text files to import into. If you can see the problem, we have decided for multiple tables. More like 10 CD's each with one db containing a series of tables. (After 6 hours of FTP and another 4 hours of importing for 2 people.)
BUT what really roasts my rutabaga is that each table in the db (there are 76 tables of them) have the first 5 columns at 255 characters. In reality it was never over a 6-2-3-2-7 combination. They also pack each table to close to (as not if) 255 columns. The last 250 in integer format.
So when you go to resize the a text column in design mode, Access says ok then chokes on the save table. Both in 97 and 2000.
Anybody got any ideas how to rebuild the shell tables with the smaller field sizes...
I am thinking of programmatically (with a loop because the table name are all SF30001 through SF30076) with tabledefs to copy the table def then write an new table shell. (I have never worked with table def's...) Can you access the table attributes via table defs and if not how and samples? Then we do the data imports. Import specs are too cumbersome to build because each table has different columns after the first 5, that need to be preserved to match the incoming text files.
Many news bureaus beside myself are wondering.......
PS Great site, I have used many suggestions and have recommend it other programmers to it.
