Integrating Access and Excel
VBA Level 6

Course Outline

DURATION	1 Day
PREREQUISITES	This course is for Advanced Excel users who have experience in VBA programming within Excel.  You need to have the equivalent knowledge of Access Levels 1 & 2 and Excel VBA Levels 1 & 2.
DESCRIPTION	Excel users. Access users. You′re probably among the majority, living in one camp or the other but rarely crossing between the two. Yet Microsoft designed these applications to work together. On this course, you′ll discover how Access benefits from Excel′s flexible presentation layer and versatile analysis capabilities, while Access′s relational database structure and robust querying tools enhance Excel. Once you learn to make the team work, you′ll find that your team′s productivity is the real winner.
COURSE DATES	Click here to request an alternate date Or call us on 020 313 73958 from 8:00 to 22:00 Monday to Sunday. Evening Wed, 1-Sep-10 and Thu, 2-Sep-10 18:30 - 21:30 £200 Weekday Mon, 27-Sep-10 09:30 - 16:30 £200 Weekday Fri, 8-Oct-10 09:30 - 16:30 £200 Weekend Sat, 23-Oct-10 10:00 - 16:30 £200 Weekday Mon, 8-Nov-10 09:30 - 16:30 £200 Evening Mon, 22-Nov-10 and Tue, 23-Nov-10 18:30 - 21:30 £200 Weekday Wed, 8-Dec-10 09:30 - 16:30 £200 Evening Wed, 29-Dec-10 and Thu, 30-Dec-10 18:30 - 21:30 £200 Weekday Tue, 11-Jan-11 09:30 - 16:30 £200 Evening Mon, 24-Jan-11 and Tue, 25-Jan-11 18:30 - 21:30 £200 Any One-to-One Tuition Any Date - Fully Customisable 09:30 - 16:30 £400

Manually INTEGRATING EXCEL AND ACCESS

• Getting Excel data into Access

  • Linking

  • Importing

• Getting Access data into Excel

  • Using “Get External Data”

  • Drag-and-Drop

  • Exporting data from Access

  • Grabbing Access data straight into a Pivot Table

  • Using Microsoft Query

  • Limitations of Microsoft Query

• Using Excel data within Access to utilize Access reporting functionality

Using VBA to Move data between Excel and Access

• Understanding ADO Fundamentals

  • The Connection String

  • Declaring a Recordset

  • Return Read Only data from a Table or Query

  • Return Updateable Data from a Table or Query

  • Writing an ADO Procedure

  • Referencing the ADO Object Library

  • Writing the Code

  • Using the Code

• Understanding SQL Fundamentals

  • The SELECT Statement

  • Selecting All Columns

  • The WHERE Clause

  • Expanding Your Search with the Like Operator

  • Grouping and Aggregating with the GROUP BY Clause

  • The HAVING Clause

  • Creating Aliases with the AS Clause

  • Setting Sort Order with the ORDER BY Clause

  • SELECT TOP and SELECT and SELECT TOP PERCENT

  • Writing your first ADO/SQL Data Extract

  • Using Numerical Criteria

  • Using Textual Criteria

  • Using Date Criteria

  • Using Multiple Criteria

  • Using the LIKE Operator

• Common Scenarios where VBA can Help

  • Query Data from an Excel Workbook

  • Append Records to an Existing Excel Table

  • Querying Text Files

Exploring Excel and Access Automation

• Understanding the Concept of Binding

  • Early Binding

  • Late Binding

• Automating Access from Excel

  • Setting the Required References

  • Running an Access Query from Excel

  • Running Access Parameter Queries from Excel

  • Running an Access Macro from Excel

  • Opening an Access Report from Excel

  • Opening an Access Form from Excel

  • Compacting an Access Database from Excel