ms-accessdata-import

Import txt file into ms access m:m table


I'm quite new to MS Access. I just set up a new Access database to be used with our schematics tool. Our tool can access the database, so this works fine. At the end of our schematics capturing process, we generate a bill of material (BOM), listing every part that is in the design.

I want to be able to evaluate which part (each has a unique part ID "TUPID") is used on which board (unique board-ID "UBRID") - so I set up a Many-to-Many-table. First tests entering some data there by hand look good, importing text-files containing "UBRID", "TUPID" works fine too.

Now I want to be able to import the generated BOM, which is plain text containing only the "TUPID" - the "UBRID" is not known by the schematics tool. I know, I can manipulate the text file before I run a query, but I would rather not. I want to start the import operation from a form, which lists all boards (thus getting the "UBRID" by selecting the relevant board) then I want to start the import - but how is this done? Do I have to run a query for each line of the text file, or what is the best way to achieve my goal?

Looking forward for any tips!


Solution

  • Assuming import is for one UBRID set at a time - import records then run UPDATE action to populate those records with UBRID. Something like:

    CurrentDb.Execute "UPDATE tablename SET UBRID = '" & Me.UBRID & "' WHERE UBRID IS NUll"