Visual Basic - File & Database Management

13 minute read

 

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

 

Mode

Description

Input

The file is opened for reading the data from the file.

Output

The file is opened for writing the data to the file.  This mode creates a new file if the file doesn’t exist.  If it already exists, then the data of the file is overwritten.

Append

The file is opened for editing/adding the data to the previously existing file.  If the file doesn’t exist previously, a new file is created.

Random

The file is opened for random access.

Binary

The records are entered in binary format

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.

clip_image002

 

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. 

 

clip_image003

To Remember:
 The difference between Print and Write statement is that Print statement can only write text on file, while Write statement can write text and other data types also.

 

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

 

clip_image005 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.

 


clip_image007

 

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.

 

clip_image009


Figure 3: Database Connectivity

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_
 Search", "DAO")

Data1.Recordset.MoveFirst

While Not Data1.Recordset.EOF

   If UCase(name) = _

      UCase(txtName.Text) Then

        isfound = isfound + 1

        MsgBox "Press any key to _
      continue,.."

    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.

 

clip_image011

 

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.

 

clip_image013

 

Figure 5: Creating a new ODBC Data Source

 

clip_image015

 

Figure 6: Database db as ODBC database.

clip_image017 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

clip_image019
Figure 7: RDO Connectivity



Here, UserDB has following fields:

 

Type                Character         1
UserName       Character         30
Password         Character         10

 

 

 

 

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)

 

clip_image021ADO 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.

 

 

clip_image023

 

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.

 clip_image025

 

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.

 

clip_image027

 

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.

 

clip_image029

 

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

clip_image031

 

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.

Updated: