Transcript Database Communication

External Data Access
BIT 5474
A Schematic View of
DSS Components
Knowledge
(KBMS)
Data
(DBMS)
Messaging
Models
(MBMS)
Dialog
(GUI)
User
Source: Turban, E and Jay E. Aronson, Decision Support Systems and Intelligent
Systems, 5th Ed., Prentice Hall, 1998.
Where are we?
Databases
Automated Data
Acquisition using VBA
The Web
Data
Models
Decision Modeling with Excel
3
Excel Data Retrieval
We can retrieve data to Excel from a number of
different external sources:
4
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
5
 An
example webpage imported to Excel
using VBA
6
Access Data Retrieval
We can retrieve data from a database using the
following techniques:
• QueryTables
• ADO, RecordSets (rs), and SQL statements
7
Getting Data into Excel 2013 from Access 2013 using
QueryTables
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
Getting Data into Excel 2013 from Access 2013 using
QueryTables
Next, tell Excel where you want to put the query results.
9
Getting Data into Excel 2013 from Access 2013 using
Active 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
What is ADO?
 ActiveX
Data Objects
 It is a “go-between” – an interface
ADO
For us, it is an interface between Excel and
Access
VBA
Excel
Access
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
ADO
The Process
VBA
process
results
Excel
rs results
rs
cn
Access
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…
14
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
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.
16
“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
Development Process - Recordset
 Step
1. Create a new instance of the
recordset object:
Dim rs as new ADODB.Recordset
 Step
2: Develop a connection string
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=" & Path & "HokieStore.accdb;"
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:
1. CursorLocation
2. CursorType
3. LockType
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
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
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
Development Process - Recordset
 Step
4. Create the query string:
strSQL = "Select * FROM Customers"
 Step
5: Develop a connection and
populate the recordset (use the open
method)
rs.Open strSQL, strConnect, adOpenStatic, adLockPessimistic
Recordset Properties and
Methods
 Step
6. Leverage the Recordset Object
– Properties
 RecordCount,
EOF, BOF, CursorType, etc.
– Methods
 MoveFirst,
MoveNext, MoveLast,
MovePrevious
Development Process - Recordset
 Step
7. Close Connections
rs.Close
 Step
8: Free up system resources
Set rs = Nothing
 Once
a recordset is “opened,” it is easy to
go through its records with a Do loop such as the
following:
With rs
Do Until rs.EOF
Statements
.MoveNext
Loop
End with
Database and SQL Background
Let’s have a brief review of
relevant topics.
27
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)
A ‘Flat File’ Database
RecNo Name
1 John Smith
2 William Chin
3 William Chin
4 Marta Dieci
5 Marta Dieci
6 Marta Dieci
7 Peter Melinkoff
8 Martin Sengali
9 Martin Sengali
10 Martin Sengali
11 Martin Sengali
Address
221 Main St.
43 1st Ave.
43 1st Ave.
2 West Ave.
2 West Ave.
2 West Ave.
53 NE Rodeo
1234 5th St.
1234 5th St.
1234 5th St.
1234 5th St.
City
State
New York NY
Redmond WA
Redmond WA
Reno
NV
Reno
NV
Reno
NV
Miami
FL
Boston FL
Boston FL
Boston FL
Boston FL
Zip
08842
98332
98332
92342
92342
92342
18332
03423
03423
03423
03423
Product Units Amount
Television
1
500
Refrigerator 1
800
Toaster
1
80
Television
1
500
Radio
1
40
Stereo
1
200
Computer
1
1500
Television
1
500
Stereo
1
200
Radio
1
40
Refrigerator 1
80
Customers Table:
A ‘Relational’ Database
CusNo Name
1
John Smith
2
William Chin
Address
221 Main St.
43 1st Ave.
City
New York
Redmond
State
NY
WA
Orders Table:
CusNo
1
2
2
3
3
3
4
5
5
5
5
Product
Units
Television
1
Refrigerator 1
Toaster
1
Television
1
Radio
1
Stereo
1
Computer
1
Television
1
Stereo
1
Radio
1
Refrigerator 1
Amount
500
800
80
500
40
200
1500
500
200
40
80
Zip
08842
98332
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
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
SQL - Structured Query Langauage
 A widely
used language for creating,
manipulating, analyzing and searching
through databases.
 We’ll just stratch the surface.
The SELECT Statement
SELECT fieldlist FROM recordsource
WHERE searchcondition ORDER BY sortorder
The DELETE FROM Statement
DELETE FROM tablename WHERE condition;
The UPDATE Statement
UPDATE tablename SET fieldname = newvalue
WHERE condition;
The INSERT INTO Statement
INSERT INTO tablename (fieldlist) VALUES (valuelist);
The CREATE TABLE Statement
CREATE TABLE tablename (field1 type (size),
field2 type (size) [NOT NULL], … );
The ALTER TABLE Statement
ALTER TABLE tablename ADD COLUMN fieldname type (size);
or
ALTER TABLE tablename Drop COLUMN fieldname;
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