Efficient Excel to Access uploads

[Kamil]

Hi.
I have an Access database for financial reports (actuals,
budgets, ...).
The data is uploaded from excel sheets (all validations are done in
excel sheets). One report contains about 10 sheets of data to be
uploaded to different access tables. It's about 300 rows and 50
columns in each sheet.

What is the most effective method to upload that amounts of data?
These sheets are not ready to be directly uploaded to Access, I need
to select specific columns to upload).
Now I'm just creating INSERT INTO statement for each cell and
executing it (using ADO).
I'm wondering if there is a better method, like working on recordsets
or temporary excel sheets (uploading whole table at once).
The current solution is too slow.
In addition - when someone else tries to upload some other data at the
same time, both uploads are frozen for several minutes... (the
database is saved on the network share).

Do you have any advice for such case?

Thanks
Best regards,
Kamil

 

[AB]

If the format (file, sheet, column names) don't change from month to
month - i'd suggest linking those sheets into the Access db and then
executing there in Access append queries (can also be automated) that
would select the necessary columns and write those into the
appropriate tables.
Sure you can also connect to the Access tables as recordsets and keep
looping through excel sheets and AddNew to the recordset.
In my opinion, if the formats are static - linking the tables into
access is more efficient approach.