1 / 15

Comprehensive Guide to Updating an Access Database: Add, Modify, and Delete Records

This guide covers essential operations in Microsoft Access for managing database records. Learn how to efficiently add new records, modify existing data, and delete unwanted entries. You'll explore practical examples, including how to verify the existence of a record before adding or updating it and the importance of handling user input smoothly. Each section is supported by code snippets for hands-on practice, ensuring a deeper understanding of Access data manipulation. Ideal for beginners and experienced users alike looking to enhance their database skills.

gunnar
Télécharger la présentation

Comprehensive Guide to Updating an Access Database: Add, Modify, and Delete Records

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. Updating an ACCESS database • Three Actions • Add a new record to a table • Change information in an existing record in a table • Delete an existing record from a table

  2. Adding a New Record to an ACCESS Database • Appends a blank record to the end of the table • datControl.Recordset.Addnew • Use the fields option to insert a value into each (required) field on the blank record • datControl.Recordset.Fields(“field name”) = txtbox.text • datControl.Recordset.Fields(“field name”) = variable • Update the database • datControl.Recordset.Update

  3. AddNew • You may want to do a FindFirst to verify that the “new record” does not already exist • Use the fields options to give all required fields a value • strLookup = txtSSN.text strFind = “[ssn] = ‘” & strLookup & “’” datStudent.Recordset.FindFirst strFind If datStudent.Recordset.NoMatch = False then datStudent.Recordset.Addnew datStudent.Recordset.Fields(“Major”) = txtMajor.text datStudent.Recordset.Fields(“SocialSecurity”) = txtSSN.text datStudent.Recordset.Fields(“Name”) = txtName.text datStudent.recordset.Update Else MsgBox “already on file” End If

  4. Practice Problem - ADD • Assume the user is entering data on the screen below txtName Customer Name txtStreet Customer Street Customer City picCity Customer State picState txtZip Customer ZIP

  5. Practice Problem - ADD • Assume the user is entering data on the screen on the previous slide • The zip code is a keyed in txtZip • If the Zip code in txtZip is in the zip code table • The city/state should be printed in picCity and picState picture boxes • If the zip code in txtZIP is not in the zip code table • The user should be prompted to enter a city and state in input boxes. Then this information should be added to the zip code table [consisting of fields of Zip, City, State] and displayed in the picture boxes picCity and picState

  6. Practice Problem - ADD On the lost focus event for txtZip: Dim strFind as String Dim response as Integer Dim city as String Dim state as String StrFind = “[Zip] = ‘” & txtZip.text & “’” datZip.recordset.findfirst strFind If datZip.recordset.nomatch = true then response =Msgbox (“zip not found – is it correct?”, vbYesNo) If response = 6 then ‘response = 6 means the user clicked on YES city = inputbox (“enter the city”) state = inputbox (“enter the state”) datZip.recordset.addnew datZip.recordset.fields(“City”) = city datZip.recordset.fields(“State”) = state datZip.recordset.fields(“Zip”) = txtZip.text datZip.recordset.update lblCity.caption = city lblState.caption = state else txtZip.setfocus ‘ resets the cursor on the zip textbox for correction endif else lblCity.caption = datZip.recordset.fields(“City”) lblState.caption = datZip.recordset.fields(“State”) endif

  7. Update • Find the desired record strFind = “[CourseID] = ‘” & txtCourseID.text & “’” datClass.Recordset.FindFirst strFind • Use the field option to change the desired field and then update the database If datClass.Recordset.NoMatch = False then datClass.Recordset.Fields(“enrolled”).value = datClass.Recordset.Fields(“enrolled”).value + 1 datClass.Recordset.Update End If • The last record read is the record which is updated • I use the value option whenver I want to do mathematics

  8. Update – example 2 • Find the desired record • strFind = “[SSN] = ‘” & txtSSN.text & “’” datStudent.Recordset.FindFirst strFind • Use the field option to change the desired field and then update the database If datStudent.Recordset.NoMatch = False then datStudent.Recordset.Fields(“Name”) = txtName.text datClass.Recordset.Update End If • The last record read is the record which is updated

  9. Delete • Generally, before I delete a record I display some information and ask for the delete to be verified • There is usually a FindFirst or FindNext before the delete • The actual delete statement is then merely datTable.Recordset.Delete • For example: • DatSudent.Recordset.Delete will delete the record which was read last from the student table

  10. Delete Example • Print the names of all students with enrolled in txtCourseID and txtSection. Delete them from this class – delete from the student class table strFind =“[CourseID] ='” &txtCourseID.text& “'and [Section] = '” &txtSection.text& “'” datStuClass.Recordset.FindFirst strFind Do While datStuClass.Recordset.NoMatch = False strFind2 = “[SSN] = ‘” & datStuClass.recordset.fields(“SSN”) & “’” datStudent.Recordset.FindFirst strFind2 If datStudent.Recordset.NoMatch = False then picNames.print datStudent.Recordset.Fields(“Name”) End If datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind Loop

  11. Practice Problem 1 • Assume that txtSSN.text contains a social security number • Write a routine which will delete all records on the student class file for this student • Display the CourseID and Section from the student class record which you are deleting

  12. Answer strFind2 =“[SSN] ='” & txtSSN.text & “’” datStuClass.Recordset.FindFirst strFind2 Do While datStuClass.Recordset.NoMatch = False picClasses.print datStuClass.Recordset.Fields(“CourseID”); tab(12); datStuClass.Recordset.Fields(“Section”) datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind2 Loop

  13. Practice Problem 2 • Re-do practice problem 1 but also: • Read the class file and update (decrement by 1) the number enrolled

  14. Answer strFind2 =“[SSN] ='” & txtSSN.text & “’” datStuClass.Recordset.FindFirst strFind2 Do While datStuClass.Recordset.NoMatch = False picClasses.print datStuClass.Recordset.Fields(“CourseID”); tab(12); datStuClass.Recordset.Fields(“Section”) strFind = “[CourseID] ='” & datStuClass.Recordset.Fields(“CourseID”) & “'and [Section] = '” &txtSection.text& “'” datClass.Recordset.FindFirst StrFind If datClass.Recordset.NoMatch = False then datClass.recordset.Edit datClass.Recordset.Fields(“enrolled”).value = datClass.Recordset.Fields(“enrolled”).value + 1 datClass.Recordset.Update End If datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind2 Loop Refresher: An IF statement was used to check for nomatch on the class file as it was read by PK; A DO LOOP was used with the student class file as it was NOT read by PK

  15. Practice Problem 3 • Assume the user has entered a social security number in txtSSN • And a Course ID and Section in txtCourseID and txtSection • Write the VB that will: • Check that the SSN is found on the Student Table • Check that the Course ID/Section are found on the Class table • Add 1 to the number enrolled in the CourseID/Section and update the class table • Add a new record to the student class table for the SSN/CourseID/Section in the textboxes.

More Related