Visual Basic - File & Database Management
File Management
A software must be able to store and retrieve the data that user enters while execution of software. In this article, we shall understand the different ways to access the file data.
Opening and Reading a File
The syntax for opening the file with Open Command is as,
Open Filename For mode As [#]filenumber [Len=reclength]
Table 1: Arguments of Open Statement
|
mode |
Various modes in which a file can be opened are
|
||||||||||||
|
filenumber |
Each file is referred by a unique filenumber. The range of filenumber is from 1 to 511. To obtain the next available filenumber, we use FreeFile Function |
||||||||||||
|
reclength |
This defines the maximum length of the file. It can take values less than or equal to 32,767 bytes. It is record length for files opened in Random Access mode. |
Opening file for Writing /
Consider that we need to store Name and Age of the employees in a file. This file if opened in Output mode, will store data of one employee rather than keeping the data of all employees. Hence, it is needed to be opened in Append mode.
The following code will make it clear,
Private Sub cmdReadSimple_Click()
txtRName.Text = ""
txtRAge.Text = ""
Dim s1, s2 As String
Open txtFileName.Text For Input As #2
Do Until EOF(2) ‘Search till End-Of-File
Input #2, s1 ‘Get data from file
Input #2, s2
txtRName.Text = txtRName.Text + s1 + vbNewLine
txtRAge.Text = txtRAge.Text + s2 + vbNewLine
Loop
Close #2
End Sub
Private Sub cmdWriteAppend_Click()
Open txtFileName.Text For Append As #1 ‘Opened in append mode
Print #1, txtWName.Text
Print #1, txtWAge.Text
Close #1
End Sub
Private Sub cmdWriteSimple_Click()
Open txtFileName.Text For Output As #1 ‘Opened in output mode
Print #1, txtWName.Text
Print #1, txtWAge.Text
Close #1
End Sub
The output of the program is shown in Figure 1. The data entered at the end is xyz and 34. The other two data’s are added previously in the append mode.
Figure 1: File Opened for writing in Append Mode
When opened in the Output Mode, only last data xyz would remain in the file.
In the function cmdWriteSimple, we open the file mentioned in the text box in Output Mode. Print statement is used to write text in the file. The syntax of print statement is as,
Print [#]filenumber, data in text format.
Instead of print statement, Write # Statement can also be used. It assumes the same format as Print statement.
|
|
To Remember: |
The Close statement is used to close the file that was opened by Open Statement. This close statement inserts End Of File to the file.
In the function cmdWriteAppend, the file is opened in Append Mode. The rest of the code remains same.
While reading the file, we have used EOF statement in do until loop, which executes the code till End Of File is not detected.
Opening file in Binary Mode / Random Access Mode
The commands for opening the file in Random Access Mode or Binary Mode vary from those mentioned before.
To write data to the file Put statement is used and to retrieve data from the file, Get statement is used.
The syntax of Put and Get statement are:
Put [#]filenumber, [recordnumber], varname
Get [#]filenumber, [recordnumber], varname
This renders us to insert record at a particular record number.
File Related Functions
FileLen and LOF()
Syntax: FileLen(filename)
This function returns the length of the file on disk in bytes
Private Sub cmdLength_Click()
MsgBox FileLen("c:\data.txt")
End Sub
A similar function is LOF (abbreviation of Length of File). The difference in both the functions is that LOF accepts the file number as an argument while FileLen accepts filename as argument.
Syntax: LOF(filenumber)
Private Sub cmdLength_Click()
Open “c:\data.txt” For Input As #1
MsgBox LOF(1)
End Sub
Input$ Statement
Syntax: Input$(characters to be read, filenumber)
It returns the text from the file and stores it in the variable name.
Private Sub cmdRead_Click()
Open “c:\data.txt” For Input As #1
Text1.text = Input$(LOF(1), #1)
End Sub
Seek Statement
Syntax: Seek [#]filenumber, position
Sets the position for the next read/write operation within a file opened using the Open statement. In Random Access Mode, Seek sets the next record while in all other modes it sets the byte position at which next operation takes place.
Line Input Statement
Syntax: Line Input [#]filenumber, varname
Reads a single line from an open sequential file and assigns it to String Variable
Private Sub cmdRead_Click()
Dim text as String
Open "c:\data.txt" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, text ' Read line into variable.
MsgBox text ' Print to the Immediate window.
Loop
Close #1 ' Close file.
End Sub
Database Management
Storing data in file can make the file bulky and searching, sort processes can become cumbersome. A better available alternative is use of database. What makes database different from normal text files is their pre-defined compressed structure. There are different sets of database objects in Visual Basic.
Initially, Microsoft introduced Data Access Objects (DAO) to connect Microsoft Jet database engine in Microsoft Access. Later, realizing that there are other datatypes available, they came up with Open Database Connectivity (ODBC) and supported Remote Data Objects (RDO) in Visual Basic. As internet became more popular and eCommerce concept involved, Microsoft created ActiveX Data Objects (ADO) that can use connections on a single computer over networks.
DAO
DAO uses Microsoft Access Database files with extension .mdb. Database can be made either through Microsoft Access or using Visual Data Manager (Add-Ins Menu) of Visual Basic. We assume that the database has been created and the structure of database is as shown in Table 2 and a part of method of creating database through Visual Data Manager is shown in Figure 2.
Figure 2: Visual Data Manager
There are three fields in the table PData so we need three TextBoxes on the form to bind the data in the database with these TextBoxes. We then insert the DAO control on the form and the form appears as in Figure 3.
Table 2: Personal Database
|
Database Name:
|
C:\PersonalDatabase.mdb |
||
|
Table Name: |
PData |
||
|
Field Name |
Data Type |
Size |
|
|
Name |
Text |
50 |
|
|
Mobile |
Text |
10 |
|
|
Address |
Memo |
|
|
Thereafter, we set the properties of Data1 and TextBoxes as shown adjacent to Figure 3.
|
|
Data1 DatabaseName:C:\PersonalDatabase.mdb RecordSource: PData
DataSource of txtName, txtMobile and txtAddress as Data1
txtName DataField: Name
txtMobile DataField: Mobile
txtAddress DataField: Address |
If there are any records in the database, we can view them on this form using buttons on the Data1 control.
Let’s now add code to Add New Records in Database, Edit, Delete and Search Records.
|
Private Sub cmdAdd_Click() Data1.Recordset.AddNew cmdAdd.Enabled = False cmdEdit.Enabled = False cmdUpdate.Enabled = True End Sub
|
To add a new record, we use AddNew method.
To edit a record, Edit method is to be called
After the add/edit button is clicked, update button (that updates the record in the database) should be pressed so that the record is entered into the database
|
|
Private Sub cmdEdit_Click() Data1.Recordset.Edit cmdAdd.Enabled = False cmdEdit.Enabled = False cmdUpdate.Enabled = True End Sub
|
|
|
Private Sub Form_Load() cmdUpdate.Enabled = False End Sub
Private Sub cmdUpdate_Click() Data1.Recordset.Update cmdUpdate.Enabled = False cmdAdd.Enabled = True cmdEdit.Enabled = True End Sub
|
To update database, we need to use Update Method. |
|
Private Sub cmdDelete_Click() Data1.Recordset.Delete If Not Data1.Recordset.EOF Then Data1.Recordset.MoveNext End If End Sub
|
To delete the current record that is visible on the screen, we use the Delete Method. After deleting the record, the record should either move to the next or previous record. |
|
Private Sub cmdSearch_Click() Dim name As String Dim isfound As Integer isfound = 0 name = InputBox("Enter Name to_ Data1.Recordset.MoveFirst While Not Data1.Recordset.EOF If UCase(name) = _ UCase(txtName.Text) Then isfound = isfound + 1 MsgBox "Press any key to _ End If Data1.Recordset.MoveNext Wend If isfound = 0 Then MsgBox "No records found" End If Data1.Recordset.MovePrevious End Sub |
To search the record, we move to the first record (MoveFirst method) and thereafter check all the records one by one (MoveNext method) till the End Of File (EOF property).
When the record is found, we display the message through the Message Box. |
Figure 4: Personal Database Program at Run-time.
The execution of the program is shown in Figure 4.
RDO (Configuring ODBC)
Before using RDO, we need to configure a new connection for ODBC source. We configure an ODBC connection with 32-bit ODBC item in Control Panel (Control Panel> Administration Tools>Data Sources>System DSN) as shown in Figure 5 and 6.
Figure 5: Creating a new ODBC Data Source
Figure 6: Database db as ODBC database.
To introduce RDO control, select Microsoft Remote Data Control from Components in Project menu. Thereafter, we connect RDO with the project.
The properties of RDO need to be changed as,
DataSourceName: db
SQL: Select * form UserDB
|
|
Here, UserDB has following fields:
Type Character 1 |
After binding the database UserDB, we do the coding as
|
Purpose |
Command |
|
For Adding new record |
MSRDC1.Resultset.AddNew |
|
For Updating the record |
MSRDC1.Resultset.Update |
|
For Editing the record |
MSRDC1.Resultset.Edit |
|
For Deleting the record |
MSRDC1.Resultset.Delete |
The code remains the same except that instead of RecordSource, we use ResultSet
Using RDO, we can configure databases remotely and add/modify data in them remotely. This is an added advantage of RDO over DAO, which is used for local machines.
ADO (ActiveX Data Objects)
ADO is a higher version of both DAO and RDO and is generally used for database handling on the Internet. The Adodc objects have an added advantage over the DAO and RDO because not only their properties can be defined through the properties window, but they can be even altered dynamically during execution.
A database can be linked with Adodc in the following manners:
1. Linking through properties of ADODC
2. Linking dynamically using code
Linking through properties of ADODC:
There are various database engines available with us and we need to select one of them. Let us assume that we are using Microsoft Access Database which uses Jet Engine. Hence, to link a access file (with extension .mdb) we need to provide a connection string in ConnectionString property of ADODC.
We select Microsoft Jet 4.0 OLE DB as provider, .mdb file as database file. We thereafter, Test Connection to check whether the connection is perfectly established or not. Applying the settings, connection string appears as
Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\LMS\MasterDB.mdb;Persist Security Info=False
After defining the connection string, one needs to define the RecordSource, i.e. the table in the database which we wish to refer to.
select * from bookTable
Above is a SQL Statement that enables us to retrieve data of all the fields of the table (here bookTable). With the knowledge of SQL, we can have selected records to be displayed and can omit the ones not required. Sorting of records can also be done.
Thereafter, we link the data fields’ to textboxes and labels as desired and program the same way as we did in DAO. However, there’s one exception of ADO from DAO that we don’t need to write the code for editing. It prefers to edit automatically once the key is pressed on any of the fields. We just need to update the database so that the changes made are reflected the next time.
Visual Basic provides an easier method to do the same by the concept of VB Data Form Wizard. This wizard creates a raw form for accessing the database with the help of ADO or RDO.
Let’s create a new form of type VB Data Form Wizard. A window appears that asks for the profile. We select the default value (None) and proceed ahead.
Figure 8: Data Form Wizard - Profile
Thereafter, we select the type of connection: Access (ADO) or the ODBC(RDO) Interface. The database link is to be provided in the next page after you select Access Interface, which we provide as C:\LMS\MasterDB.mdb.
We then need to decide the name of the form in the next screen that appears.
Figure 9: Data Form Wizard – Form Layout
We proceed ahead keeping the form layout as Single Record. We will have direct data access with the help of adodc object named datPrimaryRs, which is automatically kept by wizard. Here, the Binding type is ADO Data Control. We shall consider ADO Code in the second style of ADO Connection.
We can also use other layouts as per requirements.
Figure 10: Data Form Wizard – Record Source
The Record Source is the table of the database which we need to access. The selection of available fields (in the table defined in Record Source) determines the way(order) in which we want the form components to be placed.
Column to Sort By defines by which field one wants to sort the entry into the database. Thereafter, we finish this procedure and a form as shown below is created by the wizard.
Figure 11: Data Form Wizard Output - ADO Binding
In cases, where we need to link data of two tables, we use an alternative layout called Master/ Detail. We can link them by a common data field. Here, we have used Student ID as linking data field between StudentRecords table and IssueRecords table and the result is
Figure 12: Data Form Wizard Output – Linking of two databases
Let’s now understand the second method i.e. ADO Code. We repeat the same process without setting the properties of ADODC and rather by code.
Let’s understand the code written below:
Global Definition:
Dim WithEvents adoPrimaryRS As Recordset
Dim db As New ADODB.Connection
On Form Load:
db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data _ Source=C:\LMS\masterdb.mdb;" ‘Which database to use
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select Type,UserName,Password from UserDB", db,_
adOpenStatic, adLockOptimistic
‘Type,UserName and Password are fields in table UserDB
Dim oText As TextBox ‘oText is a general TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS ‘Bind textbox to adoPrimaryRS
Next
In the above code, there’s an array of textboxes called txtFields to which the data is binded. The rest code of Add, Update, Refresh buttons remains same as DAO. However, the other way of coding for Add Button can be as:
db.Execute "insert into UserDB values("
& txtFields(0).Text & " , '"
& txtFields(1).Text & "','"
& txtFields(2).Text & ")"
Similarly, using SQL Queries Update and Delete, we can write code for Update and Delete button.
Note: This series was first published in DeveloperIQ and was co-authored by Puneet Ghanshani with Pranjali Bakeri in 2005-2006.
