Microsoft Access allows for data to be intrinsically stored in a tabular format. Connecting Microsoft Excel to Access is straightforward, in the form of being able to upload a large dataset into Excel as a manual exercise.
However, through the use of Excel VBA – you are able to upload data into Access databases on the fly. This allows for new records and entries to be added to tables within a couple of seconds.
The benefits of such a process are almost instantaneous through the form of time-saving, and continues to increase almost linearly as the data set increases in size. A large Excel table, in the 10s of 1000s of rows can take several seconds to load, then several more seconds to save. A file containing 30,000 rows can take 5 seconds to save, 1 second to update and another 3 seconds to save and close again (tested on a Core i5 machine with 8GB of RAM).
Moving this dataset to Access, reduces the total process from 9 seconds – to under 1 (one) second.
This is due to the way SQL is processed; SQL is the language that is used to retrieve and insert data into tables. Instead of loading a table, adding a new record to the bottom and then closing it again, an SQL statement can simply slot a record into a table – without fully loading the table.
VBA can be used to create these statements in a dynamic fashion, which identifies the tables, columns and values within those columns that need to be updated. After that, it can connect to the database, and execute the SQL statement – without loading anything.