First, you don't necessarily need to use VBA to bring data into Excel from a
database, whether from Access or another DBMS. Two easy ways are to use a
pivot table (Data, PivotTable and PivotChart Report, and specify External
Data Source in the wizard's first step; then, follow the prompts), or Data |
Import External Data | New Database Query. Some of these menu items will
differ according to the version of Excel you're using.
The pivot table approach is handy if you want to summarize information from
the database. The Import External Data approach is handy if you want to use
individual records. In either case, you can arrange for the data to
automatically refresh each time you open the workbook, and you don't
necessarily need to do any VBA coding.
If you for some reason need to use VBA to get the data into Excel, you'll
need to go to the Visual Basic Editor, open your VBA module, and use Tools |
References to establish a reference to a Microsoft DAO (Data Access Objects)
Object Library, or a Microsoft ActiveX Data Objects (ADO) Library. The
version available to you will depend of the version of Excel you're using.
It's up to you whether to use DAO or ADO. Since you're using Access, you
might want to use DAO, which is optimized for Excel-JET connections (Access
uses JET). But if you might switch to another database manager such as SQL
Server or MySQL, consider using ADO instead -- it's more broadly applicable,
and most people consider it easier to use than DAO anyway.
As to documentation, Microsoft's is both sparse and weak. Nothing new about
that. Two books that I like are:
Microsoft Access Developer's Guide to SQL Server, by Mary Chipman and Andy
Baron, SAMS, 2001. Despite the book's title, you'll find lots of examples of
how to use VBA code in Excel to bring in external data by means of VBA using
either ADO or DAO. Also, Mary Chipman is drop dead gorgeous.
Managing Data with Excel, by Conrad Carlberg, Que, 2004. Takes more of an
Excel perspective than the Chipman/Baron book, but also discusses both ADO
and DAO in the VBA context. But I am not gorgeous (my S.O. is, though).
