External Data Access

1. External Data Access BIT 5474

2. Messaging A Schematic View of DSS Components Knowledge (KBMS) Data (DBMS) Models (MBMS) Dialog (GUI) User Source: Turban, E and Jay E. Aronson, Decision Support Systems and Intelligent Systems, 5th Ed., Prentice Hall, 1998.

3. Where are we? Automated Data Acquisition using VBA Databases SQL Data The Web HTTP Request Data Models HTML or XML Data Decision Modeling with Excel 3

4. Excel Data Retrieval We can retrieve data to Excel from a number of different external sources: 4

5. Importing Webpage Code • To import a webpage, there are a few new properties needed. With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.nvc.vt.edu/rmajor/bit5474/schedule.htm", Destination:=Range( _ "$A$1")) .CommandType = 0 .Name = "schedule" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With

6. An example webpage imported to Excel using VBA

7. Access Data Retrieval We can retrieve data from a database using the following techniques: • QueryTables • ADO, RecordSets (rs), and SQL statements 7

8. Getting Data into Excel 2013 from Access 2013 usingQueryTables Excel will let you choose which saved Queries (or VIEWS) you want to execute, or which Tables you want to retrieve data from. Drag the bottom left corner of the window to enlarge it. 8

9. Getting Data into Excel 2013 from Access 2013 usingQueryTables Next, tell Excel where you want to put the query results. 9

10. Getting Data into Excel 2013 from Access 2013 usingActive Data Objects (ADO) The second method we will use for retrieving data uses ADO. Before using ADO, you need to activate the relevant components in VBA. Go to Tools | References in VBA, then scroll down the list of available references and select the Data references shown below. 10

11. What is ADO? • ActiveX Data Objects • It is a “go-between” – an interface For us, it is an interface between Excel and Access ADO Excel Access VBA

12. ADO Object Model • With ADO, we can write relatively simple code in VBA to retrieve data from an external database • ADO has an object model – we will just use that • See object browser • Before and after

13. The Process rs results ADO process results Excel cn rs Access VBA

14. Recordsets • In general, you can: • add a record to the set • edit a record in the set • delete a record in the set • Then put the whole new Recordset back into the database • You can also: • put results in a Worksheet • populate a ComboBox • Work with the numbers in the Recordset • Etc…

15. Recordset Object • A recordset is a “set of records.” • It resides temporarily in memory and • not on a hard drive • The recordset contains either • the result of a query, or • an entire database table

16. Persistence • Databases are persistent: • They continue to exist once you have quit your program • Recordsets are not persistent: • They will go away (forever) once you end your program, close the database, etc.

17. “Opening” the Recordset • We must specify 2 items before we can Open a recordset: • The SQL statement that tells what to “grab” out of the database, and • The Connection object that tells the kind of database we have and where it is (see above) For example: rs.Open SQL, cn

18. Development Process - Recordset • Step 1. Create a new instance of the recordset object: • Step 2: Develop a connection string Dim rs as new ADODB.Recordset strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & "HokieStore.accdb;"

19. Development Process - Recordset • Step 3: Configuring the Cursor Properties • A cursor is used to: • control record navigation • control the visibility of changes in the database • control the updatability of data • We assign values to the following three properties: • CursorLocation • CursorType • LockType

20. Development Process - Recordset • Step 3.1: Determine the best cursor location • The CursorLocation property sets or returns a long value that indicates the location of the cursor service. • adUseServer (default) • adUseClient • Uses a client-side cursor supplied by a local cursor library. • Use this option for our class rs.CursorLocation = adUseClient

21. Development Process - Recordset • Step 3.2: Determine the best cursor type • The CursorType property sets or returns the cursor type to use when opening a Recordset object. • adOpenForwardOnly (default) • adOpenStatic • Allows us to move back and forward through the recordset. • Use this option for our class

22. Development Process - Recordset • Step 3.3: Determine the best lock type • The LockType property sets or returns a value that specifies the type of locking when editing a record in a Recordset. • adLockReadOnly (default) • adLockPessimistic • Allows us to change values • Use this option for our class

23. Development Process - Recordset • Step 4. Create the query string: • Step 5: Develop a connection and populate the recordset (use the open method) strSQL = "Select * FROM Customers" rs.Open strSQL, strConnect, adOpenStatic, adLockPessimistic

24. Recordset Properties and Methods • Step 6. Leverage the Recordset Object • Properties • RecordCount, EOF, BOF, CursorType, etc. • Methods • MoveFirst, MoveNext, MoveLast, MovePrevious

25. Development Process - Recordset • Step 7. Close Connections • Step 8: Free up system resources rs.Close Set rs = Nothing

26. Once a recordset is “opened,” it is easy to go through its records with a Do loop such as the following: With rs Do Untilrs.EOF Statements .MoveNext Loop End with

27. Database and SQL Background Let’s have a brief review of relevant topics. 27

28. Relational Databases • Purposes of Databases • Store data efficiently • Allow users to request data they want, in a suitable form • Create Information!!! • Two main types • Desktop (e.g., Microsoft Access) • RDBMS – server based (e.g., Microsoft SQL Server; Oracle)

29. A ‘Flat File’ Database RecNo Name Address City State Zip Product Units Amount 1 John Smith 221 Main St. New York NY 08842 Television 1 500 2 William Chin 43 1st Ave. Redmond WA 98332 Refrigerator 1 800 3 William Chin 43 1st Ave. Redmond WA 98332 Toaster 1 80 4 Marta Dieci 2 West Ave. Reno NV 92342 Television 1 500 5 Marta Dieci 2 West Ave. Reno NV 92342 Radio 1 40 6 Marta Dieci 2 West Ave. Reno NV 92342 Stereo 1 200 7 Peter Melinkoff 53 NE Rodeo Miami FL 18332 Computer 1 1500 8 Martin Sengali 1234 5th St. Boston FL 03423 Television 1 500 9 Martin Sengali 1234 5th St. Boston FL 03423 Stereo 1 200 10 Martin Sengali 1234 5th St. Boston FL 03423 Radio 1 40 11 Martin Sengali 1234 5th St. Boston FL 03423 Refrigerator 1 80

30. A ‘Relational’ Database Customers Table: CusNo Name Address City State Zip 1 John Smith 221 Main St. New York NY 08842 2 William Chin 43 1st Ave. Redmond WA 98332 Orders Table: CusNo Product Units Amount 1 Television 1 500 2 Refrigerator 1 800 2 Toaster 1 80 3 Television 1 500 3 Radio 1 40 3 Stereo 1 200 4 Computer 1 1500 5 Television 1 500 5 Stereo 1 200 5 Radio 1 40 5 Refrigerator 1 80

31. Problems with a “flat” database - revisited • Redundancy (i.e. data duplication) • Multiple value problems • We could have Order1, Order2,… • Or, we could have multiple orders in a cell • Update anomalies • Updating a single customer’s phone number requires you to update multiple entries • Insertion anomalies • You cannot insert information about an customer until we have acquired the customer data • Deletion anomalies • If there is a single order for a particular customer, and for some reason we decide to remove the record about the customer, we lose information about the order too

32. One solution – the Relational Database Management (RDBMS) model • Data in the form of tables • Idea introduced in 1970 by E. F. Codd • Based on mathematical foundations of relational algebra and calculus • Commercial databases began to appear around 1980

33. SQL - Structured Query Langauage • A widely used language for creating, manipulating, analyzing and searching through databases. • We’ll just stratch the surface.

34. The SELECT Statement SELECT fieldlist FROM recordsource WHERE searchcondition ORDER BY sortorder

35. The DELETE FROM Statement DELETE FROM tablename WHERE condition;

36. The UPDATE Statement UPDATE tablename SET fieldname = newvalue WHERE condition;

37. The INSERT INTO Statement INSERT INTO tablename (fieldlist) VALUES (valuelist);

38. The CREATE TABLE Statement CREATE TABLE tablename (field1 type (size), field2 type (size) [NOT NULL], … );

39. The ALTER TABLE Statement ALTER TABLE tablename ADD COLUMN fieldname type (size); or ALTER TABLE tablename Drop COLUMN fieldname;

40. Key Recordset Properties & Methods .AddNew .AbsolutePosition .BOF .EOF .Delete .Edit .Fields .MoveFirst .MoveLast .MoveNext .MovePrevious .FindFirst criteria .FindLast criteria .FindPrevious criteria .FindNext criteria .NoMatch .RecordCount .Update