Ross Sheppard High School
Computer Programming 30
Student Learning Guide
Using Visual Basic to Access a Database

	When you have finished this project, using Visual basic, you will be able to:
	access a database
	browse database records
	add records to a database
	delete records
	modify records in a database
	find records in a database

1. Save everything to do with this project in the Database folder you created on your personal drive..
   
   
2. Start a new project in Visual Basic.
   
   
3. Visual Basic has the ability to access data in a Microsoft Access database. For this project, you will use the CD Collection database you created in the last project.
   
   
4. Name the form, create a caption, and save the project.
   
   
5. Using the Code View, add a comment that indicates the name of the database that will be used for this project.
   
   
   
   
6. Switch to the Form View. You now need to add the Data Control to your form.
   
   
7. The Data Control can be found in the toolbox. Double click it to add the control to your form.
   
   
   
   
8. The Data Control will appear on your form; place it near the bottom of the form. Later, you will make it invisible so that it does not appear on the form at run-time.
   
   
   
   
9. Next, you must set some of the properties for the Data1 control. The Name property of the control should be changed to datCDCollection.
   
   
10. Change the Caption property should be changed to CD Collection.
    
    
11. Change the Connect property to Access by choosing the property from the drop down menu. As you can see, Visual Basic has the ability to connect with a number of database types. Because you created your CD Collection database in Access, you will use that property type.
    
    
    
    
12. The next step is to tell Visual Basic which database to access. This is done with the DatabaseName property. Click the property, then browse to your database. After you have selected the database, it will appear in the DatabaseName property window.
    
    
    
    
13. Set the RecordSource property to the name of your database by clicking the property then selecting the name of your database; in this case CDCollection.
    
    
    
    
14. As you complete the next few steps, you may need to adjust the size of your form to accommodate the controls and objects that you will be placing on the form. When you do this, keep in mind the expectations that users have with respect to the design of a program.
    
    
15. You will add four command buttons: first record, last record, previous record, and next record. These button will allow a user to peruse the database by clicking the button.
    
    
16. Start by adding a new command button to your form. Name the button btnFirstRecord and caption it First Record.
    
    
    
    
17. Next, write the code for the button.
    
    
    
    
18. The code you just wrote is explained in the illustration below.
    
    
    
    
19. Using the same methods as for the First Record button, create the objects and code for a Last Record button, a Previous Record Button, and a Next Record button. Consider the layout and the order of the buttons as you place them on the form. Although there is no "correct" or "incorrect" method of designing your form, remember that users have certain expectations when using a program. Once you have all four buttons completed, click here for an example of the layout and code.
    
    
20. Create an Exit button and write the code for it.
    
    
21. Now that you have created buttons that will allow the user to browse the database, you need to create a place for the data to be presented. You need to create a series of text boxes to do this. Take a look at your database. What are the fields in the database?
    
    
22. Create a text box and name it txtTitle and set the Text property to blank.
    
    
23. Set the DataField property to the name of the field; in this case Title.
    
    
24. Set the DataSource property to the name of the data element; in this case datCDCollection.
    
    
25. Save you program and run it. Notice the title of your first CD is in the text box. Try each of the buttons and notice that you can browse the database with the buttons you previously created.
    
    
    
    
26. Next, add text boxes to your form for each of the other fields in the database. Remember to observe good design functionality as you place the objects on the form.
    
    
27. You may wish to add labels to the form to indicate what each field represents.
    
    
    
    
28. Your form may not be the same as the one illustrated above - the design choice is up to you as the programmer.
    
    
29. Save your project and run it again. Keep clicking the Next Record button - after the last record, notice that you get an error message. You need to create code so this error won't happen. The code for this is shown below.
    
    
    Remember, the code shown in red above should all be on a single line.
    
    
30. Save and run your program. After you click the Next Record button and are at the last record in the database, a message box should appear. Clicking OK returns you to the program, and you know there are no more records to display.
    
    
    
    
31. The same error occurs with the Previous Record button. Add code to prevent the error.
    
    
32. Your CD Collection database only displays records already in the database. From a user's perspective it would be useful if new CDs could be added as they were purchased. You will now learn to create code that will add a new record - a new CD - to your database.
    
    
33. Create a new button that will add a record.
    
    
34. From the pseudo-code given below, write Visual Basic code that will add information from the text boxes to the database.
    
    
    
    
35. To make the program easier for the user, you should also set the TabIndex property for each of the text boxes. Set them so the user can tab, in order, from one to the other rather than clicking in each of the boxes. Set the TabIndex for Title to 0, Artist to 1, and so on.
    
    
36. Save and run the program - click the Add Record button and tab from one text box to the next.
    
    
37. Save your project. Run the program and add a new CD to your collection, then exit the program. Open your database in Access and see if the record was added.
    
    
38. Of course, opening the database every time you added a new CD to your collection is not an efficient way to work - it would be better if you could look at the records that were added right from your Visual Basic program. This can be accomplished using the UpdateRecord property.
    
    
39. Create a new command button called Update and add the code for it. After you have tested the program, check your code.
    
    
40. Save and run your program.
    
    
41. Add 5 more CDs to your collection.
    
    
42. The next task is to create a button and code that will delete records from your database. You should be able to write this code based on what you know about the RecordSet property. After you have tested the program, check your code.
    
    
43. Although you have added many improvements to your database, it would be nice if the user could quickly find a specific title. In a small database, this is easily accoomplished by browsing the reocrds using the Next and Previous buttons. However, if your database consisted of hundreds of records, this would be very inefficient. You can create a search or find button in Visual Basic.
    
    
44. Create a text box called Find.
    
    
45. Create a new command button called Find. Be sure to set all of the properties required. The code for this is shown below.
    
    
    
    
46. There are other methods for using the RecordSet Find property:
    • FindFirst: finds the first record that meets the search criteria
    • FindLast: finds the last record that meets the search criteria
    • FindNext: finds the next record that meets the search criteria
    • FindPrevious: finds the previous record that meets the search criteria
      
      
47. You have learned much about accessing databases using Visual Basic. For the next project, you will continue to work with databases.
    
    
48. Click here to see the marking guide for this project.
    
    
49. When you have finished, send an email to your teacher asking that this project be marked.