Core Training for Microsoft Office Access 2003

Importing and Exporting Information

Importing Information from a Spreadsheet

1To open an existing database, click the File menu, and then click Open.
2To select the GardenCo database, in the Open dialog box, under My Documents, double-click Garden Company, and then double-click GardenCo.
3To acknowledge the security warning, in the Security Warning message box, click Open.
4To view the Customers table with no records, in the Database window, double-click Customers, verify that the table is empty, and then click the table's Close button.
5To begin importing data from a Microsoft Excel file, click the File menu, point to Get External Data, and then click Import.
6To continue importing, in the Import dialog box, click the Files of type list arrow, and then click Microsoft Excel.
7To open the Import Spreadsheet Wizard, in the Import dialog box, click Customers, and then click Import.
8To select the worksheet to be imported, in the Import Spreadsheet Wizard, note that Customers is selected in the Show Worksheets box, and then click Next.
9To accept the column headings of the Excel worksheet, in the Import Spreadsheet Wizard, click the First Row Contains Column Headings check box, and then click Next.
10To select the Access table into which you want to import the Excel data, in the Import Spreadsheet Wizard, click the In an Existing Table list arrow, click Customers in the list, and then click Next.
11To complete the importing, in the Import Spreadsheet Wizard, click Finish, and then in the Import Spreadsheet Wizard message box, click OK.
12To view the table and to confirm that Access has imported the worksheet data, double-click Customers.
13To close the Customers table, click the File menu, and then click Close.

Importing Information from a Text File

1To import information from a comma-delimited text file into the GardenCo database, click the File menu, point to Get External Data, and then click Import.
2To continue importing, in the Import dialog box, click the Files of type list arrow, drag the scroll bar down to the end of the list, and then click Text Files.
3To open the Import Text Wizard, click Employees, and then click Import.
4To select delimited text, in the Import Text Wizard, verify that the Delimited option is selected, and then click Next.
5To select the delimiter and field names, in the Import Text Wizard, notice that the Comma option is selected, click the First Row Contains Field Names check box, and then click Next.
6To select the target table in the GardenCo database, click the In an Existing Table list arrow, click Employees, and then click Next.
7To complete the importing of the delimited text file, in the Import Text Wizard, click Finish, and then, in the Import Text Wizard message box, click OK.
8To view the newly imported records, double-click Employees.
9To close the Employees table, on the title bar of the Employees : Table window, click the Close button.
10To import a fixed-width text file into the GardenCo database, click the File menu, point to Get External Data, and then click Import.
11Notice that in the Import dialog box, Text Files is displayed in the Files of type box. To open the Import Text Wizard, click Suppliers, and then click Import.
12To continue importing text in the fixed-width format, in the Import Text Wizard, note that the fixed-width option is selected, and then click Next.
13To view the phone numbers starting in column 331, in the Import Text Wizard, drag the horizontal scroll bar to the right until column 331 is displayed.
14To delete the break line at column 337, double-click the break line.
15To delete the break line in the fax numbers in column 361, double-click the break line at column 367, and then click Next.
16To select the target table in Access, click the In an Existing Table list arrow, click Suppliers, and then click Next.
17To complete importing the text file, in the Import Text Wizard, click Finish, and then in the Import Text Wizard message box, click OK.
18To view the newly imported records, double-click Suppliers.
19To close the table, on the title bar of the Suppliers : Table window, click the Close button.

Importing Information from a Database

1To begin importing from another Access database, click the File menu, point to Get External Data, and then click Import.
2To continue importing, in the Import dialog box, click the Files of type list arrow, drag the scroll box up until you see Microsoft Office Access, and then click Microsoft Office Access.
3To select the database to be imported, in the Import dialog box, under My Documents, double-click Garden Company, click Products, and then click Import.
4To expand the Import Objects dialog box and display the import options, click the Options button.
5To select the Categories and Products tables to be imported, in the Import Objects dialog box, click Select All, and then click OK.
6To view the newly imported Categories table, in the Tables object, double-click Categories.
7To close the Categories table, on the title bar of the table window, click the Close button.
8To view the newly imported Products table, in the Tables object, double-click Products.
9To close the Products table, on the title bar of the table window, click the Close button.
10To import information from a dBASE file into the Shippers table in the GardenCo database, click the File menu, point to Get External Data, and then click Import.
11To continue importing, in the Import dialog box, click the Files of type list arrow, and then click dBASE 5.
12To select the dBASE file to import from, click Shippers, and then click Import.
13To complete the importing, in the Microsoft Office Access message box confirming the successful import of the data, click OK, and then, to close the Import dialog box, click the Close button.
14To view the newly imported Shippers table, in the Tables object, in the GardenCo Database window, double-click Shippers.
15To close the Shippers table, on the title bar of the table window, click the Close button.

Importing Information from HTML and XML Documents

1To import the information from an HTML document, click the File menu, point to Get External Data, and then click Import.
2To continue, in the Import dialog box, click the Files of type arrow, click the scroll down arrow, and then click HTML Documents.
3To import the HTML file, in the Import dialog box, click NewCust, and then click Import.
4To display the next page of the wizard, click Next.
5To continue, click the In an Existing Table list arrow, click Customers, and then click Next.
6To import the new customers data into the Customers table, click Finish.
7To close the Import HTML Wizard message box, click OK, and then to view the Customers table, in the GardenCo Database window, double-click Customers.
8To close the Customers table, click its Close button.
9To import information in an XML format, on the File menu, point to Get External Data, and then click Import.
10In the Import dialog box, click the Files of type list arrow, drag the list scroll box to the end of the list, and then click XML.
11To continue, in the Import dialog box, click Orders, and then click Import.
12To display the import options, in the Import XML dialog box, click the Options button.
13To accept the default option to import both the structure and data, notice that the Structure and Data option is selected in the Import Options area, and then click OK.
14To close the Microsoft Office Access message box, click OK.
15To import the Order Details XML file, click the File menu, point to Get External Data, and then click Import.
16To continue, click Order Details, and then click Import.
17To accept the default structure and data, in the Import XML dialog box, click Order Details, and then click OK.
18To close the message box, click OK, and then to view the Order Details table, in the GardenCo : Database window, double-click Order Details.
19To close the Orders Details table, click its Close button.
20To view the Orders table in the GardenCo database, double-click Orders.
21To switch to Design view, click the View menu, and then click Design View.
22To change the data type for the OrderDate field, click in the Data Type column for OrderDate, click the list arrow, and then click Date/Time.
23To change the data type for ShippedDate, click in the Data Type column for ShippedDate, click the list arrow, and then click Date/Time.
24To save your changes, on the toolbar, click the Save button.
25To return to Datasheet view, on the View menu, click Datasheet View.
26To close the Orders table, on the File menu, click Close.

Exporting Information to Other Programs

1To open the Suppliers table, in the GardenCo Database window, double-click Suppliers.
2On the File menu, click Export, and then, in the Export Table ‘Suppliers’ To dialog box, click the Save as type arrow.
3To select the file type, in the list, click below the scroll box on the scroll bar, click Microsoft Excel 97-2003, and then click Export All.
4To view the new worksheet in Excel format, click Start, click My Documents, and in the My Documents window, double-click Garden Company, and then double-click Suppliers.
5To close the Suppliers worksheet, click the Close button.
6To return to the GardenCo Database window, click the GardenCo : Database taskbar button.
7To export the Suppliers table as XML, verify that the Suppliers table is selected, and then, on the File menu, click Export.
8To continue, in the Export Table ‘Suppliers’ To dialog box, click the Save as type arrow, drag the scroll box down to the end of the list, click XML, and then click Export All.
9To select both Data (XML) and the Schema (XSD) of the data, verify that both the Data and Schema of the data check boxes are selected in the Export XML message box, and then click OK.
10To view the exported Suppliers table as .xml and .xsd files, click the Garden Company taskbar button.
11To view the .xml file in the browser, double-click Suppliers.xml.
12To close the .xml file, click the File menu, and then click Close.
13To close the My Documents window, on the title bar, click the Close button.

Sharing Information

1To begin linking a table from another Access database, click the File menu, point to Get External Data, and then click Link Tables.
2To continue linking, in the Link dialog box, under My Documents, double-click Garden Company, click LinkDatabase, and then click Link.
3To select the table to link, on the Tables tab in the Link Tables dialog box, click Shippers, and then click OK.
4To view the new linked table, double-click Shippers1.
5To close the table, on the title bar of the table window, click the Close button.
6To begin linking an Excel spreadsheet to the GardenCo database, click the File menu, point to Get External Data, and then click Link Tables.
7To continue linking, in the Link dialog box, click the Files of type list arrow, and then click Microsoft Excel.
8To open the Link Spreadsheet Wizard, in the Link dialog box, under Garden Company, click LinkWorksheet, and then click Link.
9To select the range of the worksheet to be linked, notice that Customers is selected in the Show Worksheets box, and then click Show Named Ranges.
10To continue selecting the range, in the Show Named Ranges box, click SpecialCustomers, and then click Next.
11To skip defining the column headings, verify that the First Row Contains Column Headings check box is cleared, and then click Next.
12To accept the default table name and to complete the linking, in the Link Spreadsheet Wizard, click Finish, and then click OK.
13To copy selected records in a table to an Excel worksheet, in the GardenCo Database window, double-click Customers.
14To select the rows to be copied, drag to select rows 1 through 6, as indicated, and then, on the toolbar, click the Copy button.
15To open a blank worksheet in Microsoft Excel, click Start, point to All Programs, point to Microsoft Office, and then click Microsoft Office Excel 2003.
16To paste the copied rows into the worksheet, on the Sheet1 tab, notice that cell A1 is selected, and then click the Paste button.
17To close Excel, click the File menu, click Exit, and then click Yes in the Microsoft Excel message box.
18To insert the file name, in the Save As dialog box, for the purposes of this exercise, press SPACEBAR, wait as the text is typed for you, and then click Save.
19To open a blank document in Microsoft Word, click Start, point to All Programs, point to Microsoft Office, and then click Microsoft Office Word 2003.
20To paste the copied rows into the blank document, click the Paste button.
21To close Microsoft Office Word, click the File menu, click Exit, and then click Yes in the Microsoft Office Word warning message box.
22To insert the file name, in the Save As dialog box, press SPACEBAR, wait as the text is typed for you, and then click Save.
23To close the Customers table, on the title bar of the table window, click the Close button, and then, to close the GardenCo database, on the Database window title bar, click the Close button.