Adding to CJ's suggestion:
I see that you have not posted a lot and surmise that you might be somewhat of a novice with Access. (If I'm wrong and you are comfortable with Access, I apologize.)
I recommend that you import to a table that you erase before each import step so that you can do testing or validation before actually adding that new information to the place where you want it to stay in the long run. You are less likely to need to perform manual intervention to undo mistakes if you pre-clean your data.
Yes, it takes an extra step or two, but if you can automate it more easily, do less handling, and in general prevent mistakes, it is a worthwhile effort.
You will probably need to do some reading. Since you are talking about having to play with looking in folders for files, I will suggest that you do some web searches (and search this forum) to look at
- Scripting File System Object. (That is its name, don't be put off by it.) That is what you would use to scan for files in folders with different names. This is what you would use to GET the next file name from which to do an import.
You will SOMETIMES see that written as FileSystemObject (without spaces). In order to use this object, you will need a code reference added to your project, which you can do by opening any code module (general or a Class module that is part of a form.) In the Menu bar, click Tools then References. Find the Scripting reference and check it. The list is alphabetically sorted so should be easy to find. Then close the References dialog box and save your module.
- TransferText method is a way to import a file in CSV format. Again, a web or forum search will tell you a lot about it.
As to importing data from the CSV file in two parts:
The first import should drop data into a table, but if there are column or row headers in that file, you might have to remove them first before using the INSERT INTO syntax to move data from the staging table to the final table. Therefore, read up on INSERT INTO syntax.
That might be enough to get you started in the right direction.