Getting Data into Access

Now that you have covered tables, you are ready to bring data from outside sources into Access. Apart from creating a table from scratch and manually entering the data, the two main methods for bringing data into Access are importing and linking.

Importing

With importing, you are making a copy of the data and filling a newly created table with the copied data. After importing, the data is disconnected from the source from which it was imported. If any future changes are made to the outside source, they will not be reflected in the Access data. This is also true in the other direction, in that changes to your Access data will have no affect on the original source. After importing a table, it is common to treat that Access data as the true data source. Any updating, appending, or deleting will be done to the Access data. Then when it is time to analyze the data, you can be sure it reflects the latest, most accurate version of that data.

Linking

When you link a table to Access, you are creating a pointer to another data source. When the Access database is opened, it establishes links to its outside data source and displays the data as if it were a regular local Access table. However, linked data does not reside in Access. The data is physically located on another computer, server, or other source. If you change the data in the Access table, the true data source will reflect that change. If you change the original data source, when you reopen your linked table, those changes will be reflected.

Things to Remember About Importing Data

Whether you choose importing or linking data can depend on the situation. When you import data, it resides directly in the Access file, so operations on that data perform much more quickly. With linked tables, you can be dependant on the speed of the connection with the data source or the speed of the database engine at the other end.

An important point to remember is that when importing data you can select to create a new table or import your data into an existing table. If you choose to create a new table, Access makes a copy of the data to import and then attempts to determine the column names and field data types. Access may make an incorrect assumption about the data type, but you can go back and make the necessary changes. If you choose to import data into an existing table, you must make sure that the data types of the fields are compatible. If you attempt to import a text string into a number field, an error will occur.

It's important to remember that Access does not let go of disk space on its own. This means that as time passes all the file space taken up by the data you imported will be held by your Access file, regardless of whether the data is actually still there. In that light, it's critical that you perform a compact -and-repair operation on your Access database regularly to ensure that your database does not grow to an unmanageable size or, even worse, become corrupted. To compact and repair your database, click the Office icon and select Manage> Compact and Repair Database.

Importing Data from an Excel Spreadsheet

You can import data from a wide variety of sources into Access tables: Excel spreadsheets, text files, or other database tables. Access provides a set of easy-to-use Import wizards that will guide you through the process of importing data.

In this example, you will import data from an Excel workbook provided with the sample files.

Go to the application ribbon and select the External Data tab.
Click the Excel icon in the Import group.
Select the Excel file containing the data you want to import. In this case, select the Excel file called Employee_Master2. Choose the selection next to Import the source data into a new table in the current database, and then click the OK button. This activates the Import Spreadsheet Wizard.
Go through each screen of the Import Spreadsheet Wizard, answering the questions posed and clicking Next.

When you step through the entire Wizard, you have an Access table that contains the same data as in the source Excel file.

There are a couple of things worth noting about the Import Spreadsheet Wizard. The wizard enables you to specify the first line of the dataset as the column headings. As long as you are importing a properly formatted Excel file, this option can save time. Another extremely useful feature is the ability to select a column to serve as the primary key of the table. The wizard also offers to create a primary key adding an AutoNumber field.

Note:If you select a column to serve as the primary key, the Import Spreadsheet Wizard will perform a test on the column, to ensure against blank entries or duplicate values. If there are blanks or duplicate values in the chosen column, the wizard will inform you that it cannot set that column to primary key and the table is imported anyway.

Importing Data from a Text File

Similar to the data imported from spreadsheets, the data in text files must be in a consistent format for the wizard to extract the information correctly. Typically, the data in text files is delimited (separated) by commas. Access will properly interpret this and separate the data located between the commas into their appropriate fields. Usually someone in I.T. will prepare a text file of data for the analyst, or it can be the output of a mainframe application.