Importing text file

[Guest]

I need help importing a text file that is 50MB into access. Below is a sample
of the data. There is a pattern to the data. The beginning of each record
starts with "STMTS". There is also a Carriage Return character in front
"STMTS". There are 16 fields in each record. I think this can be done by
writing some code in VBA but I do not know how to write the code. any help
would be appreciated.

Marianne

Sample Data:

STMTS 9999999-01 Timmy County Horses palomino horses for daffy duck
01-JAN-2004 31-MAR-2004 08-APR-2004 daffy
ducky 999999999 daffy A ducky
1111 American Highway street
AnywhereUSA KY 12345 STMTS 9999998-01
Timmy Country Horse palomino horse for daffys duck
01-JAN-2004 31-MAR-2004 08-APR-2004 daffy duck
888888888 daffy J ducky
123 Halfacre parkway
LEBANON MN 12345 STMTS 9999997-01 Timmy County
Horses palomino horses for daffy ducks 01-JAN-2004
31-MAR-2004 08-APR-2004 goofy smithyys 777777777
mickeys ands minne 5555
sMORES CREEK plat finely
NJ 12345 STMTS 5555555-01 timmy County horses
palomino horses for daffy duck 01-JAN-2004 31-MAR-2004
08-APR-2004 Basin MERRY 444444444 Merry G Basin
222 RTe 666
pagosa springs FL 12345
STMTS .....

 

[Guest]

Hi Marianne,

I would suggest using the "Get External Data" option in Access and then
select Import. Specify your text file and use the wizard to tell Access how
to 'see' the data in the file. You can specify fixed spaces to define where
data begins and ends in a column and if this data import is a repetative
process, save the import specification so that you can use the setup over and
over again in VBA coding. I use the saved Import Specifications monthly to
import around 5000 records into one of our Access database tables and it
works so smoothly.

The VBA code you could use after you setup your Import specification would
look similar to:

DoCmd.TransferText acImportDelim, "ImportSpecName",
"AccessTabletoImportInto", FileName

Hope this helps!