A database can be set up by importing it from another source, for example an Excel spreadsheet. The Excel spreadsheet does not need to have been created as a database table but this may well be the case. You might have a worksheet in Excel that contains data you have collected on some topic and you may even have used the database facilities in Excel to run queries and reports from it. You then decide that you need to transfer the data to Access so that you can use the features of a relational database to develop the application further.
The easy way to set up the database in Access is to import it. This can be demonstrated with an example, the GCSE results spreadsheet. In Access create a new database and choose File/Get External Data/Import. This launches the Import wizard. The first step is to select the file from which you will import data. The default file type is Access (MDB) because importing from an existing database is a common action. For example, you might obtain a database from a friend and want to use part of it, in which case importing one or more tables from it will be the quickest way. In this example we change the file type to Excel and identify the data source. We then move to the second screen where we can choose the worksheet containing the data:
Here there is only one worksheet and no named data ranges so we simply click Next.
We know that the first row of the spreadsheet contains column headings so we tick the box for this. In this case the wizard throws up an error message because there are some columns (AB, AC) that do not contain data like the others:
We accept this on the basis that we can put right any problems later. The next stage asks whether we want to use an existing table or create a new one.
In this case we want a new table. The next step allows us to add or edit field names but we do not need to do this as we can work with the ones in the first row of the spreadsheet.
The next step allows the addition of a primary key. If you choose to let Access add a primary key then a new field will be added as follows:
If you choose one of the existing fields then Access does not add a new field:
The final step allows you to enter a name for the new table:
The table is now in Access and can be edited in Design mode. The wizard in this case has picked up three extra fields from columns AA, AB and AC, these can be deleted.