Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Chapter 21

Using Access as a back end to enhance multiuser access to data

In this chapter, you will:

• Understand the difference between ADO and DAO

• Get to know the tools of ADO

• Add a record to a database

• Retrieve records from a database

• Update an existing record

• Delete records via ADO

• Summarize records via ADO

• Get to know other utilities via ADO

• Examine SQL Server examples

The example near the end of Chapter 19, “Text file processing,” proposes a method for storing 660,601,620 records in an Excel worksheet. At some point, you need to admit that even though Excel is the greatest product in the world, there is a time to move to Access and take advantage of Access multidimensional database (MDB) files.

Even before you have more than 1 million ...