Hi All,
I have been trying to find a solution to this for a while now without success, it seems i am trying to do something no-one else has wanted to do. I have a reasonable level of experience using access to design databases but i am self taught on the whole.
A full description of the task will take a long time but the crux of what i cannot find a solution to is this; I want to import all the excel files in a folder called "IMPORTS", each into their own new table in access, with the table name the same as the filename of the excel file from which they were imported. This is a temporary step in a process and once the tables have performed their purpose will be deleted.I will explain the process a bit more below for context.
The idea is i will be receiving up to a few thousand of these excel files, each will contain one column of up to a maximum of 200 numbers (whole integer). These numbers are the primary key for a table of courses in the database which i will then link to a personID in the database, using an update query before the table is to be deleted. Ideally this will be a one button update records code that runs for all the files, updates the records and then deletes the tables imported (leaving the excel files untouched for now, but once i have tested it i may add code to move the files to another folder). The excel files will all (hopefully if users follow the outlined procedure) be uniquely named and that name will be used to find their record in the database, i may have to add code to check that the user exists before updating records and ignore the delete table step if it doesn't, to allow me to add a new user.
At this stage i am trying to prove a concept rather than make the final code in one go but the one thing i have had little luck with is the main question above.
Any guidance would be much appreciated,
Graham