Auto-Import XLS or XLSB workbooks with multiple sheets and append data to tables. (Access Tables and Excel Sheets = same name)

[Manerlao]

Dear MS Access Community!

I am actually quite new to this forum (a few months) and very new to MS Access (1 month or so).

Earlier this week, I was having trouble with Excel, and now I'm having trouble importing data into my MS Access Tables.
Please allow me to explain:

Structure and Background:

I have 30 MS Access Tables named (Tbl1, Tbl2, Tbl3, ... Tbl30) which correspond exactly to my sheet names within multiple XLS or XLSB workbooks (Tbl1, Tbl2, Tbl3, ... Tbl30).
Each workbook contains different datasets, but within corresponding headers.
I have around 190 workbooks with 30 sheets to import/append into MS Access Tables. Each sheet has the same name as my MS Acess tables as mentioned.
I have already ensured that the MS Access Table headers per table are corresponding to headers in my MS Excel Workbooks so the import runs smoothly.

My Progress:

I created one Macro to append for a single XLSB workbook located on my C:// drive and it works (Macro is shown in the image attached)
I created it using the "Design Tab" and it appends new data to each table from the corresponding worksheets in my XLS or XLSB workbooks.

HOWEVER, every time I run it I get an error message stating:

There is actually no error at all. It just doesn't want to import the data that is already in my table. Which is great!
This is what I already need, but I don't want this message to come up each time.

My Objective is 2-fold:

1. I would like to set a parameter as the filename extension so I can select different workbooks to import as and when needed to import.
2. I would like the import to only import the most recent data, not contained within the MS Access Tables
(This works for me quite good so far because I have already set my primary keys as 4 headers: for "Date, Time, Description1, Description2" since these will be unique each time as a combined group)
3. I don't want this message to come up each time.

Sorry, I'm quite new to Access, perhaps there is an easy fix?
If anyone can provide some guidance, this would be great! I spent my whole evening on youtube and forums trying to figure this out! I'll try get some sleep on the weekend.....(If I can!)

I look forward to hearing from the community! Thank you all and stay safe all.

Best regards,
Manerlao.

 

[Manerlao]

Edit: Written more clearly

Dear MS Access Community!

I am actually quite new to this forum (a few months) and very new to MS Access (1 month or so).

Earlier this week, I was having trouble with Excel, and now I'm having trouble importing data into my MS Access Tables.
Please allow me to explain:

Structure and Background:

I have 30 MS Access Tables named (Tbl1, Tbl2, Tbl3, ... Tbl30) which correspond exactly to my sheet names within multiple XLS or XLSB workbooks (Tbl1, Tbl2, Tbl3, ... Tbl30).
Each XLS or XLSB workbook contains different data, but within corresponding headers.
My headers in each sheet within the workbooks match the headers in each table in MS Access. I ensured this.

I have around 190 workbooks with 30 sheets to import/append into MS Access Tables. Each sheet has the same name as my MS Acess tables as mentioned.
I have already ensured that the MS Access Table headers per table are corresponding to headers in my MS Excel Workbooks so the import runs smoothly.

My Progress:

I created one Macro to append all my 30 MS Access tables but only for a single XLSB workbook located here: C:\Users\administrator\documents\DataBaseFilesAll\Workbook1.xlsb and it works (Macro is shown in the image attached)
I created it using the "Design Tab" and it appends new data to each table from the corresponding worksheets in my XLS or XLSB workbooks.
View attachment 16300

HOWEVER, every time I run it I get an error message stating:
View attachment 16299

There is actually no error at all. It just doesn't want to import the data that is already in my table. Which is great!
This is what I already need, but I don't want this message to come up each time.

My Primary Keys:
I have already set my primary keys as 4 headers: for "Date, Time, Description1, Description2" since these will be unique each time as a combined group
Sometimes the date and time can be the same but Description1 can be different, this is why I have set all 4 headings as my primary key. Otherwise, I get duplicated data. Is this ok? Is it correct?

My Objectives:

1. I would like to set a parameter as the filename extension so I can select different workbooks to import when I have to import the data.
2. I would like to import only the most recent data which is within the XLS or XLSB workbooks. Subsequently, I don't want to import the same data which is already contained within the MS Access Tables
3. I don't want this error message to come up each time.

Sorry, I'm quite new to Access, perhaps there is an easy fix? Perhaps you can provide additional guidance as to whether this logic/plan is correct?

If anyone can provide some guidance, this would be great! I spent my whole evening on YouTube and forums trying to figure this out! I'll try get some sleep on the weekend.....(If I can!)
For now, this is my mission.

I look forward to hearing from the community! Thank you all and stay safe all.

Best regards,
Manerlao.