ADO Data tan.tplt 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 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