Implementing the process of table creation:
Let’s say we have some data in Excel.
We start MS Access.
Now we exit our Excel program if it is open.
Start MS Access. Click on the Office button in MS Access and click on ‘New’.
We’ll name our new database ‘Database2’ and click on ‘create’.
We now see the first table ‘Table1’ of our new database. A database as you know, is just a collection of organized tables from which we later create queries, reports, etc.
Now we’ll get the data from Excel into Access. Click on the External Data tab. Select ‘Excel’ from the ‘Import‘ command group.
We browse for our file.
We select the relevant file and click on ‘Open’.
Next we slect ‘Import the source data into a new table in the current database’ and click OK.
We are able to view our Sheet1 data.
Click on the Next button.
We check the item ‘First Row Contains Column Headings’ and click Next.
Click again on Next without making any modifications.
We’ll not assign a primary key and click on Next.
Under the header ‘Import to Table:’ we’ll give a name ‘Employee_Data’ and click the Finish button.
MS Access gives a message called ‘Save Import Steps’, We ignore it for the moment and click on the Close button.
We can now see that a new table called ‘Employee_data – Table’ has been created. When we double-click on it we can view the data.
Click on the Home tab and select the ‘Design View’
In the design view we can modify certain things like we can change the ‘Pin Code’ from the number data type to a text data type.
We then create a new field name called EmployeeID, assign it the data type ‘AutoNumber’ and make it the primary key.
Finally we save our table. We can now see that EmployeeIDs of the two records have been assigned the numerical values one and two.
This is indeed an interesting and easy way to create tables in Access via Excel.
Creating a new Access database from an Excel spreadsheet