Assuming that the sequence of the records determines to whom the "new
address" belongs, this would not be a big problem to to in VBA code... you
would only have a problem if the addresses are freeform and may exceed 255
bytes. In that case, you would either have to define those Fields in the
Table as Memo or you would have to attempt to parse the addresses into
component parts before storing in the Record in the Table. For purposes of
the example, I am going to assume that the old address and the new address
are both less than 256 bytes or that you've defined them as Memo, and that
the fields in the file record begin in the same byte in each record (e.g.,
it is a fixed-field text file)
First, create the Table (I call it "tblOldAndNew" in the example) with the
Fields you want (I called them FirstName, LastName, OldAddress, and
NewAddress) in the record. In a standard module, or the module associated
with a form from which you are going to kick off the operation:
Dim intFile as Integer 'File number used by BASIC to read
Dim strRecord as String 'Buffer into which to read the Record
Dim db as DAO.Database
Dim rs as DAO.Recordset
CONST FNPos as Integer = <you insert the position of the FirstName>
CONST FNLen as Integer = <you insert the max length of the FirstName>
CONST LNPos as Integer = <you insert the position of the LastName>
CONST LNLen as Integer = <you insert the max length of the LastName>
CONST AddrPos as Integer = <you insert the position of the Address>
CONST AddrLen as Integer = <you insert the max length of the Address>
Set db = CurrentDB 'Set database to current
database
Set rs = db.OpenRecordset ("tblOldAndNew") 'Open the table you defined
intFile = FreeFile 'Get file number from
System
Open "<youinsertpathandfile>" For Input As intFile
Line Input #intFile, strRecord 'Read entire first line from
File (firstname,
'lastname,
oldaddress)
Do Until EOF(intFile) 'Process entire file
rs.AddNew
rs("FirstName") = Mid(strRecord,FNPos,FNLen)
rs("LastName") = Mid(strRecord,LNPos,LNLen)
rs("OldAddress") = Mid(strRecord,AddrPos,AddrLen)
Line Input #intFile, strRecord 'Read second line from File
(new address)
rs("NewAddress") = Mid(strRecord,AddrPos,AddrLen)
rs.Update 'Add the new Record to
the Table
Line Input #intFile, strRecord 'Read entire first line of next
Record (next
'person) from
File
Loop 'End of Do loop
Close intFile
rs.Close 'Close Recordset opened on Table
Set rs = Nothing 'Release memory used by recordset
object
Set db = Nothing 'Release memory used by database object
This, as you may guess, is "aircode", completely untested... and there may
be some detail slipups, but I have written a lot of code to read files and
populate tables, so the approach is correct. Now, don't forget... I said if
either address exceeds 255 bytes, that Field in the Table will have to be
defined as Memo, or you'll have to define the component Fields (such as
Street Address, etc.).
Good luck with your application.
Larry Linson
Microsoft Access MVP