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.
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.
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:
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:
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: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.
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:
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:
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:
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.
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:
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:
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:
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:
When the user click the text pattern is a word ImgSearch Guide , we will see the image below:
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::
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:
You can download this program from here DAOPRJ.zip . 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.
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 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:
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:
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:
Find a 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 Loop End With End If End Sub |
When the user click the text pattern is a word ImgSearch Guide , we will see the image below:
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.
This image has been resized in order to avoid breaking the interface. Click here to view photo in full size (644x265)
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::
Note that in string Criteria, because the ISBN of the type text, not a number, so we have to clamp it between parentheses.
Bookmark
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:
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:
Here is the code to return to previous record current position in the recordset:
LastModified
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: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 DAOPRJ.zip . 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.
(Collected)
0 comments:
Post a Comment