Chapter 10: Moving Old Data into a New Database

There are two situations where you need to move old data into a new database: (1) When you are reworking an application that was developed in a non-normalized fashion, and you make a fresh start by creating a brand-new database with properly normalized tables, and then need to import data into the new tables from the old, non-normalized tables, and (2) when you develop a new Access application, and need to import data into it from another application such as dBASE or Excel, possibly after that data has been exported to an intermediate format such as comma-delimited text files.

In both cases, you can use a variety of techniques, involving both queries and VBA code, to extract the data you need from the old tables or text files and place it into the appropriate normalized tables.

Getting at the Old Data

Whether your old data is in the form of Access tables in another database or external files of some sort, the first step is to link to these tables (or import, if you prefer) to bring the data into your application, where you can work with it. For the Toy Workshop sample database, the client has sent me an Excel worksheet containing customer data entered before the Access database was created. I need to either link to this worksheet or import data from it into a new Access table so that I can work with the data. Since I only need to pull the Excel data into my new tables once, I will import the data in order to have full read-write access to all the fields in the table.

To import the worksheet, select Tables in the object bar, and click the New button in the datasheet window. Select Import Table in the New Table dialog, as shown in Figure 10.1.

Figure 10.1

Select the appropriate file format (in this case, Microsoft Excel (*.xls)) in the Files of type selector in the Import dialog, browse to the folder where the file is located, select it and click the Import button, as shown in Figure 10.2.

Figure 10.2

The Import Spreadsheet Wizard opens; generally, you can accept the defaults throughout the wizard, checking First Row Contains Column Headings on the first page, as shown in Figure 10.3.

Figure 10.3

You can check the No primary key option on the next to last page of the wizard because Access will create its own primary key when new records are added to tblCustomers. On the last page of the wizard, give the table the name tblRawCustomers (the word “Raw” in front of the base name of a table is my convention for marking raw data for import into normalized tables), and click Finish. Figures 10.4 and 10.5 show tblRawCustomers in Datasheet view. The first figure shows the name and part of the main address data; the second shows some of the phone numbers and email addresses.

Figure 10.4

Figure 10.5

In addition to the main address, there are also two sets of fields for shipping addresses. Data from tblRawCustomers needs to be appended to several linked tables. Figure 10.6 shows a portion of the Relationships diagram showing how the normalized tables are linked.

Figure 10.6

A simple append query (with some calculated fields to do splitting and concatenating as needed) will suffice to add the new customer records to tblCustomers, but to add new records to the linked tables, VBA code is needed. I’ll discuss both of these methods in the next sections.