My company currently has a script written in VBA in MS Access that fetches data from a series of excel spreadsheets and updates a table on a SQL server. As it stands, the VBA script imports the rows individually, using a For-loop to loop through the rows and building / executing a series of INSERT commands on the SQL server. This is extremely slow, and problematic since we need to update the tables regularly.
My question is, how can we bulk-import an entire excel spreadsheet into a table on the SQL server using VBA in access?
Is there a command in SQL that will do this automatically? And if so, will it be any quicker than updating it on a row-by-row basis? I have checked out various articles online, but have so far found a workable solution.
Any advice would be appreciated, thanks.