1.1. Communications Between Excel and Access

There are several ways to exchange data between Access and Excel. Automation (formerly called OLE Automation) is a method of communication that gives you access to another application's objects . Using Automation, you can actually take control of the other application and send and retrieve data, set properties, run methods, and perform many other tasks. This book will explore in depth how automation can be used to allow integration between Access and Excel.

One of the original ways to communicate between Windows programs was dynamic data exchange (DDE) . While this can be useful, I do not recommend it between Office applications. It is sometimes necessary when you are communicating with a program that does not have a very useful object model. However, the object models for all of the programs in the Office suite allow you so much flexibility that I cannot imagine a situation when DDE would be preferable to Automation with VBA.

The other methods of communication treat Access or Excel simply as a data source and allow query access. This is accomplished through ActiveX Data Objects (ADO) or Data Access Objects (DAO). In addition to these programming methods, both Access and Excel offer data access methods from the standard user interface that work well for simple tasks.