Efficient Excel to Access uploads

K

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
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top