Excel is a great application for analysing data. It is not so good at storing and collating large and complex data sets, something for which Access is much better suited. By using the two together you can harness the strengths of both applications, and build some powerful tools for your business. There are a number of ways to transfer data between the two, including linking Excel worksheets into Access and using the Excel Query tool to retrieve data. Both of these options have limitations. The main one is that neither allows 2-way traffic; for example, if you pull data into Excel using the Query tool, you can't change the data in Access, and you can't edit linked Excel data with Access since Service Pack 2 of Office XP.
If you use code instead, it is possible to transfer one or many records between the two applications, and to update Access with changes made in Excel. There are two main object models for communicating with Access; DAO, which is optimised for working with the Jet database engine, and the newer and more generic ADO. They are quite different, so because ADO can also be used for transferring data to enterprise databases like SQL Server and Oracle, this tutorial will use ADO. There are a number of parts to the tutorial, all of which use the same Excel download file. They are:
I suggest that you download the Excel sample file and run through the tutorial in sequence to build and check the functionality. You can go back and adapt any of the sections for later use.