Purpose: The purpose of this assignment is to become proficient in • Constructing a database in MS Access • Connecting a Visual Studio Application to a database and create a Dataset • Using the DataGridView control to display a Dataset • Bind controls to a Dataset
Assignment
1) Create a database a) Download the Excel file Online Grocer Data.xlsx from the Canvas site. b) Create named ranges for the three tables in the spreadsheet c) Open MS Access and create a blank database d) Import the named ranges of the spreadsheet to an MS Access database. Repeat the following steps for each table of the spreadsheet that you want to import to MS Access i) Go to External Data>New Data Source>From File>Excel ii) Browse for the Excel file and Import the source data into a new table in the current database iii) Next window. Show worksheets. Select a worksheet. iv) Next window: For each worksheet check First Row Contains Column Headings v) Next window: For each field, select data type vi) Next window: Choose “let Access add primary key” vii) Next window: Import to table — the table name should be the same as the Excel sheet name viii) Next window: Close. A table will be created in the MS Access database that shows the data that is on the corresponding Excel sheet e) Name the MS Access database HW3TeamXXDB, where XX is your team number. Save the database as a 2002-2003 version of MS Access. This file should have a suffix of “.mdb”.
2) Create a Query in MS Access a) Go to Create>Query Design. b) Build a Select Query that links the three tables: Category Table, Inventory Table, Product Table c) Make a one-to-many relationship from the Category Table to the Product Table by linking the CategorylD fields. d) Make a many-to-one relationship from the Product table to the Inventory table by linking the SKU fields. e) Show the following fields in the Query: i) Category Description from the Category Table ii) Product ID, Product Description and Price from the Product Table iii) SKU and Available from the Inventory Table iv) Name the query “QueryCategoryProductInventory”