Programming language Visual Basic 6 (Chapter 15 - Programming with technical DAO)

Reference Dao

In this article we will learn basic programming with MS Access database through DAO techniques without the use of the  Control  Data  as in the previous post. I will need some DAO Objects in the library, so if you open a new VB6 project, use the Command Menu  Project | References ...  to select the Microsoft DAO Object Library 3:51  by clicking the checkbox to the left like in the picture below here. (One way to remember the name of this object is to remember the sentence  "He's DAO 35 goats" ).

Then the code would declare the Form variable to an instance of the DAO MyDatabasedatabase and variable myRS for a DAO recordset. Here we specify that the Databaseand Recordset loaiDAO to distinguish the type of Database and Recordset ADO(ActiveX Data Object) later. I noticed that Intellisense help while writing code:

Now you put up the main form, named  frmDAO , 4 labels with Captions:  Title, Year Published, ISBN  , and Publisher ID . Then add 4 textboxes and named them respectively as  txtTitle, txtYearPublished, txtISBN  and  txtPublisherID .

What we want is the new form is loaded, it will retrieve a recordset from a database containing all records in the  Titles table  in the order of letters (Alphabetical order) of the  Title field  and displays the first record.

Use keyword SET

First thing is to open a Database Object based on the full name (full path name) of the Access database:
'Open main database 
Set myDB = OpenDatabase (AppFolder & "BIBLIO.MDB")
Note the word  Set  in the sentence above code. That's because myDB a  Pointer  to an Object. Although we will henceforward use myDB as a Database in the same way as any other variable of data type, but when first appointed as its origin, we use the word Set, to say the truth is not out myDB Not the Object Database, but the Pointer to the Object Database. This point comes as more and more difficult to understand.

Basically that dynamically allocates VB6 runtime (available for when needed) a part of memory (memory) Object Database to store when we receive it from the execution of the  OpenDatabase method . Oil storage location in memory Object Database is not certain, but only because we took the position that officers should still be able to work with it normally. The staff was value (value) of variable myDB. Because this value is not Object, but it contains the  memory address  pointed to ( point to  or  refer to ) Object Database, so we called it the Pointer.

Pointer programming in general use is very flexible performance in languages like C, Pascal, C + +, etc..However, the developer must remember to return your Operating System the memory when no longer needed it back again to the Operating System for Object other cấp phát. If the re-use memory management is not satisfied with the piece of memory is located lang Operating sytem states that do not know. Last time Operating System will no longer balance memory again. We call this phenomena as  memory leakage (leakage) . Later languages such as Java, C # Pointer are not used anymore. Visual Basic programmers do not want to use Pointer. Only a few special cases reveal new VB6 shows in the scenes actually use VB6 Runtime Pointer, as in this case.

Similarly, because the recordset is a Pointer to an Object, we also use  Set  when specifying a DAO Recordset retrieved from  OpenRecordset Method  of myDB database.
'Open recordset
Set myRS = myDB.OpenRecordset("Select * from Titles ORDER BY Title") 
The parameter type of String is used as a method OpenRecordset Order (Statement)SQL. It is assigned to retrieve all database fields (columns) (Select *) of each recordfrom the Titles table (from Titles) as a recordset and sort the records in the recordset'sfield was under Title Alphabetical order (ORDER BY Title).

Remember this is just as Recordset  Recordset property  of a Control Data that we used in the previous post. Now there recordset then we can show the first detailed record of the recordset if it has at least one record. I check it based on  RecordCount property  of recordset as in the code below:
Private Sub Form_Load() 
   ' Fetch Folder where this program EXE resides
   AppFolder = App.Path 
   ' make sure it ends with a back slash
   If Right(AppFolder, 1) <> "\" Then AppFolder = AppFolder & "\" 
   ' Open main database
   Set myDB = OpenDatabase(AppFolder & "BIBLIO.MDB") 
   'Open recordset
   Set myRS = myDB.OpenRecordset("Select * from Titles ORDER BY Title") 
   ' if Recordset is not empty then display the first record
   If myRS.RecordCount > 0 Then 
      myRS.MoveFirst  ' move to first record
      Displayrecord  ' display details of current record
   End If 
End Sub 
After using the method of the Recordset MoveFirst current position to first record in theRecord, we show the values of record fields by assign them to the textboxes of the Formas follows:
Private Sub Displayrecord() 
   ' Assign record fields to the appropriate textboxes
   With myRS 
      ' Assign field Title to textbox txtTitle
      txtTitle.Text = .Fields("Title") 
      txtYearPublished.Text = .Fields("[Year Published]") 
      txtISBN.Text = .Fields("ISBN") 
      txtPublisherID.Text = .Fields("PubID") 
   End With 
End Sub 
Notice for field  Publshed Year  should include two letters of the field we have named him between square brackets ( [] ). To avoid inconveniences such as in this case, when you put your name in the database field as a table designed just stick the words together, not to leave out. Eg use  YearPublished  ratherPublished Year .

The scroll button

Navigators wanted the equivalent node of a Data Control, set up to Form 4 buttons titled  CmdFirst, CmdPrevious, CmNext  and  CmdLast  with Captions:  << ,  < ,  > ,  >> .

Code for this button is simple, but we must be careful when users want to move the last record or the record first. I have to check  EOF  is True when the user clicks into CmdNext, or  BOF  is True when the user clicks into CmdPrevious:
Private Sub CmdNext_Click() 
   myRS.MoveNext  ' Move to next record
   ' Display record details if has not gone past the last record
   If Not myRS.EOF Then 
      Displayrecord  ' display details of current record
      myRS.MoveLast  ' Move back to last record
   End If 
End Sub 

Private Sub CmdPrevious_Click() 
   myRS.MovePrevious  ' Move to previous record
   ' Display record details if has not gone past the first record
   If Not myRS.BOF Then 
      Displayrecord  ' display details of current record
      myRS.MoveFirst  ' Move back to first record
   End If 
End Sub 

Private Sub CmdFirst_Click() 
   myRS.MoveFirst  ' Move back to first record
   Displayrecord  ' display details of current record
End Sub 

Private Sub CmdLast_Click() 
   myRS.MoveLast  ' Move back to last record
   Displayrecord  ' display details of current record
End Sub 
When you run the program you will see it showing details of another record with the first previous post because the records have been sắp xếp:

Try using the Navigator buttons trees, leaves his garden watching them work correctly.

Go here, do not know whether you have noticed is that users have accidentally changed a detail in the textboxes, without any record being updated in the database when the user moves from this record to other records. The reason is that no Texboxes Data Bound for the Fields of the Recordset.

Further reduce the  Records

Like all programs, we are adding means for adding (add), remove (delete) the records. Now you go to 5 buttons on the Form name:  cmdEdit, cmdNew, cmdDelete, cmdUpdate  and cmdCancel .

Somewhere in the program before we use  Data1.Recordset  now we use  myRS .

We will use again with the parameter Editing Sub SetControls is True or False valuedepending on the user are the Browse or Edit. In Browse mode, the Textboxes areLocked (locked) and the buttons cmdUpdate vacmdCancel become incapacitated. InEdit mode, the Textboxes are unlocked (unlocked) and nutcmdNew, cmdDelete andcmdEdit become incapacitated.

Because there is no Data Binding should wait until the  update (updates)  on our new set RecordsetAddNew  or  Edit mode . Therefore, we must remember that when a user edits a record exist are Edit or add a new Record. Boolean value that we contain the variable  AddNewRecord . If the user is about to add a new record AddNewRecord = True, if the User Edit a record exists about the AddNewRecord = False.

Also, when the user about to add a new record by clicking the New button then we have to clear (as white) by the end of the textboxes Empty string to assign property of their text as follows:
' If Editing existing record then AddNewRecord = False
' Else AddNewRecord = true
Dim AddNewRecord As Boolean 

Private Sub ClearAllFields() 
   ' Clear all the textboxes
   txtTitle.Text = "" 
   txtYearPublished.Text = "" 
   txtISBN.Text = "" 
   txtPublisherID.Text = "" 
End Sub 

Private Sub cmdNew_Click() 
   ' Remember that this is Adding a new record
   AddNewRecord = True 
   ' Clear all textboxes
   ' Place controls in Edit Mode
   SetControls (True) 
End Sub 

Private Sub CmdEdit_Click() 
   ' Place controls in Edit Mode
   SetControls (True) 
   ' Remember that this is Editing an existing record
   AddNewRecord = False 
End Sub 
If the user clicks Cancel while editing the textboxes, we do not need to call the methodbecause CancelUpdate Recordset AddNew or may be placed in Edit mode. Here wejust show the details of the current record, ie what the user is canceling type:
Private Sub CmdCancel_Click() 
   ' Cancel update
   SetControls (False) 
   ' Redisplay details or current record
End Sub 
At the user clicks Update, you have the opportunity to examine data to see if any field is left empty (especially  Primary Key ISBN  mandatory value) or something is valid by calling  Function GoodData . If GoodData return a false value, then we do not promote the Update. If GoodData return value to True, then we set Recordset AddNew or Edit mode, depending on the value of Boolean variable AddNewRecord.

Like when displaying details of a record we must assign each field in the textbox, so now when we must update the reverse, ie assign the Text property of each textbox on the Record Field respectively. Finally we call the  Update method  of the recordset and return to Browse mode controls:
Private Function GoodData() As Boolean 
   ' Check Data here. If Invalid Data then GoodData = False
   GoodData = True 
End Function 

Private Sub CmdUpdate_Click() 
   ' Verify all data, if Bad then do not Update
   If Not GoodData Then Exit Sub 
   ' Assign record fields to the appropriate textboxes
   With myRS 
      If AddNewRecord Then 
         .AddNew  ' Place Recordset in AddNew Mode
         .Edit  ' Place Recordset in Edit Mode
      End If 
      ' Assign text of txtTitle to field Title
      .Fields("Title") = txtTitle.Text 
      .Fields("[Year Published]") = txtYearPublished.Text 
      .Fields("ISBN") = txtISBN.Text 
      .Fields("PubID") = txtPublisherID.Text 
      ' Update data
   End With 
   ' Return controls to Browse Mode
   SetControls (False) 
End Sub 
Just because there is no Data Binding, so when the User Delete a record, after moving through the next record we have to show details of that record as follows:
Private  Sub CmdDelete_Click () 
   On  Error GoTo DeleteErr 
   With myRS 
      'Delete new record
      . Delete 
      'Move to next record
      . MoveNext 
      If . EOF Then . MoveLast 
      'Display details of current record
      Exit  Sub  
   End  With 
DeleteErr: MsgBox Err.Description 
   Exit  Sub  
End  Sub 

Find record

Following this, we want to list the book titles contain a certain word or sentence, such as the " Guide ".Then the user can choose a select book titles by then and click the button  Go . The program will locate (find) the record books and show him the details of it.

Now you go to a textbox on the form name  txtSearch  and an Image name  ImgSearch . Then set a frame name  fraSearch  the Form. To frame this up a listbox named  List1  to display the titles of books, and two buttons named  CmdClose  and  CmdGo , with the caption Close and Go. After you select a book in List1, the user will click  Go  to display the book details her. If you change your mind, the user will click Close  to disappear fraSearch frame.

FraSearch normal frame only appears when needed, should initially be set  Visible property  of it to False.I will give ImgSearch display a zoom lens so you must click on the  Picture property  in the Properties Window to select Icon BINOCULR.ICO from the folder E: \ Program Files \ Microsoft Visual Studio \ Common \ Graphics \ Icons \ Misc:

This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (837x557)

What is the Primary Key of the table Titles ISBN. When the user wants to select a bookthat was to locate the book's ISBN (the place) it in the Recordset myRS. Thus whileadding the title of a book on List1, we also add that the book's ISBN on Monday nameda Listbox List2. We will only use List2 backstage, so we'll set its Visible property toFalse. Here is the code to load the book title and ISBN in Listboxes:

Private Sub ImgSearch_Click() 
   ' Show Search Frame
   fraSearch.Visible = True 
   Dim SrchRS As DAO.Recordset 
   Dim SQLCommand As String 
   ' Define SQL statement
   SQLCommand = "Select * from Titles where Title LIKE '" & "*" & txtSearch & "*" & "' ORDER BY Title" 
   ' Fetch all records having Title containing the text pattern given by txtSearch
   Set SrchRS = myDB.OpenRecordset(SQLCommand) 
   ' If Recordset is not Empty then list the books' titles in List1
   If SrchRS.RecordCount > 0 Then 
      List1.Clear  ' Clear List1
      ' We use List2 to contain the Primary Key ISBN corresponding to the books in List1
      List2.Clear  ' Clear List2
      With SrchRS 
         ' Iterate through the Recordset until EOF
         Do While Not SrchRS.EOF 
            ' Display Title in List1
            List1.AddItem .Fields("Title") 
            ' Store corresponding ISBN in List2
            List2.AddItem .Fields("ISBN") 
            .MoveNext  ' Move to next record in the Recordset
      End With 
   End If 
End Sub 

When the user click the text pattern is a word ImgSearch  Guide , we will see the image below:
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (644x265)
In the SELECT statement above we use the LIKE operator on the text pattern, lettersGuide, a wildcard character (*) on either side. Wildcard character is a place with (or without) the word whatever. In this case means that everyone have the Guide in the book title is, no matter where it is located. Also this choice is not case sensitive, ie the textguide, Guide, or are even GUIDE.

When the user clicks the Go button, we will use the recordset method FindFirst myRS tothe place of record a Primary Key value is found in List2 text corresponding to the titleselected in List1 as follows::
Private  Sub CmdGo_Click () 
   Dim SelectedISBN As  String  
   Dim SelectedIndex As  Integer  
   Dim Criteria As  String  
   'Index of line in List1 selected by users
   SelectedIndex = List1.ListIndex 
   'Obtain ISBN tương ứng in List2
   SelectedISBN = List2.List (SelectedIndex) 
   'Define Search Criteria - use single quotes for selected text
   Criteria = "ISBN = '" & SelectedISBN & "'" 
   'Locate the record, it Will Become the current record
   myRS.FindFirst Criteria 
   'Display details of current record
   'Make fraSearch disappeared
   fraSearch.Visible = False  
End  Sub 
Note that in string Criteria, because the ISBN of the type text, not a number, so we have to clamp it between parentheses.


When moving from record to record in this recordset, sometimes we want to mark the location of a record to be given the opportunity to return. We can do it by remembering  Bookmark  the Recordset.

For example when the user clicks the Go button, we want to remember the location of the record at that time was to return later when the user clicks the button Go Back . Please add a button named Form CmdGoBack  with Caption  offline . I will add a variable named  LastBookmark  type data type  Variant:
Dim last BookMark As  Variant 
CmdGoBack button invisible at first, and only became visible after the user clicks the Go button. Now we add the following codes into Sub CmdGo_Click () as follows:
' Remember location of current record
LastBookMark = myRS.BookMark 
CmdGoback.Visible = True 
Here is the code to return to previous record current position in the recordset:
Private Sub CmdGoback_Click() 
   ' Reposition record to last position
   myRS.BookMark = LastBookMark 
   ' Redisplay details or current record
End Sub 


LastModified  record is the location of the newly amended or added to the recordset. To test this you add an invisible button called  CmdLastModified  with a caption  Last Modified . This button appears only after the user clicks Update. Anytime you CmdLastModified button, a new record has been changed or added will be displayed:
Private Sub CmdLastModified_Click() 
   ' Reposition record to last position
   myRS.BookMark = myRS.LastModified 
   ' Redisplay details or current record
End Sub 
Here is the form of design as being:
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (640x265)

You can download this program from here . BIBLIO.MDB file copy MS Access Memory, which is a database, into the same folder of the program before running the test.

You will learn techniques ADO (ActiveX Data Object) in the next article.


Post a Comment