Given that this is something you will do many times, your best bet is to write some VBA code.
You will need to look up the following topics, which will comprise your tool set for this operation.
Open statement
Close statement
Input Line statement
InStr function
Len function
Mid function
Left function
Right function
Edit function
OpenRecordset method
CurrentDB function
Close method (applied to recordset objects)
AddNew method (if you regenerate the tables from scratch)
Edit method and Seek method (if you update the tables)
Update method
In overview, you can do this from a form or a macro, both of which are capable of running code. If you do it from a macro, you have to write the code as a function. If you do it from a form, even an unbound one, you can write the code as a simple subroutine with no return value.
I might take the approach of importing the new data to an empty (but pre-existing) staging table to allow you to do some preliminary data massaging, then use an update or append query as appropriate.
The import routine I would used in the scenario I suggested looks like this, in broad-brush terms:
1. Open the input text file
2. Open the recordset
3. Try to read a new line. If you are not at EOF, proceed.
4. Add a new record to the recordset
5. Parse the individual lines of your data groups using InStr to find the constant part that represents your field names. Also find the colons that separate the field name from the field data.
6. For each line that applies to the record, verify the field name, and if it passes, insert the field data.
7. When you have finished the data grouping, update the recordset.
8. Go back to step 3 unless at end of input file.
9. Close everything you opened.
OK, that takes care of the import. From there, all you have to do can probably be done with no more than three or four queries, and maybe only one or two queries.
Now, I'll step back and touch on my line-item 6 a bit more.
If your format for each input line is exactly <fieldname>:<fielddata>, then you can use InStr to find the colon. The position of the colon gives you two values. First, you know where the field name ends. Second, you know where the data area begins. If the import table's field's correct name appears to the left of the colon, use the Left function to extract it. If the import table's data appears to the right of the colon, use the Right function to extract it. Let's say you assign the long integer loPos to the position of the colon.
loPos = InStr( 1, Line, ":" ) {or is it InStr( 1, ":", line) .... please check that one, I always forget and have to look it up...}
recset.Fields(Left$(Line,loPos-1)) = Right$(Line, Len(Line)-loPos)
This method DOES NOT WORK if the field name in the staging table doesn't exactly match the field name that appear in your text file. You might also wish to impose an Edit function on the data string before you store it.
