outline

Data storage schemes

• Sequential Access
• Random Access
• Indexed RA
• RDBMS
• OODBMS

VB Data control

• A VB control
• Connect to database
• Attach to bound controls
• Controlled entirely through VB
• Divorced as much as possible from DB

Advantages of Data control

• Very easy
• simple control binding
• variety of databases

Disadvantages of data control

• Resource hog
• Clumsy with multiple DBs
• Limited SQL
• No support for building databases or tables

Data Access Objects (DAO)

• Object, not a component
• OOP encapsulation of DB
• How data controls worked under the hood
• Recordset = collection of records
• Record = collection of fields

Typical recordset methods

• moveFirst, moveLast
• moveNext, movePrevious
• eof()
• count()
• findFirst

SQL methods of the database object

• getRecordset(query)
• returns a new recordset
• execute()
• Used for data definition, such as insert, update

Advantages of DAO

• Much better flexibility
• More efficient than data controls
• Programmer has more control
• Can use SQL to manage database

Disadvantages of DAO model

• No binding to controls - must be done by hand
• useful only for local databases of specified types
• requires RDO for remote data access
• ... and ODBC for ODBC connectivity

Enter Activex Data Objects / Controls

• Attempt to unify ActiveX technology ...
• with previous DB technologies
• "unified model" of DB access
• Both a set of controls AND DAO-Like objects
• NOT directly compatible to DAO, DAO data controls
• New features integrated into VB itself

The ADO Data control

• NOT the same as the data control you've been using
• Find it under the toolbox options
• It will look something like "adodc1"
• The properties will be much like the DAO DC
• ..but not exactly.

Setting up a connection:

• Click on elipsis (...) of connectionString property
• connection types
  • data link
  • ODBC source
  • Connection string
• For now, we'll choose connection string, and click on 'build'

Use the resulting database to set up a provider type

• Then choose an existing database name (if it exists)
• You can also use this dialog to set up a relationship with an existing
• server (for example to connect to oracle db on ds9)
• This dialog will be different for the different types of connections
• There will be a 'test connection' button somewhere. You should run
• the test.

Setting the recordsource

• Once the connection is made, examine the recordsource property
• Choose commandType (table, text, stored procedure)
• If it's a table or stored procedure, choose it
• If it's a text command, type in the SQL

Binding controls

• Just like DAO -
• Set each control's dataSource to the ADO control
• Set the data Fields appropriately

Additional properties and events of ADO control:

• BOFAction, EOFAction describe what to do when these events occur
• willChange - a number of events useful for data validation
• endOfRecordset - the user tried to go past recordset
• error - some data error occurred

Useful ADO methods (most are inherited from DAO)

• MoveFirst, MoveLast, MoveNext, MovePrevious
• Update
• Delete
• Addnew

Improvements in databound controls

• Data source isn't only ADODC!
• Some controls can be directly bound
• Databindings property
• DataCombo, DataList
• DataGrid
• (note that DBx is usually NOT ADO-aware, and dataX usually is)

Data Environments

• Way to manage data visually
• Multiple data sources simultaneously possible
• Data Environment -> connection object
• connection object -> command
• command ->recordset
• All else is just like DAO

Data Command Shortcuts!

• Once you have a data command specified,
• Drag a table, procedure, or field to a form
• A label and bound text box will automatically
• be generated. (!)

Setting up a command to be a specific table

• (presuming myCommand is existing command)
• myCommand.commandType = adCmdTable
• myCommand.commandText = "People"
• set myRecordset = myCommand.execute

Setting up a command to be results of an SQL query

• (presuming myCommand is existing command)
• myCommand.commandType = adCmdText
• myCommand.commandText = "Select LName, FName from people"
• set myRecordset = myCommand.execute