1 / 35

Chapter 11

Chapter 11. Accessing Database Files. Microsoft Access dBASE III, IV, V Excel FoxPro Lotus Paradox. SQL Server Oracle DB2. Database Formats Supported. Databases Tables Records Fields Primary Key Foreign Key Relationships. Database Terminology. Relational Databases.

giona
Download Presentation

Chapter 11

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. Chapter 11 Accessing Database Files

  2. Microsoft Access dBASE III, IV, V Excel FoxPro Lotus Paradox SQL Server Oracle DB2 Database Formats Supported

  3. Databases Tables Records Fields Primary Key Foreign Key Relationships Database Terminology

  4. Relational Databases • Database includes • Tables - collection of related data • Queries - SQL designed to select data from tables • Table • Record - row of related data for one instance • Field - column of specific data element • Keys • Primary - uniquely identifies a record • Foreign - links record to related record in related table

  5. Creating New Access Databases • Use Microsoft Access • Use VB's Visual Data ManagerAdd-In • Add-Ins menu, Visual Data Manager • VisData Application capabilities • Create a new file • Modify an existing file • VisData Includes • Query Builder Utility • DataForm Designer

  6. ADO • Active Data Object • Microsoft's latest database object model • There have been many in previous releases, some still compatible withVB 6, some not • You should use ADO rather than the older models (DAO, RDO)

  7. ADO Data Control Connections (ADODC) • Add active data control to toolbox • Project, Components, Microsoft ADO Data Control 6.0 (OLEDB) • Add data control to form • Set properties • Prefix - ado • Custom Property Pages - Build Connect String • Connect data aware controls to data control

  8. Data Aware/Data Bound Controls • Controls bound to database fields • Linked to the database using ADODC • Intrinsic Controls • labels • text boxes • check boxes • images • picture boxes • list boxes • combo boxes • data-bound list boxes • data-bound combo boxes • data-bound grids

  9. ADODC Properties (pp 447-451) • Connection String • OLE DB Provider (ex: Jet 3.5.1 or Jet 4.0) (p 99) • Database path and filename • Optional userid and password • RecordSource • Name of Table, Stored Procedure, or SQL statement • Command type (ex: adCmdTable, adCmdStoredProc, adCmdText) • Access Permissions (ex: read, readwrite)

  10. Linking Data Aware Controls • Intrinsic controls • DataSource ===> ADODC • DataField ===> field in source

  11. Navigating the Database • Use the movement buttons on the ADODC to move through the database records OR • Set the ADODC's Visible property to False and write code for command buttons to move through the records using the Recordset object

  12. Recordset • Object that contains a set of records from ADO data control • RecordSource property of the data control determines the recordset • Recordset has properties and methods

  13. Using Recordset Object • Object in ADODC hierarchy • Has methods and properties to use for record movement and to determine EOF and BOF

  14. Recordset Object - Move Methods Code Examples adoBooks.Recordset.MoveFirst adoBooks.Recordset.MoveLast adoBooks.Recordset.MoveNext adoBooks.Recordset.MovePrevious adoBooks.Recordset.EOF adoBooks.Recordset.BOF

  15. EOF and Move Next • Be careful not to move past EOF Private Sub cmdNext_Click( ) With adoBooks.Recordset .MoveNext If .EOF then .MoveFirst End IF End With End Sub

  16. BOF and Move Previous • Be careful not to move past BOF Private Sub cmdPrevious_Click( ) With adoBooks.Recordset .MovePrevious If .BOF then .MoveLast End IF End With End Sub

  17. Lookup Table for a Field • Use a combo box to display a list of acceptable values for a field • Populate the combo 1 of 2 ways • Enter the list of values in the List property at design time • Use the AddItem method in Form_Load at run time • When the user selects an item from the combo's list update the database field

  18. Updating a Database File • Modify existing records • Add records • Delete records

  19. Modifying Records • With bound controls, saving changes is practically automatic(if ADODC's ReadOnly=False) • Changes to record saved when user moves to another record (like Access) • Explicitly save changes in code using the Recordset's Update method

  20. Adding Records Automatically • ADODC • Set EOFAction property to adDoAddNew in Properties Window • At EOF, when user clicks next record • VB automatically begins Add operation • VB clears bound controls • When user clicks arrow button, the Update method is automatically executed and the new record is written to the file

  21. Disadvantages of Using ADODC for Add • No opportunity for data validation of field values • If user enters invalid data you will not be able to easily trap the error and the program will terminate • If user decides not to add the record there is no way for them to cancel

  22. Adding Records Manually • AddNew method clears all bound controls (New Record cmd button) • After the user enters the data, perform data validation, write to file using Update method (Save Record cmd button) adoBooks.Recordset.AddNew adoBooks.Recordset.Update

  23. Typical Add Record Problems • Primary Key Field must be entered or a runtime error occurs • Make sure you have provided a textbox for the user to enter the Primary Key or create the Key programatically • Use data validation to verify the validity of the primary key and all other data

  24. Protecting an Add Record (P468-471) • When the user clicks the New Record/Add Record cmd button • Enable the Save cmd button • Enable the Cancel cmd button or just change the caption of Add to Cancel and use code (If structure or Select Case) to check the caption and decide what to do • Disable all other navigational and operational buttons

  25. Deleting Records • Delete method deletes the current record • Always follow with a Move method since the current record is no longer valid after it has been deleted! With adoBooks.Recordset .Delete .MoveNext End With

  26. Delete Considerations • What if the record deleted is the last record? • What if the record deleted was the only record?

  27. Better Delete Code With adoBooks.Recordset .Delete .MoveNext If .EOF Then .MovePrevious If .BOF Then MsgBox "The recordset is empty" End If End If End With

  28. Tips For Preventing User Errors • Set TextBox's MaxLength equal to database field length • Use TextBox's Lock property when you don't want the user to change data, like primary key when not adding a record • Once an Add has begun disable all navigation buttons (see code example pages 468-471)

  29. Typical Database Errors • Entering data that violates rules • Empty key field • Duplicate key field • Violates referential integrity

  30. Additional Special Topics • Connection String Considerations • Opening a Database • Closing a Database

  31. Connection String Considerations • The ADODC's ConnectionString can be set using the custom property pages • If so ==> the connection string contains a "hard-coded" path to the database file • If the database file is moved, the ConnectionString is no longer valid • Overcome this problem by using the App.Path object as we did for Sequential and Random files

  32. ConnectionString Code Example adoBooks.ConnectionString= "Provider=Microsoft.Jet.OLEDB.3.51; Persist Security Info=False; Data Source=" & App.Path & "\Biblio.mdb; Mode=Read" App.Path points to the folder/directory in which the VB Project is saved. The database file must be saved in the same folder for this code to work.

  33. Open or Reopen a Database (ADODC) • Use the Refresh method of the ADO control to open or reopen a database adoBooks.Refresh

  34. Ex: Writing Code to Open a Database using ALL Custom Properties of the ADODC Private Sub Form_Load( ) With adoBooks .ConnectionString="Provider=Microsoft.Jet.OLEDB.3.51;" & _ "Persist Security Info=False;" & _ "Data Source=" & App.Path & "\AVB.mdb; " & _ "Mode=Read" .CommandType=adCmdTable .RecordSource="Patient" .Refresh End With Note: If you type the ConnectionString on one line in the code window, the & and _ are not needed!

  35. Close a Database (ADODC) • Use the Close method of the ADO control to close a database • Usually place this code in Form_Unload of the form that opened the database or the main form's Unload event adoBooks.Recordset.Close

More Related