1 / 22

Topic 12: Database and Visual Basic

Topic 12: Database and Visual Basic. Learning outcomes. By the end of this topic, you should be able to: 1. State the relationship between entities and attributes in database ;

afra
Download Presentation

Topic 12: Database and Visual Basic

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. Topic 12: Database and Visual Basic

  2. Learning outcomes • By the end of this topic, you should be able to: 1. State the relationship between entities and attributes in database; 2. Explain the relationship between Database Management System to Entity Relationship Diagram, Structured Query Language and database software 3. Manipulate data through Structured Query Language; and 4. Construct a program by combining MS Visual Basic and MS Access

  3. Introduction • In this topic, you will learn the techniques of combining two separate software to develop a system which is a combination of VB programming language, software and database software. • Normally, VB will act as an interface. However, MS Access is used as the database. Combining VB and MS Access makes the computer much better and more user-friendly.

  4. Database • Database is a collection of data that is stored by category. The categories are linked by the type of data stored. Database is also defined as a collection of data about related entities. Its application is to assist the process of collecting, storing and accessing data effectively.

  5. Data Base management System (DBMS) • Database Management System (DBMS) is a system that exists specifically to manage the database. • DBMS makes sure that the data entered into the database can be viewed, manipulated, opened and accessed easily. There are many programming languages that are developed along with DBMS. Among them are Informix and Oracle.

  6. Advantages of DBMS • DBMS has many advantages. Among them are: • Data is easily viewed by user; • Easy to access; and • Easily manipulated by the user to generate information using SQL instructions. • DBMS such as Informix has three main interfaces for users: • Run SQL language; • Choose attributes in table through menu; and • Write programs that involve data from the database. • All three interfaces have the convenience of being able to generate information from the results of the run mode. This can be done easily

  7. Relational DBMS • There are many types of DBMS. However, the best and most often used are Relational DBMS. This type of DBMS separates data into categories that are named entities. • An entity has attributes which is data that comprise of data name and value (attribute name and value). • Each entity needs the same attribute as other entities in order for a link to be established between the entities. • For example, one entity called EMPLOYEE will contain the attributes such as NAME, STAFFNO, ADDRESS. The next entity called POSITION will have the following attribute names, STAFFNO, GROSSSALARY,andCATEGORY. • An entity will have one set of attribute names ad more than one attribute value. The Relationship Model between entities is shown in the Relationship Table and Entity Relationship Diagram (ERD).

  8. DBMS using Access • Below are a few simple steps to develop an MS Access database. (a) Create an entity. (b) Develop an Entity Relationship Diagram. (c) Open MS Access software. (d) Create table based on your entity. One entity is a table. (e) Save your database.

  9. ERD

  10. Structured Query Language (SQL) • One of the advantages of DBMS is that it supports SQL • SQL is an important language that performs on links. • It is a command to the SQL to give out the information based on the manipulation of data in the DBMS. “Select * From EMPLOYEE” • All attributes available are listed • If Record of a particular employee is needed then run this query: “Select * From EMPLOYEE where NAME = ‘Ibrahim Zahir’” “Select name, i.c from PERSONAL”

  11. Visual basic and MS Acess • VB is a programming language, while MS Access is a DBMS. Programming languages is used in developing applications, controlling flow and operations of an application. DBMS is used to for convenient storage of data and SQL. • To used MS Access and VB, you will need to create a relation first. You will be taught how to create a link between VB and MS Access with ActiveX ADODC. Follow the following steps to create a link to MS Access from VB6. (a) Open a VB project. (b) Click on Project menu then click on Components (to get an .ocx file, such discussed in chapter 11). (c) Scroll and click an ActiveX Microsoft ADO Data Control 6.0 (OLEDB). Click Apply and then OK. (d) An ADODC object is added in ToolBox

  12. (e) Click on ADODC object and bring it to the form. • (f) Click the right mouse button on ADODC and click on ADODC Property

  13. (g) Make sure the radio button Use Connection String is active and click on the Build button

  14. (h) In the menu dialogue box shown in Figure 12.9, if you are using MS Access 2000, select Microsoft Jet 4.0 OLE DB Provider. If MS Access 97 is selected, you must select Microsoft Jet 3.51 OLE DB Provider. • (i) Click on the Next button.

  15. (j) click on the button to browse for your database location. After that, click on Test Connection button. • (k) If the connection to the data is successful, a message box will appear with the message “Test Connection Succeeded” displayed. • (l) If the message does not appear, make sure that you database exists or is in the correct directory.

  16. There are two important terms in developing VB and database applications, which are RecordSource and RecordSet. RecordSource is the data source or records in the database obtained through ADODC. This data source can be obtained from a table or SQL instructions. • You can obtain RecordSource from a Tab RecordSource controlled interface dialogue menu.Ifyou click on the Tab, there are three spaces that need to be filled. They are: (a) Command Type. (b) Table or Stored Procedure Name. (c) Command Text (SQL).

  17. Command types

  18. RecordSet is a record storage location that exists as a result of the implementation of RecordSource. Below is an example record set from personel table. • A RecordSource will have the following SQL instructions: “Select name, id From PERSONAL” “Select * from PERSONAL where IC = ‘A123400’”

  19. Visual basic and MS access application • The following are the steps required to develop an application using database and VB without involving writing too many codes. Development that involves writing programs will be discussed in Topic 13. (a) Open a project and VB form. (b) Construct a database in MS Access (.mdb). (c) After you have completed the relation (follow steps in Subtopic 12.7), click on the third Tab which is “RecordSource” on the dialogue box menu (d) Select 1 - adCmdText in the Command Type section. (e) Type the SQL instructions in the Command Text (SQL). (e.g. Select * PERSONAL). (f) Return to form, click on DataGrid object in the ToolBox. DataGrid is an ActiveX object. To retrieve it, click on Project menu, click Component, and select Microsoft DataGrid Control 6.0 (OLEDB).

  20. (g) In DataGrid Properties box, fill in the information in DataSource space. The required information includes the ADODC object name that you have linked to the database. • (h) If you did not modify the object name, the ADODC object name will be Adodc1. • (i) If you do not want user to edit data in the database, select False in AllowUpdatecolumn. • (j) Run your program. Data that is displayed in DataGrid1 is similar to data obtained by SQL instructions.

  21. Data grid in forms

  22. Summary • • Application allows for easier flow of a system in an organisation. It stores and allows fast access of information. • • Without database, an application will store data in a file format. From the information point of view, this is not effective because similar files can be stored more than once. • • Apart from that, a programmer’s task is difficult and complex because of the numerous reports that need to be prepared. • • VB allows data to be stored and managed in a database. The data can be manipulated by using SQL instructions. VB and database can be liked using ActiveX, ADODC applications.

More Related