How to read text file in a module and load it to access tables

[AccessDe]

Hello group,
I am new to access module development.Here is the
requirement. I have an text file with different records
and record lengths. I need to write a module to read thru
the file and update access tables for each record type
with in the file. I am looking for some examples on how to
to do this. Any example will be of great help.

the text file looks like this

01abcd
01abcd
02hhhhhhhhhhhhhh
02hhhhhhhhhhhhhh
03kkkkkkkkkkkkkkkkkkk
01abcd
01abcd
06gggggggg

 

[Tim Ferguson]

I need to write a module to read thru
the file and update access tables for each record type
with in the file.

This can be fairly basic or hideously complex: it depends on how structured
the text file is.

01abcd
01abcd
02hhhhhhhhhhhhhh
02hhhhhhhhhhhhhh
03kkkkkkkkkkkkkkkkkkk
01abcd
01abcd
06ggggggg

Oh dear: given that this file has duplicate records (do you want a count,
or something else?) and presumably some kind of structure (is the length of
the char repeat significant, or the number of different chars), you will
need to build your data dictionary very carefully. That's the bunch of
rules that defines what each item in the database means.

I am sure that there is some sense in this stuff, and that it can be
translated into a decent database; but it's not possible to guess it from
what you have posted. My hunch is that it will take a significant amount of
coding to parse this into tables.

B Wishes


Tim F

 

[accessde]

The length of the fields are different in each record type.
Record type 01 will go into one table,record type 02 into
another table. There can be duplicate records types.I
cannot have a delimited file becoz of records are of
different length. I have to read by the record type and
update the tables in access.
Revised example

record type 01
first 2 position : 01
next 9 postions : ssn
next 8 postions : date
example : 0112345678920040104

record type 02:
first 2 positions: 02
next 8 positons : start date
next 8 postion : end date

example :

like..wise..more record types..

0112345678920040104
022004010420041004
03......................
04.........................

...

Hope this is more clear..an example of reading a text
file by each record and updating access tables will help
me get going.

Thanks

 

[Tim Ferguson]

There can be duplicate records types.

Duplicate types (easy) or duplicate records (sign of design problems...)?

record type 01
first 2 position : 01
next 9 postions : ssn
next 8 postions : date
example : 0112345678920040104

record type 02:
first 2 positions: 02
next 8 positons : start date
next 8 postion : end date

Okay: this looks a lot easier. You'll still have to write a VBA routine to
do it, but at this level it is simple string slicing. Look up the following
in the help files and check out the examples:

Open Statement, Input Line statement, Mid$() function.

Hope that helps


Tim F