1 / 21

Visual Basic Database Access

Visual Basic Database Access. BICS546. Microsoft Universal Data Access. OLE DB: The OLE database protocol Allows a program to access information in any type of data source, and it is the basis for ADO. Data provider: databases, spreadsheets, etc. ADO: ActiveX Data Objects

art
Download Presentation

Visual Basic Database Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Visual Basic Database Access BICS546

  2. Microsoft Universal Data Access • OLE DB: The OLE database protocol • Allows a program to access information in any type of data source, and it is the basis for ADO. • Data provider: databases, spreadsheets, etc. • ADO: ActiveX Data Objects • An Interface for OLE DB. • Allow programmers to use a standard set of objects to refer to any OLE DB data source. • ODBC: Open Database Connectivity • A driver manager • Used for relational databases

  3. Program ActiveX Data Objects OLE DB Provider OLE DB Provider ODBC Data Source ODBC Data Source

  4. Using ODBC • Three types of data source names • User DSN: usable only by you and only on the machine currently using. • System DSN: Any one using the machine can use. • File DSN: Can be copied and used by other computers with the same driver installed.

  5. Visual Basic Database Tools • Visual Data Manager • Data Environments • Data Views • Data Report Designer • Data Projects

  6. Visual Data Manager • Add-Ins/Visual Data Manager • Supports: Access, dBase, FoxPro, Paradox, ODBC, Text files. • Creating new database • Managing existing database • Query Builder, Form Wizard • DAO

  7. Data Environments • Project/More ActiveX Designer/Data Environment • Data Environments are designed to be the basis of design-time and run-time data access. They make data available to any forms and modules in your application.

  8. Data Environments at Design Time • Right Click Connection1 • Click Properties to set up database connection • Click Add Command to choose record source. • Drag and drop fields from Command1 to form. Text box properties: • Data Source: Data Environment1 • Data Member: Command1 (Note: Data environment can support many command objects) • Data Field

  9. Data Environments at Run Time • Access command object’s Recordset object and Recordset object’s methods. • Recordset’s name: rs+command name. • Ex. rsCommand1 • Properties: • EOF, BOF, RecordCount • Methods: • MoveFirst, MoveLast, MoveNext, MovePrevious

  10. Data Environment Code Example DataEnvironment1.rsCommand1.MoveNext IF DataEnvironment1.rsCommand1.EOF THEN DataEnvironment1.rsCommand1.MoveLast END IF

  11. Data Views • View/Data View Window • An overview of the database objects you have in a project, such as Data Environments, connections, data links.

  12. Data Report Designer • Project/Add Data Report • Features of Data Report Designer • Drag and Drop fields from the Data Environment to report detail section. The DataMember and DataField properties of the text box are automatically set. • Drag and Drop the Command object (Must make sure the DataSOurce and DataMember properties of the DataReport are set properly).

  13. Data Report Code Example ‘To preview report DataReport1.Show ‘To print DataReport.PrintReport

  14. Data Projects • VB projects come with built-in Data Environments and Data Report Designer.

  15. Using Databases in VB • Three VB database objects: • DAO, RDO, ADO • Two ways to work with these objects: • VB Data controls, data-bound controls • Programming objects

  16. DAO • Support the Jet database • DAO data control • Database property • RecordSource property: tables, queries, SQL statement • RecordSet Type Property: Table, Dynaset, Snapshot • Able to connect to data sources such as dBase, text files, and Excel spreadsheet.

  17. ADO Data Control • Project/Components/Microsoft ADO 6.0 • Double Click ADO to insert ADO • Right Click and select ADODC Properties • General : Source of Connection • RecordSource: • 1 – adCmdText: SQL statement • 2 – adCmdTable: Table in the database • 4 - adCmdStoredProc

  18. ADO Properties • BOF Action, EOF Action • Command TimeOut: Amount of time in seconds the ADO control will allow for commands to be executed on the server. • ConnectionString • CursorLocation: server site, client site • CursorType • MaxRecords: 0 means no limit. • RecordSource

  19. Cursor Type • Forward Only: Can only move forward through the recordset (can only MoveNext). Use the least amount of system resources. • Static: Can navigate in both directions. Unable to see changes made by other users. • Keyset: Can see changes made by other users (except insertions). • Dynamic: Use most of resources, and can see all changes made by other users.

  20. Cursor Lock Type • ReadOnly • Optimistic: Does not lock the file until updates are actually applied. • BatchOptimistic: All changes be submitted at one time. • Pessimistic: Lock the table the entire time that the recordset is open.

  21. Cursor Mode Property • Determines the access rights of the user. • Read • Write • ReadWrite • ShareDenyRead: Deny other from opening connections with read permissions. • ShareDenyWrite • ShareExclusive • ShareDenyNone: Prevent others from opening conections with any permissions.

More Related