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



Various modes in which a file can be opened are





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


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.


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.


The file is opened for random access.


The records are entered in binary format


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


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


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


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.




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.




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:



Table Name:


Field Name

Data Type












Thereafter, we set the properties of Data1 and TextBoxes as shown adjacent to Figure 3.



Figure 3: Database Connectivity



RecordSource: PData


DataSource of txtName, txtMobile and txtAddress as Data1



DataField: Name



DataField: Mobile



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()


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()


cmdAdd.Enabled = False

cmdEdit.Enabled = False

cmdUpdate.Enabled = True

End Sub


Private Sub Form_Load()

cmdUpdate.Enabled = False

End Sub


Private Sub cmdUpdate_Click()


cmdUpdate.Enabled = False

cmdAdd.Enabled = True

cmdEdit.Enabled = True

End Sub


To update database, we need to use Update Method.

Private Sub cmdDelete_Click()


If Not Data1.Recordset.EOF Then


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")


While Not Data1.Recordset.EOF

   If UCase(name) = _

      UCase(txtName.Text) Then

        isfound = isfound + 1

        MsgBox "Press any key to _

    End If



If isfound = 0 Then

    MsgBox "No records found"

End If


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.

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

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




For Adding new record


For Updating the record


For Editing the record


For Deleting the record



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.





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



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.