Lab 8 Data Access Using Microsoft ActiveX Data Object (ADO)

1. Lab 8 Data Access Using Microsoft ActiveX Data Object (ADO)

2. Why ADO? • ADO is used to import data from Access or other database files into Excel applications.

3. Database Terminology • Field– single item of information about a person, place, or thing • Record– group of related fields that contain all of the information about a person, place, or thing • Table– group of related records; each record contains the same fields • Relational database – one or more tables which are related

4. Open a Connection(1) • Connection object sets up a link between your program and the data source.  • This object contains all of the necessary configuration information and acts as a gateway for all of the other ADO objects.  Step 1: Declare a Connection Object Variable and Assign a new Connection to it Dim cn As ADODB.Connection Set cn= New ADODB.connection Step 2: Specify ConnectionString cn.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\newsvendor.mdb"

5. Open A Connection (2) Step 3: Specify Data Provider cn.Provider = "Microsoft Jet 4.0 OLE DB Provider" Step 4: Open the Connection cn.Open

6. Open a Recordset • A Recordset is a temporary database table which contains the results of a query or an entire database table. Step 1: Declare a Recordset Object Variable and Assign a new Recordset to it Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Step 2: Specify ConnectionString rs.Open “Demand", cn where “Demand” is a table name.

7. Recordset and Field Objects The select * from demand command will create the following Recordset object, Field objects (rs.open “SELECT * FROM demand”) Recordset object: All records stored in the Demand table Field objects:ID, DailyDmd 2 Field objects • Each field contained within the Recordset object is referred to as a Field object • To refer to a field: rs.Fields(“field”) e.g. rs.Fields(“ID”)

8. Methods of the Recordset Object AddNew Add a new, blank record to the end of the recordset CancelUpdate Cancel changes made to the current record Delete Delete the current record from the recordset MoveFirst Move the record pointer to the first record in the recordset MoveNext Move the record pointer to the next record in the recordset Update Save the changes made to the current record

9. Syntax of Recordset Object’s Methods and Properties • The syntax for invoking a method of the Recordset object is: rs.method • The syntax for referring to a property of the Recordset object is: rs.property.

10. Recordset Object’s EOF Property EOFTest for the end of the recordset The EOF property contains True if the record pointer is positionedafterthe last record in the recordset; otherwise, the property contains False

11. EOF Example Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open “Demand", cn Do Until rs.EOF count = count + 1 rs.MoveNext Loop This example counts the number of records in the Recordset. It quits when the end of the file is reached. MoveNext is a method of Recordset, which is used to move to the next record in the Recordset.

12. Close Method • A Recordset object should be closed when it is no longer needed. rs.Close • Once you finish accessing or modifying the database, the connection should be closed. cn.Close

13. Exercise • Download NewsvendorSimulation.xls and newsvendor.mdb from blackboard. Look at the work sheet “Data”. You have to connect to the database and fill in the fields “Day” and “Demand” on the worksheet with the data “ID” and “DailyDmd” from the “demand” table. • In the Data worksheet, Fill the “Number Of Days” Column. You have to count the number of days when demand was equal to 0,1, 2 and 3 in column B. Use the COUNTIF formula of Excel. In cell E10 you have to count the total number of days. • Fill in the Probability column. Probability = (no.of days/total no. of days). E.g.: F6 = E6/E10. • In cell F10 you have to calculate the total probability.