Lessons Logo

Database Maintenance, Import, and Export

 

Data Maintenance

 

Default Value

Data entry consists of typing values in data fields or selecting values from bound controls. Some records happen to have the same value for a particular field, or most records hold a common value for a certain field. When designing a form, you can assign the most commonly used value to such a field so the user would not have to type it. The user would change the value only if it is different from the usual. For example, when creating a database for a small company, all employees may have the same telephone number but with individual extensions. When a new record is being entered, the value would be set already for the field.

To specify a regularly used value for a field, open a table or a form in Design View, select the field, and type the desired value in the Default Value field.

The default value should be appropriately typed:

  • If the field is text-based, you can type the default text included in double-quotes. Examples are “M”, “Virginia”, “(301) 122-4738”
  • If the field is numeric, you can type the default number
  • If the field is date-based, you have two options and you want to specify a fixed date as the default value, type it between two # signs. An example would be #2/5/1998#
  • If the field is date-based, you can also type a function that would find the correct date to display. For example, to provide the current date as default value, you would use the Date() function.

Practical Learning:  Setting Default Values

  1. Open the Rockville Techno database
  2. In the database window, click Tables and double-click the Employees table to open it in Datasheet View
  3. Switch it to Design View
  4. In the upper section of the table, click Country 
  5. In the lower section, click Default Value, type “USA” and press Enter
  6. In the upper section of the table, click WorkPhone
  7. In the lower section, click Default Value, type “(301) 668-0808” and press Enter
  8. In the upper section of the table, click DateHired
  9. In the lower section, click Default Value, type =Date() and press Enter
  10. Save the table

Validation Rule

A validate rule is a condition that the value entered in a field must meet in order to be valid. This rule is usually created as an expression and entered in the Validation Rule property of a field when the table or the form is opened in Design View.

Practical Learning:  Setting Validation Rules

  1. The Employees table of the Rockville Techno database should still be opened with the Employees table in Design View.
    In the upper section of the table, click BillingRate
  2. In the lower section, click Validation Rule, type 15.00 and press Enter
  3. Save the table

Validation Text

When the condition in the Validation Rule field is not respected, you can display a message box to let the user know. The message for that text box can be created as a string in the Validation Text field.

Practical Learning:  Creating a Validation Text

  1. In the upper section of the table, click BillingRate
  2. In the lower section, type:
    The minimum billing rate or salary of this company is $15.00
  3. Save and close the table

 

 

Value Required for a Field

If you think that there must be an entry for a particular field for each record, you can let Microsoft Access know. The Required property is Boolean value. If you set it to Yes, the user would not be able to move to the next record until he has entered a valid data in the field. Its default value is No.

Practical Learning:  Specifying Required Fields

  1. Open the Clients table in Datasheet View and, after viewing it, switch it to Design View
  2. In the upper section of the table, click ContactLastName
  3. In the lower section, double-click Required to change its value to Yes
  4. In the upper section of the table, click PhoneNumber
  5. In the lower section, double-click Required to change its value to Yes
  6. Save and close the table

Indexed Fields

When data is entered in fields, it is possible to have the same value for a field in different records, such as two people who live in the same state. This is considered as a duplicate value. In some other cases, this may not be acceptable. For example, you may not want two employees to have the same employee number. This characteristic can be set using the Indexed property that provides 3 values:

  • If set to No (its default), no duplicate value checking will be done
  • If you want the database engine to check for duplicate but not necessary take any action, set this property to Yes (Duplicates OK)
  • If you do not want a duplicate value of the same field in different records, set the field’s Indexed property to Yes (No Duplicates)

Practical Learning:  Controlling Indexed Fields

  1. Right-click the Employees table and click Design View
  2. In the upper section, click EmployeeNumber
  3. In the lower section, click Indexed to display its combo box. Click the arrow of the Indexed combo box and select Yes (No Duplicates)
  4. In the upper section, click EmailAddress
  5. In the lower section, double-click Indexed a few times until it display Yes (No Duplicates)

Data Import/Export

 

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, and with a little trick, you can import even from unfriendly applications. Before importing data, you must make sure that it is in a format Microsoft Access can read; even if that data is coming from another application of the Microsoft Office suite.

Practical Learning:  Introducing Data Import

  1. From the resources that accompany our lessons, locate a text file named Customers, a Microsoft Excel spreadsheet named Employees, and a Microsoft Access database named Georgetown
  2. Copy and paste them in your Exercises folder
  3. In Microsoft Access, create a new blank database named GCS

Import/Export With Text Files

As you are already familiar with Microsoft Access functionality, you know that a cell is delimited with gridlines. If you intend to import a text document, format it so that Microsoft Access can recognize where a field starts and where it ends. Such a file can be created with Notepad. This delimiter is usually done by pressing Tab when creating the fields' content and pressing Enter at the end of each record. Instead of the Tab key, you can also use a comma or a semi-colon to separate two fields. Since a field is usually made of more than one word (such as an address), enclose the content of each field in double quotes, as in "1600 Pennsylvania Avenue". 

When importing data, you use a wizard that will assist you in identifying the document, the fields’ delimiter, and the target table where you would like to store the new data. You can create a new stable or use one already created in your database

Practical Learning:  Importing a Text Document

  1. Start a text application, such as Notepad (Start -> (All) Programs -> Accessories -> Notepad)
  2. Open the Customers.txt file to see what it looks like. Then close Notepad
  3. On the main menu, click: File -> Get External Data -> Import...
  4. Locate your Exercises folder and display it in the Look In combo box
  5. Click the arrow of the Files Of Type combo box and select Text Files
  6. On the list of files, click Customers (it should be selected already) and click Import
  7. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
  8. On the second page, set the delimiter as Comma and check the box stating: First Row Contains Field Names
     
  9. Click Next
  10. Accept to store the data In A New Table and click Next
  11. Click the Notes column to select it then, in the Data Type combo box, select Memo
     
  12. Click Next
  13. Accept to Let Access Add Primary Key and click Next
  14. Finally, you are asked to give a name to the table.
    Accept the name of the table as Customers
     
  15. Click Finish.
    You should receive a confirmation message when the table has been imported
     
  16. Click OK
  17. In the Database window, click Tables and double-click the Customers table to see what it looks like
  18. Close the Customers table

Import/Export With Microsoft Access Objects

Microsoft Access objects are probably the easiest objects to import because, created with of the same application, these objects are already formatted and recognizable.

To import objects from another Microsoft Access database, you can use the main menu or the New Table dialog box.

Practical Learning:  Importing a Microsoft Access Object

  1. The GCS database should still be opened.
    On the main menu, click File -> Get External Data -> Import…
  2. Locate your Exercises folder and display it in the Look In combo box. Change the Files Of Type combo box to Microsoft Access
  3. Then, click Georgetown and click Import
  4. In the Import Objects property sheet, click the Forms property page
  5. In the Forms property page, click OrderProcessing
     
  6. Click OK

Database Objects Linking

Linking data allows you to connect your database to an external document. Such a table can have its data accessed directly or modified in Microsoft Access. When linking data, since you are making it available to the database, make sure that Microsoft Access can "read" it; otherwise it would not recognize its content. The main reason you link data is to treat your database as one file despite its belonging to another document. Linking to a spreadsheet is usually a straightforward process. Linking to a word processing document usually involves performing a mail merge.

Practical Learning:  Linking to a Microsoft Access Object

  1. The GCS database should still be opened.
    On the main menu, click Insert -> Table
  2. In the New Table dialog box, click Link Table
     
  3. Click OK
  4. Locate your Exercises folder and display it in the Look In combo box. Click Georgetown and click Link
  5. In the Link Tables property sheet and in the Tables property page, click OrderProcessing
     
  6. Click OK
  7. In the Database window, click Tables

Import/Export/Linking With Spreadsheet Files

When creating a spreadsheet in Microsoft Excel for data import, you have two alternatives. You can create the column headers at the very top of the worksheet and type a data field in each corresponding column. Alternatively, you can create data as on a table, and then create a name range that holds the information you need in the database. To import a Microsoft Excel spreadsheet, on the main menu, you can click File -> Get External Data -> Import. Locate the file that holds the spreadsheet and click Import. You would be asked to specify what to import, a whole spreadsheet or a named range.

There are various circumstances when you will need to link to a spreadsheet rather than importing it. The main idea is to accommodate your users. To link to a spreadsheet, you use the same approach.

Practical Learning:  Linking to a Spreadsheet

  1. Start Microsoft Excel if you have it. From your Exercises folder, open the Employees spreadsheet to see what it looks like. Then close Microsoft Excel and return to Microsoft Access.
    The GCS database should still be opened.
    On the main menu, click File -> Get External Data -> Link Tables…
  2. Locate your Exercises folder and display it in the Look In combo box
  3. Change the Files of Type to Microsoft Excel and click Employees (it should be selected already
  4. Click Link
  5. In the first page of the Link Spreadsheet Wizard, accept the Show Worksheets radio button and, in the list, click Employees
     
  6. Click Next
     
  7. On the 2nd page, make sure the First Row Contains Column Headings check box is selected and click Next
  8. Accept the name of the table as Employees and click Finish
  9. A message box lets you know that the table has been linked.
    Click OK
     
  10. To view the table in Microsoft Access, double-click the Employees linked table
  11. After viewing the table, close it 

Import/Export With Microsoft Outlook

Importing a Microsoft Outlook document is mainly done on the address book level and is as smooth as what we have seen so far. All you have to do is have the address book already created and you are ready to go.

The following exercise is not supposed to be performed here; it is provided as a guide 

  1. On the Database toolbar, click the arrow of the New Object button and choose Table
  2. In the New Table dialog, double-click Import Table
  3. In the Import dialog, change the Files of Type to Outlook
  4. A particular wizard starts. In the first page of the wizard, click the + on Personal Folders to expand it
     
  5. Click Contacts and click Next
  6. Accept to store data In A New Table and click Next
  7. Click Next, Next, Finish, and OK

 Saving a Database Object As a Web Page

Microsoft Access allows you to create a web page based of your database. The process of doing this is different from one version of Microsoft Access to another. We will cover only Microsoft Access 2000.

Practical Learning:  Exporting a Database Object as HTML

  1. Open the Rockville Techno database
  2. In the Database window, click Queries. Right-click AssetsInventory and click Exports…
  3. In the Export Query dialog box, locate and display your Exercises folder in the Save In combo box
  4. In the Save As Type combo box, select HTML Documents
     
  5. Click Save
  6. Still in the Queries section of the Database window, click AssetsAssignedToEmployees to select it
  7. On the main menu, click File -> Export…
  8. Make sure the Save As Type displays HTML Documents. Click the Save Formatted check box and click Save
  9. On the HTML Output Options dialog box, click the Browse button
  10. Locate the folder in which you installed Microsoft Access or Microsoft Office. Open the Templates folder. Open the 1033 folder. Open the Pages folder. Open the Normal folder
     
  11. Click Normal and click OK
  12. On the HTML Output Options dialog box, click OK

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.

When performing a mail merge, you usually do not need all the fields that are part of a table. Although you can use all fields on a table, it is recommended that you create a query made only of fields you need for your document. This would include the name and address of the recipient.

Perform the following exercise only if you have Microsoft Word

Practical Learning:  Merging Data With Microsoft Word

  1. Open the Bethesda Car Rental1 database
  2. On the main menu of Microsoft Access, click Insert -> Query
  3. On the New Query dialog box, double-click Simple Query Wizard. 
  4. In the 1st page of the Simple Query Wizard, click the arrow of the Tables/Queries combo box and select Table: Customers
  5. On the Available Fields list box, double-click FirstName, LastName, Address, City, State, ZIPCode, and Country 
  6. Click Next
  7. Change the name of the query to CustumersContact and click Finish
  8. Close the query and close Microsoft Access
  9. Start Microsoft Word
  10. On the main menu, click Tools -> Mail Merge… 
  11. On the Mail Merge Helper dialog box, click Create -> Form Letters…
     
  12. On the Microsoft Word dialog box, click New Main Document
  13. On the Microsoft Word dialog box, click Get Data -> Open Data Source…
     
  14. From the Open Data Source dialog, click the arrow of the Files Of Types combo box and select MS Access Databases
  15. Using the Look In combo box, locate your Exercises folder and display it in the Look In combo box
  16. Click Bethesda Car Rental1 and click Open
  17. From the Microsoft Access dialog, click the Queries property page 
  18. Click CustumersContact and click OK
  19. Now Microsoft Word would like you to create the letter
    Click Edit Main Document
  20. To save the letter, press Ctrl + S and save the document as Promotion102
  21. Now we will create the main letter
    Since in the beginning we decided to create a New Main Document, if you are using Microsoft Word 2000, you should have two instances of Microsoft Word running (on the Taskbar)
    If you are using Microsoft Word 2000, from the Taskbar, click the other instance of Microsoft Word, it might display Document1 - Microsoft Word
    If you are using Microsoft Word 97, on the menu bar, click Window -> Document1 
  22. On the main menu, click File -> Open... 
  23. From the exercises that accompany our lessons, select the BCR Promotion document, and then click Open. That opens the document we will use
  24. On the main menu, click Edit -> Select All
  25. On the main menu again, click Edit -> Copy 
  26. From the Taskbar, click the instance of Microsoft Word that is holding our mail merge process
  27. On the main menu, click Edit -> Paste. After the letter has been pasted, all we have to do now is to edit the document for our mail merge 
  28. Press Ctrl + Home to get to the beginning of the letter
  29. Double-click Date to highlight it
  30. On the main menu, click Insert -> Date and Time...
  31. From the Date And Time dialog, click the 3rd date
     
  32. And click OK
  33. Click on the right side of Dear 
  34. On the Mail Merge toolbar, click Insert Merge Field -> FirstName 
  35. Press the Space bar
  36. On the Mail Merge toolbar, click Insert Merge Field -> LastName
  37. To read and preview the document, on the Mail Merge Helper toolbar, click the View Merged Field button
     
    Link to MS Word
  38. To review the letters, use the navigation buttons:
     
    Button Name Description
    View Merged Data Toggles the code of the merging process
    First Record Displays the very first record on the list
    Previous Record Displays the previous record on the list
    Go To Record To jump to a particular record, click in the Go To Record text box, type a number and press Enter. The corresponding record will be displayed
    Next Record Displays the next record on the list
    Last Record Displays the very last record on the list
  39. To print the letters, on the Mail Merge toolbar, click the Merge to Printer button 
  40. On the Print dialog box, click OK
  41. Now we will create the envelopes
  42. On the main menu, click Tools -> Mail Merge… 
  43. On the Mail Merge Helper dialog box, click Create -> Envelopes... 
  44. When you receive the dialog, click New Main Document
  45. In the Data Source section, click Get Data -> Open Data Source... 
  46. Change the Files of Type combo box to MS Access Database then locate and display your Exercises folder in the Look In combo box
  47. From the list of files, select Bethesda Car Rental1 and click Open. 
  48. On the Microsoft Access dialog box, click the Queries property page. Click CustumersContact and click OK
     
  49. Click Set Up Main Document
  50. In the Envelope Options dialog box, accept the envelope size 10 and click OK 
  51. In the Envelope Address dialog, click Insert Merge Field -> FirstName 
  52. Press the Space bar 
  53. Click Insert Merge Field -> LastName 
  54. Press Enter. Click Insert Merge Field -> Address 
  55. Press Enter
  56. Click Insert Merge Field -> City 
  57. Type the comma and press the Space bar
  58. Click Insert Merge Field -> State and press the Space bar
  59. Click Insert Merge Field -> ZIPCode 
  60. Press Ctrl + Home to position the cursor to the left of FirstName 
  61. Click Insert Postal Bar Code... 
  62. Click Merge Field With ZIP Code combo box and select ZIPCode 
  63. Click the Merge Field With Street Address combo box and select Address
     
  64. Click OK
  65. Click OK and click Close
     
  66. To view the merged envelopes, on the Mail Merge toolbar, click the View Merged Data button
  67. Once more, to review the envelopes, use the navigation buttons on the Mail Merge toolbar. 
  68. To print the envelopes, on the Mail Merge toolbar, click the Merge to Printer button
  69. Close Microsoft Word. You will be prompted to save your document. It is up to you

Considerations on Data Entry

The Computer Regional Settings

The information in a database is stored in tables, although you will usually not allow users to get access to such objects. The table is therefore the most principal means of data entry.

The computer controls how it is supposed to display some pieces of information such as date and time values. These also affect internal settings of databases and other programs used on a computer. To view, control or set these properties, the computer bases its decisions on the Regional Settings dialog box from the Control Panel window.

To review the Regional Settings of your computer, you can click Start -> Settings -> Control Panel. If you are using MS WinXP, you can click Start -> Control Panel. From the Control Panel window, you can double-click Regional Settings. If you are using WinXP, click Regional and Language Options. Click each of the property pages: Number, Currency, Date, and Time

Practical Learning: Using the Computer's Regional Settings

  1. Open Control Panel and double Regional Settings. If you are using MS Windows XP, after opening Control Panel, click Switch to Classic View, then double-click Regional and Language Options and click Customize...
  2. Click the Date property page
  3. In the Short Date Style combo box, select M/d/yy
     
  4. Click OK twice
  5. Return to Microsoft Access and open the CompanyAssets table

 

The Office Clipboard

If you are using Microsoft Access 97, skip this section

The computer clipboard is a non-spatial object used to temporarily hold a piece of data. In fact, it is one form of memory in which you can store information to be retrieved. The word temporary implies two things. First, the clipboard can hold its data only as long as the computer is On. If you turn the computer Off, the clipboard gets emptied and looses its data. Second, the clipboard can hold only one type of data. The good news is that the clipboard is not strictly confined to one type of information. This means that it can hold a picture (any type) OR text (any length), anything. This was the behavior of the classic clipboard.

Microsoft Access 2000 brought tremendous improvement to the clipboard. The new Microsoft Office clipboard can hold up to 12 items. Each item is independent of the others and can individually be anything. In fact, information held by one of these memories can origin from any application.

To use Microsoft Office Clipboard, you should first display it. To do this, you can first open an object such as a table or a form. Then, on the main menu, you can click View -> Toolbars -> Clipboard. To copy an object to the clipboard, you can just press Ctrl + C or Edit -> Copy from the main menu. You can also select something and then click Copy on the Clipboard toolbar.

 

The Size of a Database

A database is primarily a computer file, just like those created with other applications. As such, it occupies an amount of space in the computer memory. In some circumstances, you should know how much space your product is using. This can be important when you need to back it up or when it is time to distribute it. Also, when adding and deleting objects from your database, its file can grow or shrink without your direct intervention.

Like any other computer file, to know the size of a database, you can right-click it in Windows Explorer or My Computer and click Properties. If you are already using the database, to check its size, you can access the properties from the main menu under the File category.

Practical Learning:  Checking the Size of a Database File

  1. Open the Bethesda Car Rental1 database
  2. To check its size, on the main menu, click File -> Database Properties…
  3. In the Properties dialog box, click the General tab and notice the value on the right side of the Size label
  4. Click Cancel to close the Properties dialog box

Deleting Database Objects

As we have seen so far, there are various reasons you create objects in your database. Some will be made part of the final product. Some others are used to test conditions, values, data entry, etc. When such temporary objects are not needed anymore, you can delete them.

To delete a table, we learned that you could use the DROP TABLE expression in a SQL statement and specify the name of the table. To delete any database object (table, query, form, etc), you can click to select it, and then press Delete. Unlike some other database environments (like Corel Paradox), a Microsoft Access table is not a stand-alone object, which means you cannot access it as its own entity outside of its database. For this reason, when you delete a database object such as a table or a form, it does not go into the Recycle Bin. Therefore, the deleting action is irreversible. Whenever in doubt, do not delete a database object, especially a table.

Practical Learning:  Deleting a Database

  1. The Bethesda Car Rental1 database should still be opened
    In the Database window, click Forms
  2. Click Custumers2 to select it
  3. On your keyboard, press Delete
  4. When a warning message comes up, read it and click Yes
  5. In the Database window, click Tables
  6. Right-click the CarsToConsiderRetiring table and click Delete
  7. To check the current size of the database, on the main menu, click File -> Database Properties
  8. Check the size in the General property page and click Cancel to close the Properties dialog box

Compact and Repair of a Database

As mentioned already, once you have created a database file, it occupies a certain amount of memory space that can grow or shrink without your direct intervention:

When you add an object to the database, the database's file grows as needed. When you remove an object, the memory space it was occupying is left empty. This also applies when you keep removing objects:

The computer is supposed to recuperate the space those previous objects were using. Unfortunately, that is not the case. Most of the time, that space is left empty but cannot be accessed by the computer. This means that the memory space cannot be made available to other applications. To recover this memory space, you can compact the database. When this is done, the file is shrunk to occupy only the necessary amount of space and free the unused sections:

To compact a database, on the main menu, you can click Tools -> Database Utilities -> Compact and Repair (in Microsoft Access 97, the Compact Database and the Repair Database are separate menu items).

Practical Learning:  Compacting a Database

  1. The Bethesda Car Rental1 database should still be opened. If you want, check the database size again.
    On the main menu, click Tools -> Database Utilities -> Compact (and Repair) Database…
  2. When the operation has been completed, on the main menu, click File -> Database Properties…
  3. Notice that the database size has been reduced. Click Cancel to close the Properties dialog box.

Maintenance of Imported Objects

Like most other computer applications, a database is a product that can benefit from regular revisions and improvements. There are various operations related to database maintenance, including renaming objects, changing the sequence of fields, applying customers’ feed back. We have already performed some of these actions such as renaming fields of a table in Datasheet View and in Design View.

Whenever you import a database object from an external source, to be understood by Microsoft Access, its data field would receive some configuration that you may not like. For this reason, after importing a table from a text file or from a Microsoft Excel spreadsheet, you should review it in Design View and customize the fields if necessary. Most of the time, each text field imported would be assigned a field size of 255 characters. If a field in numeric, you should review it and specify the appropriate numeric data type. If you import a spreadsheet a field hold monetary values, its field would be translated with the Currency data type.

Microsoft Excel and text files do not have the concept of AutoNumber. When importing data from an external source, if you want to use an AutoNumber field on the new object, you should let Microsoft Access create a new field as the Primary Key. In this case, Microsoft Access would also create it as AutoNumber. If you decide to create your own primary key and if the object already contains data, you cannot change it to an AutoNumber.

If you link your database to an external object, you should not modify its structure because your database is not supposed to control this. Your database in this type of scenario is mainly used for data entry. This is why you should make a decision between importing and linking.

Practical Learning:  Customizing Imported Objects

  1. Open the GCS database that you created above
  2. From the Tables section of the Database window, double-click the Customers table to open it in Datasheet View
  3. After viewing the table, switch it to Design View
  4. Double-click ID to select it. Type CustomerID and press F6
  5. Change its Caption to Customer ID
  6. In the upper section of the table, double-click HomePhone. Type CustomerPhone and complete the table as follows:
     
    Field Name Field Size Input Mask Caption Required
    FirstName 20   First Name No
    LastName 20   Last Name Yes
    CustomerPhone 20 !\(999") "000\-0000;0;_ Phone Number  
    Notes        
  7.    Save the table and close it

Data Objects Backups

If you are working on a complex database and need to perform some tests on it, you should avoid use the original objects as a test might mess them. The alternative is to create a copy of either the object or the database itself. It is important to remember that data resides on tables. For this reason, if you copy a form or query and change the data, for example if you delete a record, the record would be deleted from the database. If you copy a table and make record changes on the new table, you would be safe because such a table would have a new and independent set of records.

To make a backup copy of any object that is part of your database, you can right-click it and click Copy. Then right click the same window and click Paste. Since two objects (of the same category) cannot have the same name, a dialog box would display to request a new and different name for the object you are copying.

Practical Learning:  Backing Up Database Objects

  1. Open the Bethesda Car Rental1 database and, in the Database window, click Tables
  2. To make a backup of a table, right-click the VariousOrders table and click Save As. If you are using Microsoft Access 97, click the second radio button (Within The Current Database As)
  3. Set the name of the table to OrderProcessing and click OK

Database Backup

When working regularly with a database, you should always make a backup of your database, in case something bad happens like a computer complete crash where you may not be able to recover. In most cases too, especially if you have a customer whose product you have developed, you should make a copy of the database to give away while keeping the original.

To make a backup copy of a database, you can open either Windows Explorer or My Computer, locate the folder in which the database is located, then right-click it and click Copy. If you right-click the same window and click Paste, a copy of the same database would be created with a name that starts with Copy of… If you right-click another folder and click Paste, a new copy of the database with the same name would be created in the new folder.

You can also make a backup copy of a database to distribute either to colleagues, employees, students, or customers, etc. In this case, you can copy it from Windows Explorer or My Computer and paste it in the floppy drive. If the database is too large, such as the current Bethesda Car Rental (because it has grown a lot after adding the pictures), it would not fit in one floppy disk. In such case, you can zip it using zip software and possibly spread it on various floppy disks. The best alternative is probably to put on a CD-ROM. Since most computers nowadays ship with a CD Writer, this should not be difficult to do.

If you have a more advanced backup system for your computer, you can also use it regular to backup your database. As backup systems are different, if you have one, it would give you the information you need to proceed.

The following exercises are completely optional.

Practical Learning:  Backing Up a Database

  1. Open the Clarksville Ice Cream2 database
  2. To compact it, on the main menu, click Tools -> Database Utilities -> Compact (And Repair) Database
  3. Close Microsoft Access
  4. Open Windows Explorer and put a floppy disk in the floppy drive (A:). Right-click the A: drive and click Format…
  5. After formatting the disk, click Close on the 
  6. Still in Windows Explorer, on the left frame, select your Exercises folder
  7. On the right frame, click Clarksville Ice Cream2 and drag it to the floppy drive to back it up
  8. To backup a database to a CD-R, put a blank CD in your CD writer drive
  9. If a window comes up asking you to create a CD, you can use it. If nothing comes up, in the right frame of Windows Explorer, right-click Bethesda Car Rental1, position the mouse on Send To and click the name of the drive that is the CD-R

MDE Files

If you have created a database and decide to distribute it or to give it to a customer, colleagues or employees, they may be tempted to open the forms in Design View and possibly modify their structures. There are two disadvantages to such an action. They may compromise or corrupt your hard, possibly rendering obsolete. Some bad eye may decide to cheat on your hard work VBA code. Microsoft Access allows you to make some or most of these actions difficult or impossible.

An MDE file is a database compiled by Microsoft Access so the users of the database cannot change the design of forms or reports, create new forms or reports, view its VBA code, or import forms, reports or VBA code.

To create an MDE file of your database, after opening it, on the main menu, you can click Tools -> Database Utilities -> Make MDE File…

Practical Learning:  Creating an MDE File

  1. Open the Rockville Techno database
  2. On the main menu, click Tools -> Database Utilities -> Make MDE File…
 

MOUS Topics

S21 Use the Office Clipboard
S42 Import data to a new table
S43 Save a table, query, or form as a Web page
S46 Back-up and restore a database
S47 Compact and repair a database
 

Exercises 

Yugo National Bank

  1. Open the Yugo National Bank.
    Open the Customers table in Design View and set the following properties
     
    Field Name Default Value Required Indexed
    AccountNumber     Yes (No Duplicates)
    CustomerName   Yes  
    Address   Yes  
    City   Yes  
    State   Yes  
    ZIPCode   Yes  
    Country "USA"    
    HomePhone   Yes  

    Save and close the table

  2. Open the Employees table in Design View and change the following properties
      
    Field Name Default Value Required Indexed
    EmployeeNumber     Yes (No Duplicates)
    LastName   Yes  
    WorkPhone "(410) 653-1309"    
    Country "USA"    

    Save and close the table

  3. Open the Transactions table in Design View and change the following properties
      
    Field Name Default Value Required Indexed
    EmployeeID Delete the 0 Yes  
    CustomerID Delete the 0 Yes  
    TransactionTypeID Delete the 0 Yes  
    TransactionDate   Yes  
    TransactionNumber   Yes Yes (No Duplicates)
    ServiceCharge 0    
    ChargeReasonID Delete the 0    

    Save and close the table

  4. Save the Employees form as HTML and access all defaults
  5. Save the Customers table as an MS Excel spreadsheet (Tools -> Office Links -> Analyze It With MS Excel)
  6. Check the size of the database. Then compact it

Watts A Loan

  1. Open the Watts A Loan database.
    Open the Customers table in Design View and set the following properties
     
    Field Name Default Value Required Indexed
    AccountNumber     Yes (No Duplicates)
    LastName   Yes  
    Address   Yes  
    City   Yes  
    State "MD" Yes  
    ZIPCode   Yes  
    Country "USA"    
    HomePhone   Yes  

    Save and close the table

  2. Open the Employees table in Design View and change the following properties
      
    Field Name Default Value Required Indexed
    LastName   Yes  
    WorkPhone "(410) 781-6238"    
    State "MD"    
    Country "USA"    

    Save and close the table

  3. Open the Transactions table in Design View and delete the number 0 in the Default Value property of the EmployeeID field. Delete the number 0 as the Default Value of the CustomerID field. Set 0 as the Default Value of the TransactionAmount field
  4. Open the LoanProcessing table in Design View and change the following properties
      

    Field Name Default Value Required
    EmployeeID   Yes
    DateProcessed   Yes
    CustomerID   Yes
    TypeOfLoan 1 Yes
    LoanAmount 0  
    InterestRate 0.0875  
    RegularPaymentsDueOn "The 25th of the month"  

    Save and close the table

  5. Open the Customers table. Select all records (Ctrl + A -> Ctrl + C) and paste the selection in Notepad. Save the text file as WAL Employees in your My Documents folder
  6. Save the Employees table as text
 

Previous Copyright © 2002-2004 FunctionX, Inc. Next