ADODB is awesome. That is going to give you the most control, but for larger sheets it could be slow since it uses a cursor. However I could be doing my ADODB wrong
On your secondary question, yes it is possible to normalize your data to one sheet using VBA to grab data from many sheets. This is pretty object heavy and you need to open the files from within Access to do so. On your main data sheet I would make a control sheet, where you list all the filenames/paths of the worksheets your users are updating. Then do a for/each for those.
Then you have your data in one spot.
If you can get it to one sheet in a nice table/header format I would recomend using something like..
This will import a big chunk of data without having to go row by row. I have a report that has a 9k line excel feeder sheet, ADODB takes about a minute and a half to update. This takes about 10 seconds. I would say if you have a smaller recordset use ADODB, it's an amazing skill to learn for the future but a block import might be better for a larger recordset.
Bookmarks