Home

Data Import and Export

Microsoft Access Data Import/Export

Introduction

Microsoft Access supports various scenarios of importing and exporting data, to and from MS Access databases, to and from text files, to and from spreadsheets, etc. Before importing, you should create a database that will receive the records.

The primary way to import or export is through the External Data property page of the Ribbon. You can also right-click any object in the Navigation Pane, position the mouse on either Import or Export and click the desired option.

Practical Learning: Introducing Data Import/Export

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. Set the file name as FunDS2
  4. Click Create
  5. On the Ribbon, click File and click Options
  6. In the left list, click Current Database
  7. In the right list, click Overlapping Windows
  8. Click OK
  9. Read the message box and click OK
  10. On the default table, double-click ID to put into edit mode, press F2 and press Home, type SoldItem (to get SoldItemID) and press Enter
  11. In the Views section of the Ribbon, click View (or Design View)
  12. Set the name as SoldItems and click OK
  13. Complete the table with the following fields (change only the indicated parts):
     
    Field Name Data Type Field Size Format Caption
    SoldItemID     Sold Item ID
    ReceiptNumber Number     Receipt #
    ItemNumber Number     Item #
    Manufacturer   40    
    Category   25    
    SubCategory   25   Sub-Category
    ItemName   100   Item Name
    ItemSize   20   Size
    PurchasePrice Number Double Fixed Purchase Price
  14. Close the table
  15. When asked whether you want to save, click Yes
  16. To create a new table, on the Ribbon, click Create and, in the Tables section, click Table Design
  17. Type ReceiptNumber and press Tab
  18. Right-click ReceiptNumber and click Primary Key
  19. Create the fields as follows:
     
    Field Name Data Type Field Size Format Caption
    ReceiptNumber Number Long Integer   Receipt #
    EmployeeNumber Number Long Integer   Employee #
    ShoppingDate Date/Time   Long Date Shopping Date
    ShoppingTime Date/Time   Long Time Shopping Time
    SalesTotal Number Double Fixed Sales Total
    AmountTendered Number Double Fixed Amount Tendered
  20. Close the table
  21. When asked whether you want to save, click Yes
  22. Set the name of the table as ShoppingSessions and click OK
  23. On the Ribbon, click File and click New
  24. Click Blank Desktop Database
  25. Set the file name as Computer Training Center
  26. Click Create
  27. On the Ribbon, click File and click Options
  28. In the left list, click Current Database
  29. In the right list, click Overlapping Windows
  30. Click OK
  31. Read the message box and click OK

Importing a Microsoft Access Database

The simplest type of data to import into a Microsoft Access database is another MS Access database. To import a Microsoft Access database:

  • If the Navigation Pane contains at least one table, right-click a table. Position the mouse on Import, and click Access Database

Importing a Microsoft Access Database

  • In the Import & Link section of the Ribbon, click the Access button Access

In the dialog box, if you know the path to the database including its name, type it. If not, click the Browse button to select the source database. Once you have specified the database, click OK. The Import Objects dialog box will display. The objects are organized by categories. Select the desired objects. Besides the objects, you can import or ignore such aspects as the relationships and other Navigation Pane options. To decide, click the Options button and select the desired options:

Import Objects

Once you are ready, click OK. If the objects in the database were valid (and they should be), you shouldn't have any problem.

Practical Learning: Importing a Microsoft Access Database

  1. On the Ribbon, click External Data
  2. In the Import & Link section of the Ribbon, click the Access button Access
  3. In the dialog box, click the Browse button
  4. From the resources that accompany these lessons, select CTC and click Open
  5. On the dialog box, click OK
  6. In the Tables property page, click Select All

    Importing a Microsoft Access Database

  7. Click the Forms tab and click Select All
  8. Click OK
  9. When the objects have been imported, on the dialog box, click Close

Exporting a Microsoft Access Database

There are various ways you can export the records of a Microsoft Access database. One way is to formally export a table.

To export an object:

  • In the Navigation Pane, right-click the table that contains the records, position the mouse on export, and click Access
  • In the Navigation Pane, click the table that contains the records to select it. In the Export section of the Ribbon, click the Access button

In the first page of the wizard, if you know the path to the database including its name, type it or click the Browse button to select the target database. Once you have specified the database, click OK. This would display the Export dialog box. It allows you to specify the name of the object that will receive the records or the object that would be created. You can also specify what would be exported: only the structure of the table or both the structure and the records:

Exporting a Microsoft Access Object

The above technique allows you to export one object, or one object at a time. If you want to export the whole database, on the Ribbon, click File and click Save As:

Save a Database

This window allows you to select the type of format the new database should have. The window provides eight options you can use. To use an option, either click it and click Save As or double-click it.

If you want to save the database in the current Access 2007-2016 format, select Access Database. This would open the Save As dialog box. Specify the name and location of the new database, and click Save.

If you want to create a Microsoft Access 97-2000 database, click Access 2000 Database. This would create a file with .mdb extension. These are databases compatible with Microsoft Access 97 and Microsoft Office Access 2000. There is nothing bad or wrong with those versions of databases and they are still valuable today. They just don't support some features such expressions, attachments, etc on a table:

Microsoft Access 97-2000 Compatible Database

In the MDB database versions, expressions cannot be created on tables but they can be created on other types of objects.

Microsoft Access and Spreadsheets

Importing a Worksheet

There are various ways you can import a spreadsheet into a Microsoft Access database. The spreadsheet can come from any application, including Microsoft Excel, Google Sheets, CSV, etc.

If you are planning to import a Microsoft Excel worksheet or a Google Sheet, you should first prepare it. One way is to make sure that the whole spreadsheet is organized as a single table. In this case, the top side of the spreadsheet should contain the categories that can be used/considered as column headers. No non-organized text (text that is not considered as belonging to the table) should display above the headers. The values can then display under each header. Here is an example:

When creating the spreadsheet, you may have created a list anywhere on the document, without a title, and only the list of values. In this case, the spreadsheet is ready. When you import such a spreadsheet, Microsoft Access would be able to figure out where the list starts and where it ends. Microsoft Access can even identify the column names and their records (where the records start and where they end)

No non-organized text (text that is not considered as belonging to the table) should display below the records).

Once the document is ready, to import it:

  • If the Navigation Pane contains at least one table, right-click a table in the Navigation Pane, position the mouse on Import, and click Excel
  • In the Import & Link section of the Ribbon, click the Excel button Excel

In the dialog box, specify the path and name of the spreadsheet and click OK. This would start a wizard. In the first page, select the name of the spreadsheet that contains the data.

Practical Learning: Importing a Microsoft Excel Spreadsheet

  1. On the Ribbon, click File and click Open
  2. In the list of files, click FunDS2
  3. On the Ribbon, click External Data
  4. In the Import & Link section, click the Excel button Excel
  5. Click the Browse button
  6. From the resources that accompany these lessons, select Fun Department Store and click Open

    Get Exernal Data - Excel Spreadsheet

  7. Click OK

    Importing a Microsoft Excel Spreadsheet

  8. In the first page of the wizard, make sure Sheet1 is selected and click Next. A message box may display. Read it and click OK

    Importing a Microsoft Excel Spreadsheet

  9. In the second page of the wizard, make sure First Row Contains Column Headings is checked and click Next
  10. In the third page of the wizard, as the ItemNumber column is selected, click the arrow of the Data Type combo box and select Long Integer

    Importing a Microsoft Excel Spreadsheet

  11. Click DateInStore or any cell under it and make sure its Data Type is Date With Time
  12. Click UnitPrice and make sure its Data Type is set to Double
  13. Click Next
  14. Click the arrow of the combo box and select ItemNumber

    Importing a Microsoft Excel Spreadsheet

  15. Click Next
  16. Replace the Import to Table name to StoreItems

    Importing a Microsoft Excel Spreadsheet

  17. Click Finish
  18. On the dialog box, click Close
  19. In the Navigation Pane, right-click the StoreItems table and click Design View
  20. Change the following characteristics of the fields:
     
    Field Name Field Size Format Caption
    ItemNumber     Item #
    DateInStore     Date in Store
    Manufacturer 40    
    Category 25    
    SubCategory 25   Sub-Category
    ItemName 100   Item Name
    ItemSize 20   Size
    UnitPrice Double Fixed Unit Price
  21. Close the table
  22. When asked whether you want to save, click Yes
  23. You will also receive a message box indicating that some data may be lost. Read it and click Yes

Importing a Named Spreadsheet

If the spreadsheet contains a mix of organized and non-organized sections or groups, you should create names for the necessary groups of records.

Practical Learning: Importing a Microsoft Excel Spreadsheet

  1. Start Microsoft Excel
  2. From the resources that accompany these lessons, open the Fun Department Store workbook
  3. In the bottom side, click Sheet2
  4. Click Employee # and type EmpolyeeNumber
  5. Replace First Name with FirstName
  6. Replace First Name with LastName
  7. Select the cells from EmployeeNumber to the last Sales Associates

    You may have created a spreadsheet that includes sections other than the list you want to use in your database

  8. On the Ribbon, click Formulas
  9. In the Defined Names section, click Define Name
  10. Replace the suggested Name with Employees

    If you create a spreadsheet in Microsoft Excel, Corel Paradox, or Lotus 1-2-3, and if the spreadsheet contains a mix of the desired list and other items, you can create a name.

  11. Click OK
  12. Close Microsoft Excel
  13. When asked whether you want to save, click Save and return to Microsoft Access
  14. On the Ribbon, click External Data if necessary.
    In the Import & Link section, click the Excel button Excel
  15. Click the Browse button
  16. Select the Fun Department Store spreadsheet you changed above and click Open
  17. On the Get External Data dialog box, click OK
  18. In the first page of the wizard, click the Show Named Ranges radio button

    Importing a Microsoft Excel Spreadsheet

  19. Make sure Employees is selected and click Next
  20. On the second page of the wizard, click First Row Contains Column Headings and click Next
  21. In the third page of the wizard, as the EmployeeNumber column is selected, click the arrow of the Data Type combo box and select Long Integer

    Importing a Microsoft Excel Spreadsheet

  22. Click Next
  23. Click the arrow of the combo box and select EmployeeNumber

    Importing a Microsoft Excel Spreadsheet

  24. Click Next

    Importing a Microsoft Excel Spreadsheet

  25. Accept the name of the table as Employees and click Finish
  26. On the dialog box, click Close
  27. In the Navigation Pane, right-click the Employees table and click Design View
  28. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size Expression Caption
    EmployeeNumber Number     Employee #
    FirstName   20   First Name
    LastName   20   Last Name
    EmployeeName     [FirstName] & " " & [LastName] Employee Name
    Title   100    
  29. Close the table
  30. When asked whether you want to save, click Yes
  31. You will also receive a message box that some data may be lost. Read it and click Yes

Copying From a Spreadsheet

Instead of manually importing a spreadsheet, you can select the values in a spreadsheet, copy them, and paste them in a table in Microsoft Access.

Practical Learning: Copying From a Spreadsheet

  1. Start Microsoft Excel and, in the list of files, click Fun Department Store
  2. Click Sheet3
  3. Click cell B5
  4. Press and hold Shift
  5. Click Cell G64
  6. Release Shift
  7. On the Ribbon of Microsoft Excel, click Home
  8. In the Clipboard section, click Copy
  9. In Microsoft Access, in the Navigation Pane, double-click the ShoppingSessions table
  10. Right-click the button at the intersection of the column headers and the row headers
  11. Click Paste
  12. Read the Microsoft Access message box and click Yes
  13. Return to Microsoft Excel in Sheet3
  14. Click cell I5
  15. Press and hold Shift
  16. Click cell P165
  17. Release Shift
  18. On the Ribbon, click Home and click Copy
  19. Return to Microsoft Access and double-click the SoldItems table in the Navigation Pane
  20. On the table, click Receipt #
  21. Press and hold Shift
  22. Click Unit Price
  23. Release Shift
  24. Press Ctrl + V to paste
  25. Read the message box and click Yes
  26. Close both tables
  27. In the Navigation Pane, right-click any of the tables, position the mouse on Import, and click Access Database
  28. Click Browse
  29. Locate and select the FunDS1 database from the previous lessons (otherwise, from the resources that accompany these lessons, select FunDS2) and click Open
  30. On the dialog box, click OK
  31. On the Import Objects dialog box, click Forms
  32. Click Select All
  33. Click OK
  34. When the forms have been imported, on the dialog box, click Close

Exporting to a Microsoft Excel Spreadsheet

to transfer data from Microsoft Access to a spreadsheet involves copying and pasting. To start, in Microsoft Access, you can open a table in Datasheet View then select one, some, or all records. After selecting the record(s), you can copy them, open Microsoft Excel, click the cell that would host the top-left value, and paste.

Another technique consists of exporting, and you can do it without first opening the table:

  • Before exporting a table, in the Navigation Pane, click the table. Then, on the Ribbon, click External Data. In the Export section, click the Excel button Excel
  • In the Navigation Pane, right-click the desired table, position the mouse on Export, and click Excel

This would open the Export - Excel Spreadsheet dialog box with the path where the file will be saved. The default folder is the My Documents:

Practical Learning: Exporting Data as a Spreadsheet

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Computer Training Center
  3. In the Navigation Pane, right-click the CourseSchedules table, position the mouse on Export, and click Excel
  4. Click the Browse button and select the (My) Documents folder
  5. Click OK
  6. On the dialog box, click Close

Microsoft Access and Text Files

Importing a Text File

You can create a table using data from a text file. If you are creating the (text) file in Notepad, the delimitation of a field is usually done by pressing Tab after creating the fields' content. To import a text file in Microsoft Access:

  • In the Import section of the Ribbon, click the Text File button
  • If you had previously created a table that would receive the values, in the Navigation Pane, right-click that table, position the mouse on Import, and click Short Text File

Practical Learning: Importing a Text Document

  1. To start a new database, on the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the name to Monson University1
  4. Click Create
  5. On the Ribbon, click File and click Options
  6. In the left list, click Current Database
  7. In the right list, click Overlapping Windows
  8. Click OK
  9. On the Ribbon, click External Data and, in the Import & Link section, click Text File Text File
  10. In the Get External Data - Text File dialog box, click the Browse button
  11. Locate the resources that accompany these lessons and select the Departments.txt file
  12. Click Open
  13. On the dialog box, click OK
  14. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
  15. In the second page, accept that the delimiter be set to Tab.
    Click the First Row Contains Field Names check box

    Importing a Text Document

  16. Click Next
  17. In the third page of the wizard, click Next
  18. Click the arrow of the combo box and select DepartmentCode
  19. Click Next
  20. Accept the name of the table as Departments and click Finish
  21. When the records have been imported, click Close
  22. In the Navigation Pane, right-click the Departments table and click Design View
  23. Change the following characteristics of the fields:
     
    Field Name Field Size
    DepartmentCode 5
    DepartmentName 100
  24. Close the table
  25. When asked whether you want to save, click Yes
  26. You will also receive a message box that some data may be lost. Read it and click Yes
  27. On the Ribbon, click External Data and, in the Import & Link section, click Text File Text File
  28. In the Get External Data - Text File dialog box, click the Browse button
  29. From the resources that accompany these lessons, select the Employees.txt file, and click Open
  30. On the dialog box, click OK
  31. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
  32. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names

    Importing a Text Document

  33. Click Next
  34. In the third page of the wizard, as EmployeeNumber is selected, click the arrow of the Data Type combo box and select Short Text
  35. Click Next
  36. Click the arrow of the combo box and select EmployeeNumber
  37. Click Next
  38. Accept the name of the table as Employees and click Finish
  39. When the records have been imported, click Close
  40. In the Navigation Pane, right-click the Employees table and click Design View
  41. Change the following characteristics of the fields:
     
    Field Name Field Size Caption
    EmployeeNumber 10  
    FirstName 25 First Name
    MiddleName 25  
    LastName 25 Last Name
    DepartmentCode 5  
    Title 100  
  42. Close the table
  43. When asked whether you want to save, click Yes
  44. You will also receive a message box that some data may be lost. Read it and click Yes
  45. On the Ribbon, click External Data and, in the Import & Link section, click Text File Text File
  46. In the Get External Data - Text File dialog box, click the Browse button
  47. From the resources that accompany these lessons, select the Majors.txt file, and click Open
  48. On the dialog box, click OK
  49. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
  50. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names

    Importing a Text Document

  51. Click Next
  52. In the third page of the wizard, click Dean
  53. Click the arrow of the Data Type combo box and select Short Text
  54. Click Next
  55. Click the arrow of the combo box and select MajorID
  56. Click Next
  57. Accept the name of the table as Majors and click Finish
  58. When the records have been imported, click Close
  59. In the Navigation Pane, right-click the Employees table and click Design View
  60. Change the following characteristics of the fields:
     
    Field Name Field Size
    MajorID  
    Major 100
    Dean 10
  61. Close the table
  62. When asked whether you want to save, click Yes
  63. You will also receive a message box that some data may be lost. Read it and click Yes
  64. On the Ribbon, click External Data and, in the Import & Link section, click Text File Text File
  65. In the Get External Data - Text File dialog box, click the Browse button
  66. From the resources that accompany these lessons, select the Minors.txt file, and click Open
  67. On the dialog box, click OK
  68. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
  69. In the second page, accept that the delimiter be set to Tab and click First Row Contains Field Names
  70. Click Next
  71. In the third page of the wizard, click Next
  72. Click the arrow of the combo box and select MinorID
  73. Click Next
  74. Accept the name of the table as Minors and click Finish
  75. When the records have been imported, click Close
  76. In the Navigation Pane, right-click the Employees table and click Design View
  77. Change the following characteristics of the fields:
     
    Field Name Field Size
    MinrID  
    Minor 100
  78. Close the table
  79. When asked whether you want to save, click Yes
  80. You will also receive a message box that some data may be lost. Read it and click Yes
  81. In the Navigation Pane, right-click Table1, position the mouse on Import, and click Text File Text File
  82. In the Get External Data - Text File dialog box, click the Browse button
  83. Locate the resources that accompany these lessons and select the Students.txt file
  84. Click Open
  85. On the dialog box, click OK
  86. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next

    Import Text Wizard

  87. In the second page, accept that the delimiter be set to Comma or Tab and click Next
  88. In the second page of the wizard, click First Row Contains Field Names

    Importing a Text Document

  89. Click Next
  90. As StudentNumber is selected, click the arrow of the Data Type combo box and select Short Text

    Importing a Text Document

  91. Click Next
  92. Click the arrow of the combo box and select StudentNumber

    Importing a Text Document

  93. Click Next
  94. Accept the name of the table as Students and click Finish
  95. When the records have been imported, click Close
  96. In the Navigation Pane, right-click the Students table and click Design View
  97. Change the following characteristics of the fields:
     
    Field Name Field Size Caption
    StudentNumber 12 Student #
    FirstName 25 First Name
    MiddleName 25  
    LastName 25 Last Name
    DateOfBirth    
    Gender 20  
    Address 120  
    City 40  
    State 50  
    ZIPCode 20 ZIP Code
  98. Close the table
  99. When asked whether you want to save, click Yes
  100. You will also receive a message box that some data may be lost. Read it and click Yes

Exporting a Text File

To save a table as text:

  • In the Navigation Pane, click the table you want to export. In the Export section of the Ribbon, click the Text File button
  • In the Navigation Pane, right-click the table that holds the data, position the mouse on Export, and click Short Text File

This would open the Export - Text File dialog box with the name of the file using the .txt extension. Simply follow the wizard:

 
 
 

Microsoft Access and XML

Importing an XML File

An XML file is essentially a document made of at least one table. A typical XML file starts with a root node:

<?xml version="1.0" standalone="yes"?>

Under it, the global parent node starts and closes itself:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
</FunFurniture>

This global parent node is not the table. Inside that node, you should have a node that would represent a table and it can repeat itself as many times as necessary. Here is an example:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
  </Employees>
  <Employees>
  </Employees>
</FunFurniture>

Inside each table, you should have the name of each column followed by its value:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
    <EmployeeNumber>924795</EmployeeNumber>
    <FirstName>Donald</FirstName>
    <LastName>Tripleton</LastName>
    <Title>Sales Manager</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>274957</EmployeeNumber>
    <FirstName>Jeanne</FirstName>
    <LastName>Wooley</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>684078</EmployeeNumber>
    <FirstName>Irene</FirstName>
    <LastName>Polsen</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>297149</EmployeeNumber>
    <FirstName>Monica</FirstName>
    <LastName>Jackson</LastName>
    <Title>Sales Associate</Title>
  </Employees>
</FunFurniture>

After creating and saving an XML file, to import it in Microsoft Access, in the External Data tab of the Ribbon and in the Import section, click the XML File button XML File and follow the wizard.

Practical Learning: Importing an XML Document

  1. To start a new database, on the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the name to StatesStatistics2
  4. Click Create
  5. Close the default table without saving it
  6. On the Ribbon, click External Data
  7. In the Import & Link section, click the XML File button XML File
  8. In the Get External Data - XML File dialog box, click the Browse button
  9. Locate the resources that accompany these lessons and select the StatesStatistics.xml file
  10. Click Open
  11. On the Get External Data - XML File dialog box, click OK

    Importing an XML Document

  12. In the Import XML dialog box, accept the selections and click OK
  13. In the Get External Data - XML File dialog box, click the Close button
  14. In the Navigation Pane, right-click the Regions table and click Design View
  15. In the top side of the window, make sure Region is selected.
    In the bottom side, set the Field Size to 25
  16. Close the table
  17. When asked whether you want to save, click Yes
  18. You will also receive a message box that some data may be lost. Read it and click Yes
  19. In the Navigation Pane, double-click the States table

    Importing an XML File

  20. After viewing the records, right-click the States tab and click Design View
  21. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size Format Decimal Places Caption
    Abbreviation   2     State
    StateName   40     Name
    AreaSqrMiles Number   Standard 0 Sqr Miles
    AreaSqrKm Number   Standard 0 Sqr Kms
    AdmissionUnionDate Date/Time       Date of Admission to Union
    AdmissionUnionOrder Number Byte     Order of Admission to Union
    Capital   40      
    Region   25      
    StateWebsite Hyperlink       State's Website
    Wikipedia Hyperlink        
  22. Close the table
  23. When asked whether you want to save, click Yes
  24. You will also receive a message box that some data may be lost. Read it and click Yes

Exporting to XML

To export a table to XML format, in the Navigation Pane:

  • Right-click the table that holds the data, position the mouse on Export, and click XML File
  • Click the table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> XML File

The Export - XML File dialog box that comes up allows you to verify the name of the file that will be created and the path where it will go. Once you are ready, you can click OK. A dialog box would come up asking which one(s) of the three files you want to have created:

Export XML

If you want to create a more elaborate XML application with advanced options, you can click the More Options button. This would close the previous dialog box and open another one:

This dialog box allows you to specify more details on how the table should be exported. After making your selections, click OK.

Microsoft Access and the Web

Importing an HTML File

To import an HTML file in Microsoft Access:

  • In the Import section of the Ribbon, click the More button and click HTML Document
  • If the Navigation Pane contains at least one table, right-click a table, position the mouse on Import, and click HTML Document

The primary steps to follow are the same for a text document. In the third page of the wizard, to consider and/or apply more options, click the Advanced button:

Import HTML

After making the necessary adjustments, click OK.

Practical Learning: Importing a Text Document

  1. On the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the name to States Statistics
  4. Click Create
  5. In the Navigation Pane, right-click Table1, position the mouse on Import, and click HTML Document
  6. In the Get External Data - HTML Document dialog box, click the Browse button
  7. Locate the resources that accompany these lessons
  8. Select the states.htm document and click Open
  9. Click OK
  10. In the first page of the Import HTML Wizard, click First Row Contains Column Headings

    Import HTML

  11. Click Next

    Import HTML

  12. Accept the defaults of the second page of the wizard and click Next
  13. In the third page of the wizard, click the arrow of the combo box and select Abbreviation

    Import HTML

  14. Click Next

    Import HTML

  15. Set the name of the table as States
  16. Click Finish
  17. You receive a confirmation message. Click Close
  18. In the Navigation Pane, right-click the States table and click Design View
  19. Change the following characteristics of the fields:
     
    Field Name Data Type Field Size Format Caption
    Abbreviation   5   Abbrv
    StateName   40   State Name
    AreaSqrMiles Number Double Standard Area in Square Miles
    AreaSqrKkm Number Double Standard Area in Square Kilometers
    AdmissionUnionDate       Date of Admission to Union
    Capital   40    
    StateWebsite Hyperlink     State's Website
    Wikipedia Hyperlink      
  20. Close the table
  21. When asked whether you want to save, click Yes
  22. You will also receive a message box that some data may be lost. Read it and click Yes
  23. Close Microsoft Access

A Microsoft Access Table on a Web Page

You can copy and paste Data from a webpage into a table. To do this:

  • If you want to copy all columns and all records, in the Navigation Pane of Microsoft Access, right-click the table and click Copy
  • If you want to copy all columns and all records, open the table in Microsoft Access, press Ctrl + A to select everything. Press Ctrl + C to copy. In the web page of the application, click the section you want on the web page and press Ctrl + V to paste 
  • If you want to copy only some columns and/or some records, open the table in Microsoft Access. Make the selection. Right-click the selection and click Copy. In the web application, right-click the section you want on the web page and click Paste or click the section and press Ctrl + V 

Some other applications do not support copy and paste. An alternative is to export the table to HTML. To save a table as HTML, in the Navigation Pane:

  • Right-click the table that holds the data, position the mouse on Export, and click HTML Document
  • Click the table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> HTML Document

This would open the Export - HTML Document dialog box.

Microsoft Access and Microsoft Word

Introduction

There are various ways you can use Microsoft Word with a Microsoft Access database. As opposed to copying from a database table and pasting to a table, you may want the reverse. That is, you can copy a table from a Microsoft Word document. To do this, in Microsoft Word, select the table in a document and copy it. Start a table in Microsoft Access and paste the records.

Mail Merge

Mail merging allows you to use data on your database to create letters, labels, envelopes, and other documents that require external data originating from another document. To start a mail merge, in the Navigation Pane:

  • Click the table that holds the information you want to use. In the Export section of the Ribbon, click Word Merge
  • Right-click the table, position the mouse on Export and click Word Merge

This would open the Microsoft Word Mail Merge Wizard:

Mail Merge

And click OK. This would open the Select Microsoft Word Document dialog box. It allows you to select the letter/document you plan to use. If you don't have that document, click Cancel, open Microsoft Word, create the document and proceed from there. If you have a letter, select it. This would open the letter in Microsoft Word. ail Merge window, if you want to create a letter to be sent out, accept the Letters radio button and click Next. In the second page of the wizard, you will have the choice of creating a new Microsoft Word document or using an existing document. If you want to create a new document, click or accept Use the Current Document. If you click Start From Existing Document, you will be asked to specify the document, in which case you should click Open, select the document, and click Open:

Mail Merge

After this, the Mail Merge window would come back to the Use Current Document option. Click Next: Select Recipients.

To insert other types of items, in the Mail Merge window, you can click the Address Block link, the Insert Address Block dialog box would come up. The Insert Address Block dialog box is made of various sections. It allows you to specify a type of greeting and other pieces of information to be inserted in the document. After making the selection(s), you can click OK.

After creating the document and adding the necessary fields to it, you can preview and review it. To do this, in the Mail Merge window, click the Next: Preview Your Letters link. When you do this, the letter appears with the value(s) of the first record. To review the document with the other values, in the Mail Merge window, you can click the previous or the next buttons.

After reviewing the document, in the Mail Merge window, you can click the Next: Complete The Merge link. You can then save, print, and manage the document. In the same way, you can create labels or envelopes. 

Practical Learning: Creating a Mail Merge

  1. Start Microsoft Word
  2. From the resources that accompany these lessons, open Promotion.docx
  3. Press Ctrl + A to select everything
  4. Press Ctrl + C to copy
  5. Close Microsoft Word and return to Microsoft Access
  6. From the resources that accompany these lessons, open the Bethesda Car Rental1 database
  7. In the Navigation Pane, in the Tables section, click Customers
  8. To start a mail merge, on the Ribbon, click External Data
  9. In the Export section, click Word Merge
  10. Click the second radio button:
     
    Mail Merge
  11. Click OK.
    Microsoft Word starts.
  12. In Microsoft Word, press Ctrl + V to paste the Promotion.docx document (this would be the equivalent of creating or designing the document)
     
    Mail Marge
  13. In the Mail Merge window, to create a letter to be sent out, accept the Letters radio button
     
    Mail Merge
     
    Click Next: Starting Document
  14. In the Select Starting Document section, make sure Use the Current Document is selected Click Next: Select Recipients
     
    Mail Merge
  15. Click Next: Write Your Letter
     
    Mail Merge
  16. In the Microsoft Word document, double-click Date and press Delete
  17. On the Ribbon of Microsoft Word, click Insert
  18. In the Text section, click Date & Time
  19. In the Available Formats list box of the Date & Time dialog box, click the second option (day, month, and year)
     
    Date and Time
  20. Click OK
  21. Press Enter
  22. Click the right side of Dear
  23. In the Mail Merge window, click More Items
  24. In the Fields list, click FirstName
     
    Insert Merge Field
  25. Click Insert
  26. Click Close
  27. Press the Space bar
  28. In the Mail Merge window, click More Items...
  29. In the Fields name, double-click LastName
  30. Click Close
  31. In the Mail Merge window, click Next: Preview Your Letters
     
    Mail Marge
  32. In the Mail Merge window, click the next recipient button Next Recipient and observe the name in the document
  33. Click the next recipient button 4 times and observe the names in the document
  34. Click the previous recipient button 2 times and observe the names in the document
  35. In the Mail Merge window, click Next: Complete the Merge
  36. Close Microsoft Word
  37. When asked whether you want to save, click Save
  38. Set the file name as Bethesda Car Rental Promotional Campaign1
  39. Click Save
  40. Close Microsoft Access

Sharing a Microsoft Access Database

Distributing the Database

Distributing a database consists of making it available to more than one computer. Both Microsoft Windows and Microsoft Access provide various solutions, One solution is to install Microsoft Access on each computer that will use your database. This may be the most expensive solution but it is the easiest. Most companies use that solution because it is easy either to purchase many copies or get many licenses of Microsoft Office Professional that includes Microsoft Access.

After creating a database, in the server or the computer of your choice (normally, the database can be located on any computer that is a member of your network) where you want the database to be located, create a folder. Copy the database file into that folder

Folder

Access the drive that holds that folder. To share it, right-click it and click Share:

Sharing a Folder

Click the arrow of the combo box in the File Sharing window and select Everyone:

Sharing

After selecting Everyone, click Add. Click the down-pointing arrow on the right side of Everyone:

  • If you are sharing from Microsoft Windows Server 2008, select Contributor

Sharing

  • If you are sharing from Microsoft Windows 7+, select Read/Write

Click Share. If you receive a message box, click Continue.
You should receive a message box telling you that the sharing was successful

Sharing

Click Close.

In another computer where you want to access the database, open a file utility such as Windows Explorer. Click Network. Click the name of the computer to show its shared folders. Open the folder that contains the database. Right-click the name of the database, position the mouse on Send To, and click Desktop (Create Shortcut)

As an alternative, you could right-click the database and click Create Shortcut, then copy that shortcut where you want the database to be accessible. To open the database from that computer, double-click the shortcut.

Importing or Splitting a Database

Introduction

Importing data allows you to get information from an external source and insert it in your database. Microsoft Access can accept data from various applications. To import a file or objects, on the Ribbon, click the External Data tab:

Ribbon

In the Import section of the Ribbon, click the button that corresponds to the type of object you want to get. What happens depends on the type of file you are trying to import.

Importing Microsoft Access Objects

To import objects from another Microsoft Access database, on the Ribbon, click External Data. In the Import section of the Ribbon, click the Access button Access. This would open the Get External Data - Access Database wizard. You should first locate the folder that contains the database, and then select the database.

Splitting a Database

You can divide your database in two parts. One part would be installed on one computer that can be a server or just another computer. This is referred to as back-end. The other part would be installed in each user's computer. The users computers can then connect to the back-end database that stores the actual database. The users computer have only the graphical interface that allows them to work. To implement this scenario, you split your database.

To split a database, on the Ribbon, click Database Tools:

Ribbon - Database Tools

In the Move Data section, click the Access Database button Access Database. This would open the Database Splitter dialog box with four paragraphs and two buttons:

Database Splitter

After reading them, click Split Database and follow the wizard.

Practical Learning: Splitting a Database

  1. From the resources that accompany these lessons, open the Computer Training Center database.
    Notice the objects in the Navigation Pane
     
    Splitting a Database Splitting a Database Splitting a Database
  2. On the Ribbon, click Database Tools
  3. In the Move Data section, click Access Database Access Database
     
    Database Splitter
  4. You receive a Database Splitter dialog box. Read the paragraphs and click Split Database
  5. In the Create Back-End Database dialog box, change the name of the file to ComputerTrainingCenterBackEnd
  6. Click Split.
    When the splitting is over, a dialog box appears
     
    Database Splitter
  7. Read it and click OK
  8. Open the ComputerTrainingCenterBackEnd database. Notice the icons of the tables in the Navigation Pane

    Navigation Pane after splitting a database

  9. Re-open the Bethesda Car Rental5 database and notice the new icons of the tables in the Navigation Pane

    Splitting a Database

  10. Close Microsoft Access
 
 
   
 

Previous Copyright © 2016, FunctionX, Inc. Next