Working with Data

How to store data

My tables are ready. How do I store data into the tables?

Let's assume you have data already stored in another file. Microsoft Access has the facility to import a copy of the data straight into the tables of your Microsoft Access database. Alternatively, you can create a link to data in another database application if you feel the information would be too dynamic, unnecessary or time-consuming to import on a regular basis.

When importing data, remember to:

  1. Click the Database Window button on the toolbar to switch to the Database window.
  2. Click the Import button on the toolbar. The Import dialog box appears.
  3. Select the file you want data to be imported from.
  4. Click OK. A dialog box will appear asking you various questions about where the data should go (for example, you may want to create a brand new table known as Adding Field Names, or selecting certains fields to store the data known as Appending Data).
  5. Click Import when you have finished.
  6. After importing the data, click Close.

I am having trouble importing data. Why?

If, at any time, you should have trouble importing data to a new table, remember to check for things like (i) the values are of the same data type (e.g. Microsoft Access doesn't copy text data into a number field); (ii) the field size is not too small (e.g. Microsoft Access can't copy 20 characters of text data into a 10-character text field); and (iii) no duplicate values in the primary key field.

If you find what is causing the problem, edit the file you wish to import data from, or modify your own Microsoft Access database to accept the data properly, and then you can try importing it again. If you need help to work out why the data did not import correctly, check the Import Errors table. Microsoft Access will record any errors it finds during data importing in the Import Errors table. Click the Table button in the Database window to find this table.

How do I link data to my database instead of directly importing it?

To link data to your Microsoft Access database instead of importing it, you need to attach an external table. An external table is a table in another database application. To attach and access an external table to your Microsoft Access database:

  1. Click on the Database Window button on the toolbar.
  2. Click the Attach Table button on the toolbar.
  3. The Attach Table dialog box appears.
  4. Select the database file type and click OK. Another dialog box appears, asking you to choose the external database file you want to access.
  5. Click OK. You may also be asked to choose the table you want to access if the database file is another Microsoft Access database file.
  6. Microsoft Access shows your linked table in the Database window. Click Close when you have finished.

How do I enter data directly into my database?

Instead of importing or linking data into your tables, you can type the data directly in the table datasheet view,

or as a form,

Datasheets display data in columns and rows just like a Microsoft Excel spreadsheet so you can view many records all at once. In forms, you view and edit only one record at a time (and perhaps a few extra records from another table using subforms, as we shall discuss later) and where data is displayed in a customised layout for easier viewing. We shall learn more about how to create forms later. But for now, we will concentrate on datasheets.

To navigate through the fields as you enter data, press the Tab, Right Arrow or Enter keys. To move back to a previous field, press the Shift-Tab or Left Arrow key. Or you can use the mouse to click into any field of any record.

If you should find Microsoft Access has prevented you from editing your data, choose the Allow Editing command from the Records menu. Otherwise, the datasheet (or form) may have been designed by another user to prevent editing. Furthermore, Microsoft Access will not allow you to edit data in Counter (i.e. AutoNumber) fields since Microsoft Access automatically fills in a Counter field with the next consecutive number and therefore does not need editing. The same is true of calculation fields (i.e. formulas) and fields that have their "Locked" property set to "Yes" in the Table Design window. Also check to make sure you have not violated certain validation rules as these may prevent you from editing or entering data in a certain way.

If you make a mistake when entering data:

  1. Click the Undo button on the toolbar; or
  2. Click the Undo Current Record button on the toolbar to undo all changes to the current field or record.

NOTE: You cannot undo paste, cut, delete or replace operations.

How do I move around from one record to another?

Use your mouse to click into the field of another record in your datasheet; it will make that record the current record. Alternatively, use the navigation buttons displayed in the lower-left window.

To move to a specific record number, select the record number in the navigation toolbar above and type the number you want, and then press Enter.

If you wish to use the keyboard for navigation, the Up and Down Arrow keys moves from one record to the next within the same field. Pressing the END and HOME keys will move the cursor to the rightmost (or last) or leftmost (or first) field in the current record, respectively. Pressing CTRL key and the END or HOME keys at the same time will move the cursor to the rightmost (or last) field of the last record or leftmost (or first) field in the first record.

How do I create a new record?

To create a new record and so add more data to your database:

  1. Click the last record button on the navigation toolbar. Or click on the Go To New Record button on the main toolbar to go to the first blank field of the new record.
  2. Start entering data and press Tab to move to the next field.
  3. For Microsoft Access to save your data automatically, press the Tab key one more time so that you can move to the next new record. Or choose Save Record under the File menu.

NOTE: You will know if your data has not been saved if, while editing a record, you see a pencil icon.

Can I delete records?

To delete records from your database:

  1. Select the records you want deleted by clicking the record selector in the far left-hand column. If required, while clicking on the record selector, keep the mouse button down and drag the pointer over all the records you want.
  2. Click the Cut button on the toolbar, or choose Cut from the Edit menu.
  3. Microsoft Access displays a dialog box for you to confirm your decision or cancel your change. Click OK if you are happy to delete the records.