1	Day 1: Access and Excel Interoperability Access/SQL Server/VB.NET Development Seminar Presented by: Rick Dobson
2	Roadmap for Access and Excel Interoperability Using the Excel/Access Menu command Using ADO Using ISAM drivers Using automation
3	Using the Menu to Copy an Access Table to an Excel Worksheet From a new Excel sessions, choose File, Open Change Look in to folder with .mdb containing data you want Change File type to .mdb, .mde Select a file from which to import Click Open Select a table or query Click OK Edit worksheet tab – for example, change from file name to table name Save the Workbook
4	Using the Menu to Copy an Excel Worksheet to an Access Table From an Access database, choose File, Get External Data, and either Import, or Link Tables Set the Look in box and the Files of type box for the correct folder and Excel Select the Workbook Click Link or Import Choose the worsheet or named range Specify the first row as containing column headers, if appropriate Assign a name to the table Click Finish and OK
5	Editing Linked Data in Access and Excel Open Access to AccessExcelInterop.mdb Open the linked Customers table – notice link points at an Excel workbook Edit CustomerID ALFKI to ALFKIx Close Access Open Excel with AccessExcelInterop.xls Notice how new value transfers to workbook file Remove trailing x for ALFKI; then close and save workbook Open Access to AccessExcelInterop.mdb and notice how it shows edit to original ALFKI value Tip: It is best to perform edits through the user interface with either the Access or Excel file closed
6	Three Ways to Programmatically Connect Access and Excel ADO for Connection objects and Recordset objects ISAM driver for reading Excel data in Access Automation for controlling one application, such as Excel, from another, such as Access
7	Programmatically Copy Access Data from Excel via ADO You can populate an Excel worksheet programmatically with ADO Before programming a solution make sure the Workbook has a reference to the Microsoft ActiveX Data Objects Library Create and instantiate Connection and Recordset objects based on the Access data source Optionally, add a new worksheet for the Access data Invoke the CopyFromRecordset method to transfer the contents on a Recordset object to a range in a worksheet Clean up the ADO objects
8	Demo: The RetrieveAccessData Procedure from AccessExcelInterop.xls Delete DatafromAccess worksheet if it exists (right-click and choose Delete) Choose Tools, Macro, Macros Highlight RetrieveAccessData Click Edit Choose Tools, Reference Notice reference to Microsoft ActiveX Data Objects 2.7 Library If you have an earlier version on your computer Clear the check box and click OK Open the References dialog box and check the most recent version Notice syntax for Connection and Recordset objects in Excel is identical to Access A Worksheet is an object, just like a Connection, that you can instantiate The CopyFromRecordset method fills a worksheet to the right and down from a starting point
9	Demo: Enabling a Button Click to Invoke the RetrieveAccessData Procedure Open the Toolbox from the Visual Basic Toolbar Copy a Command Button control to Sheet1 in the AccessExcelInterop workbook From the Properties window edit The Name property to cmdYourLoadData, and The Caption property to Load Data Double-click the button, and insert RetrieveAccessData in the click event procedure shell Exit Design mode from the Toolbox Click the button
10	ISAM Drivers for Getting Excel Data into Access ISAM drivers are appropriate non-Jet, non-ODBC data sources, such as Excel Each type of data can have its own specific ISAM driver The connection string for an Excel ISAM driver includes three parameters delimited by semi-colons The Jet driver for getting data into Access The Data Source pointing at an Excel workbook file The Extended Properties specifying an Excel 8.0 file type (for Excel 97, 2000, and 2002) An Excel ISAM driver in an Access database file can point at a named range in Excel as the source for a recordset Create the range By selecting a range of cells, and then Invoking the Insert , Name, Define command
11	Use an Unbound Form to Navigate Imported Excel Data ISAM-retrieved Excel data does not bind to Access forms or controls You can use an unbound form to browse imported data Navigate the recordset rows with the Imported data from button controls on an Access form Assign the current recordset row to the form fields, such as text box controls
12	Demo: Connect2XL in Module1 of AccessExcelInterop.mdb Run Connect2XL; notice it displays the last several rows from the customers named range in the Customers worksheet in the AccessExcelInterop.xls workbook Examine the Open method for cnn1 to view the connection string for an ISAM driver Notice that the Open method for rst1 specifies the customers named range in the AccessExcelInterop.xls workbook A simple Do loop can traverse the Excel data in the Recordset object
13	Demo: frmCustomers Demonstrates Unbound Processing Open the frmCustomers form in the AccessExcelInterop.mdb file Notice the previous button (<) is disabled Click the next button (>) The record advances and The previous button becomes enabled Click the previous button and watch the original row appear and the previous button become disabled again The next button disables when a user navigates to the last record
14	Demo: frmCustomers Demonstrates Unbound Processing (Cont’d) Code behind the form consists of three procedures The Form_Open event procedure Initially populates the recordset used to assign form field values and inserts the first row in the form’s text box controls Manages the appearance of the previous button The cmdNext_Click procedure Advances the recordset row Populates the text box controls with new values Resets the appearance of button controls, if appropriate The cmdPrevious_Click procedure works the same way as the cmdNext_Click procedure for moving backwards through the recordset rows
15	Using Automation Automation lets Access (or Excel) run another application This running can involve Manipulating the object model for one application from another Invoking a procedure in one application from another Automation always requires a reference for the object model of the controlled application in the controlling application The controlling application must Open the controlled application Perform some task in the controlled application Typically close the controlled application
16	Demo: The RunRetrieveAccessDataInXL Procedure in the AccessExcelInterop.mdb The RunRetrieveAccessDataInXL procedure does four tasks; it Toggles the ProductName column value for the ProductID row with a value of 1 in the Products tables Opens the AccessInterop.xls workbook Runs the RetrieveAccessData procedure in the workbook to import the edited Access data Closes the Excel application Run the RunRetrieveAccessDataInXL procedure twice After the first run, check the workbook Look again after the second run and notice The Product name for ProductID 1 changes (from Chai to Chaix or the reverse)