Hello,
I work in natural resources and manage survey data that we collect throughout our entire state. We have a rather large and complicated Access database and are making some changes to our data collections methods (switching over to iPads) that will affect how we enter the data into our existing access database. I basically now get an excel workbook with 2 tables containing all the data I would usually manually enter into our access database. This data is formatted and ready to be imported into our database. I get one of these workbooks per site sampled, so can get anywhere from 50-250 of these excel workbooks per season. These excel workbooks, with their 2 tables in each, contains data that I need to link/append/import into 3 existing tables in access.
I'm wondering what the best strategy and most efficient way of doing this would be. I've researched creating a macro, but then I see forms in excel that I could create to have them upload/link/append to the access tables. If there is a way to link an existing spreadsheet to an existing table I could combine all the data and just keep copying and pasting it. It seems like there is a lot of options ranging in difficulty that I could choose from to import large datasets into our existing access tables. I'm just looking for some advice on what is the best & most efficient way to accomplish this to get me started.
Thanks a bunch!
Jenny