Programming Microsoft Word - 09 - Working With A Database

What are we going to do in this tutorial?

In the previous tutorial, we covered the topic of inserting text in a Word document. The text to be inserted was derived from user input.

A great way to leverage your program is adding a database. The user can use the database to store information which is subsequently available in the program for reuse. There is that word 'reuse' again.

Adding a database sounds complicated but is surprisingly simple. Perhaps the most strenuous part of this tutorial is creating the database itself. In this tutorial, you will use a Microsoft Access database. That means you need a copy of Microsoft Access installed on your machine. If you have never worked with Access, this will be the hardest part. Don't worry; you will be guided through the steps.

Step 1

As stated above, building the Access database is the first step. Basically, you will create a database in Access and save it as an Access file. This file can be used as database in the VB.NET program you will write. Let's start moving….

Start Microsoft Access so you see the following start screen.

Click on the button 'Blank Database' and fill out the name of the database on the right. Give the database the name 'DbParties'. I suggest that you place the database file on your desktop so you can find it easily when adding it to your program.

When done, click on the button 'Create'.

Step 2

Now its time to fill out a table. Assume that your program is going to be used to insert party descriptions in Word documents (like in agreements). To keep it simple, your program will use the following details for an individual: name, place of birth, address, city and country. Time to insert these rows in the Access database file.

Access has already opened 'Table1' with the cursor in an orange field named 'Add New Field'. You will have to add names to each column equal the names mentioned above (name, place of birth, etc). You can ignore the ID colum, which Access made automatically. The ID column is vital because it will give each row of data a unique number and counts upward automatically.

The easiest way to insert new Fields is by switching to the Design view. Right click the 'Table1: Table' text at the left of the screen and choose 'Design View'.

In the popup window that appears, give Table1 the name: 'TbIndividuals'

On the page that appears, proceed adding 'fields' until you have the following order of things:

Also fill in some sample data so you can play with it directly when running the program later on. Do this by switching back to the earlier view. This is one by double-clicking the name 'TbIndividuals: Table' on the left side of the screen:

Upon the prompt, click the Yes button to save your table and proceed. Fill out some sample data (see sample below).

Step 3

As the next step, create another Table. This is done by going to the 'Create' tab in Access' ribbon and clicking the 'Table' button.

Again, switch to the 'Design View' and name the new Table 'TbCompanies'. Again, leave the ID field alone and add the 'fields' as shown in the picture below:

Switch back to the previous view by clicking the table name at the left of your screen, click 'Yes' to save it and fill out some sample data:

You can now save your file (with the save button left-top of the Access window and close Access. Time to start up Visual Basic…

Step 4

Open a new project through: File → New Project. Choose a Windows Form Application and name it 'MyFirstFormWithDatabase'. Click OK.

Your Form1 is shown. Increase the size of the form a bit and rename the Form to 'Data Form' in the properties window.

Before we move to the next step, we need a visual tool to differentiate between the details Individuals to be inserted in our Word documents and the details of Companies. An appropriate tool is the 'TabControl'. From the Toolbox, drag a 'TabControl' onto the Form and resize it as shown. Rename the TabControl to 'TcParties' (under '(Name)' in the Properties window). The names of the TabPages can be changes by clicking the '…' part of '(Collection)' and filling out the '(Name)' and 'Text' properties in the window that pops up. For TabPage1, choose 'TpNP' as Name and 'Individuals' as Text. Users of the form will see the Text, not tte Name. Do the same for TabPage2 with the names 'TpRP' and 'Companies' and click OK.

Run your program and notice that your form contains a so-called 'container' that can hold objects such as buttons and textboxes on the separate tab pages. Time to populate these tab pages with objects, but first edit the Tab Control by changing some of its parameters. Select the Tab Control object as a whole (not just the tab pages) and go to the properties windows.

Change the following properties: 1. Anchor: Top, Left, Right 2. Dock: Top

Furthermore, select the tabpage 'Individuals and select the box beneath this tab (so do not select the entire tab control, but just the box beneath the tabs. In the properties window, change the property BackColor from 'Transparent' to 'ButtonFace'. This color can be selected in the 'System' tab when changing the BackColor. Do the same with the tap page 'Companies'.

Finally, add two buttons to your form and place them onto the Form below your Tab Control. Name the buttons 'btnOK' and 'btnCancel' and give them the text 'OK' and 'Cancel', respectively. Your form now looks like this:

Step 5

The next step is adding a connection to the database. Adding textboxes to the form is not necessary at this stage. You will find out why.

Add a new data source (database) to your project: Project → Add new data source.

In the next window, choose 'Database' and click next.

In the next window, choose 'Dataset' and click next.

In the next window, choose the button 'New Connection': a file brower window pops up.

I assume the 'Data source:' is already set to 'Microsoft Access Database File (OLE DB)'.

Click 'Browse' next to the box 'Database file name: Search for your DbParties access file on your desktop, select it and click 'Open'.

Back to the 'Add Connection' window, you can test the connection to the data base just added by clicking the 'Test Connection' button. If everything is right, a popup window tells you that the test connection succeeded. Click 'OK' and in the next window 'Next'. A window pops up: Visual Studio is asking us whether to copy the access file (the database) into our project. Choose 'Yes'. In the next window, choose 'Next'.

In the final window, choose the data to use in your program. Select the two tables (see picture) and click 'Finnish':

One last thing to do after VS copied the database file to our program's folder, is to set the property 'Copy to Output Directory' of that file 'DbParties.accdb' to 'Copy if newer'. This ensures that the data base which the program can change when it runs, is not destroyed by the default database you created each time you run the program.

Step 6

Now that the connection to the database is ready, it is time to populate each tab page of our form.

Make sure that the tab page 'Individuals' is selected in the form.

First, let's place some textboxes and labels on the Tab page 'Individuals'. You should match the result shown in the picture (name the textboxes txtNPName, txtNPPlaceOfBirth, txtNPAddress, txtNPCity, txtNPCountry).

Do the same for the Tab page 'Companies' (name the text boxes txtRPName, txtRPSeat, txtRPAddress, txtRPCity, txtNRCountry).

To conclude this part of the tutorial, let's write a Sub that puts out the contents of the text boxes in a Message Box. Putting this content in a Word document has been dealt with in the previous tutorial, which is not the scope of this tutorial.

Click on the 'OK' button on your form and write the following code.

Select Case TcParties.SelectedTab.Name

            Case TbNP.Name
                MsgBox("Name: " & txtNPName.Text & vbCrLf & "Place of Birth: " & txtNPPlaceOfBirth.Text & vbCrLf & _
                       "Address: " & txtNPAddress.Text & vbCrLf & "City: " & txtNPCity.Text & vbCrLf & _
                       "Country: " & txtNPCountry.Text & vbCrLf, , "Show Data")
            Case TbRP.Name
                MsgBox("Name: " & txtRPName.Text & vbCrLf & "Seat: " & txtRPSeat.Text & vbCrLf & _
                       "Address: " & txtRPAddress.Text & vbCrLf & "City: " & txtRPCity.Text & vbCrLf & _
                       "Country: " & txtRPCountry.Text & vbCrLf, , "Show Data")

End Select

The code shown is very straight forward. With the 'Select Case .. End Select' code, the computer looks at the Tab Control, checks which Tab is selected and subsequently executes the code written for that 'Case'. This is more efficient than an 'If… End If' code. A very elegant code to make the distinction between the contents in both Tab pages.

The code for both Tab Pages is a simple message box code that lists all data from the relevant text boxes for the relevant Tab page. Notice the small difference in both commands, which reflects the difference between the data used for Individuals and the data used for Corporations. Furthermore, to top it all off, the title of the Message Box is specified as 'Show Data'. A lot better than the standard title (notice the double comma in the code to make this work).

Now run the program, fill out some text and hit the OK button. A Message Box appears showing the data. Do the same for the tab Companies and notice how well the code behaves.

At this stage, there is one thing left to do for this Form. Increase the size of the form a little bit, so you can add a button at the right side of the form. Add a button for each tab page on the form. For the Individuals tab, name the button btnNPLookup with text = … and for the Companies tab, name the button btnRPLookup, also with text = … The end result is shown below.

This is our basis setup. Time to make some real use of that database inserted earlier.

Step 7

You are going to perform a nice trick in VB.NET called 'inheritance'. It avoids you having to write code twice. In the end, this keeps your code neat and tidy and easier to maintain. Might as well start doing this right from the start, also in smaller coding projects. Two of these forms will be used to pass data to and retrieve data from the database.

Create three new Windows Forms (of equal size) by right-clicking on the project in the Solution Explorer and choosing: Add → Windows Form

First, we will work on the BasefmLookup. Place two buttons on this form with the names 'btnOK' (text = OK) and 'btnCancel (text = Cancel). Double-click on the OK button and insert the following code.

Double-click on this form and insert the following code.

        Me.DialogResult = Windows.Forms.DialogResult.Cancel

Do the same for the Cancel button and insert the following code.

        Me.DialogResult = DialogResult.OK

I am going to explain this code later on. It is enough for now to know that is code is part of the solution to transfer data in a Lookup form to the main form (the form with the tabs created earlier). Your Base form will look as show in the picture below.

Double-click on the frmNPLookup in the Solution Explorer. Visual Studio automatically adds code for a form_load event ('Private Sub frmNPLookup_Load …. End Sub'). You can safely delete that code again. Between 'Public Class frmNPLookup .. End Class', insert the following line.

Inherits BasefmLookup

The Error List will show an error. You can correct this by moving the mouse cursor over the underlined 'BasefmLookup', selecting the drop down menu and choosing the first solution shown (changing the class to inherit from MyFirstFormWithDatabase.BasefmLookp, see picture). This shows the power of Intellisense that follows what you are writing in the code. To give you some background: Visual Studio cannot accept the same parameters for our frmNPLookup twice (like the forms colour and its size). By inserting the code 'Inherits BasefmLookup', this is exactly what happened in a part of the code hidden from our view. Visual Studio's Intellisense can correct this for us without seeing what code was actually changed. Don't worry, our error message is gone.

Insert the same Inheritance statement in the code of the frmRPLookup form.

You can see the visual inheritance at work already by looking at the frmNPLookup or frmRPLookup by looking at this form in Visual Studio in the Design view (double click one of these forms in the Solution Explorer). You can see the OK and Cancel button on the form, which buttons are 'inherited' from the base form. Resize your form if the buttons are not visible. Actually, this can be fixed by anchoring the buttons on the base form to the bottom right of the base form, so that they never pop out of sight on inherited forms. Let's fix that right away by selecting each button on the base form and setting the anchoring property to 'bottom, right'.

Run your program to update the code of the base form and immediately close the program again. When looking at the lookup forms again in the Design view, you will now notice that the OK and cancel button follow the bottom of the form when resizing it.

This is the power of Inheritance in VB.NET. Create it once, use it endlessly. To finalise this part, changes the 'text' property of both lookup windows (not the base form, but the two other forms that inherit from the base form) and insert sensible titles for the forms (I chose 'Lookup Individual' and 'Lookup Companies'). That enhances the user experience when using the program.

To finalise the steps in this part, go to the Form1 and double-click the btnNPLookup (the button just created with the '…' text).

Insert the following code for this button.


Do the same for the button btnRPLookup with the code 'frmRPLookup.ShowDIalog()'.

The 'ShowDialog' part is essential. It basically tells the computer that Form1 will open a new form but that the 'result' of that form (the Dialog) must be returned to Form1. Without explaining it to technically (I can't), in essence the data available on the frmNPLookup form (or frmRPLookup form) will remain available to Form1 until the frmNPLookup is actively disposed of again in the Form1 code. This is a very easy manner of passing code from one form to the other. For the purpose of our sample program it is sufficient. The exact code for this solution to pass data between forms will follow later. First, run the program and notice that a lookup form pops up if you click the lookup button. This lookup form is derived from the base class: Inheritance at work!

In the next step, you will populate the lookup forms and connect the form with the database (finally…). To conclude this tutorial, you will finish the code to pass data from the lookup forms to the main form (form1).

Step 8

Bring up the 'Data Sources' window. Make this window visible by: View → Other Windows → Data Sources.

In the Data Sources window, our data source is shown. It is not actually our access database, but a copy of that data which is brought into our program: a Data Set. Visual Studio sets up the code to retrieve info from and to that Data Set and the exchange of info from and to the underlying Access file. The good part is that we do not have to bother with that code. Visual Studio will do all the work.

Click the TbIndividuals line and the dropdown menu. We are going to choose 'Details'.

Now, drag the item 'TbIndividuals' to your form into the tab page. Drag it high on the form because after you let go of the mouse button, you'll see that Visual Studio has added a nice set of labels and textboxes on your form. Each item from the Individuals table in your access file is shown on the form. The best part: each of these text boxes is connected to the database. Visual Studio even added a menu strip to navigate records (rows) in the database.

You can remove the ID lable and corresponding textbox. We do not need it for our program and the software fills in this data itself (remember that in Microsoft Access, this field of the database was set to autonumbering; the computer sorts this out itself…).

Repeat this process for the frmRPLookup and run the program. I found out that I had to resize both lookup forms a bit to show all buttons in the database navigation menu strip in both forms.

Before we move to the final piece of bode (passing data between forms), we need to do something important. When the user fills out data for new Individuals (using the menu strip in the lookup form to add new records of Individuals, inserting data in the text boxes), the user will expect this data to be saved if he closes the lookup form by clicking the well known 'x' button. Therefore, on the Lookup form for Individuals, click the 'save' button in the menu strip.

The code shown for that button is code we need to apply also if the user closes the lookup form by clicking the 'x' button.

Cut this code and create the follow sub at the bottom of with this code.

Private Sub SaveDatabase()
    End Sub

Update the code for the save button with the following code

Private Sub TbCompaniesBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TbCompaniesBindingNavigatorSaveItem.Click

    End Sub

Now, for the lookup form we need to call this SaveDatabase-sub again if the user closes the form. You can create a sub for this event by using the drop down menu's on the top of the form's code in the code editor window (see picture below).

Update the code for the event 'Deactivate' of frmLookupNP with the following code


Now, to the same for the frmRPLookup form.

Run your program, click the button to bring up a Lookup form and try to insert a new record by pressing the yellow + in the menustrip and filling out the text boxes for this new party. Close the lookup form by pressing the 'x' and immediately open the lookup form again. If everything works alright, the record you just added is still there (cycle through the records to find your newly added record). The part of the database is done.

Step 9 (final)

As indicated above, the final step is finalising the code to pass data between forms. The upside of the 'ShowDialog' element of Forms is that the Lookup form, opened by code on the main form (form1), will disappear for the user when he clicks OK, but that the data on that form (e.g., in its text boxes), is still available to Form1 for processing. After the Form1 code has used this data from the Lookup form, it disposes the Lookup form. For the user, it looks like a flawless passing of data from the Lookup form to the main form. Works great.

To finalise this code, go to the code for Form1. In the code for the button to open the Lookup form for Individuals (btnNPLookup) and update the code as follows.


        If frmNPLookup.DialogResult = System.Windows.Forms.DialogResult.OK Then

            txtNPName.Text = frmNPLookup.NPNameTextBox.Text
            txtNPPlaceOfBirth.Text = frmNPLookup.NPPlaceOfBirthTextBox.Text
            txtNPAddress.Text = frmNPLookup.NPAddressTextBox.Text
            txtNPCity.Text = frmNPLookup.NPCityTextBox.Text
            txtNPCountry.Text = frmNPLookup.NPCountryTextBox.Text

            'the following line is executed if the Dialogresult is 'Cancel'j. Basically the 
            'program is told to do nothing
        ElseIf frmNPLookup.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            'Do nothing

        End If
        'we no longer need the data of the database form so we dispose the data of the database form

Do the same for the button btnRPLookup on Form1, but with the following code.


        If frmRPLookup.DialogResult = System.Windows.Forms.DialogResult.OK Then

            txtRPName.Text = frmRPLookup.RPNameTextBox.Text
            txtRPSeat.Text = frmRPLookup.RPSeatTextBox.Text
            txtRPAddress.Text = frmRPLookup.RPAddressTextBox.Text
            txtRPCity.Text = frmRPLookup.RPCityTextBox.Text
            txtRPCountry.Text = frmRPLookup.RPCountryTextBox.Text

            'the following line is executed if the Dialogresult is 'Cancel'j. Basically the 
            'program is told to do nothing
        ElseIf frmRPLookup.DialogResult = System.Windows.Forms.DialogResult.Cancel Then
            'Do nothing

        End If
        'we no longer need the data of the database form so we dispose the data of the database form

Again, in essence Form1 opens a Lookup form and waits until the Lookup form returns with the result of the 'ShowDialog' element. The result is either 'Cancel' or 'OK'. Subsequently, the code in Form1 does something with that result. Upon the result 'Cancel', Form1 does nothing. Upon the result 'OK', the code on Form 1 reaches out to the Lookup form and more precisely the contents of the textboxes on the Lookup form. After the contents of these textboxes is copied into the textboxes of Form1, the Lookup form is no longer required and disposed of.

Run your program and test it thoroughly. Add new and delete records to the database for Individuals and Companies, bring data back from the Lookup form to the Main form and show the data in the main form in a Messagebox.


You have now mastered a couple of things:

  • connecting a database to your program and manipulating the contents thereof
  • working with tabs
  • passing data between forms
  • using inheritance to write sleek code

That's a great result.

We will expand on this program in the next tutorial by enhancing our Form1. Form1 will insert the input text in a Word document and you will add a class to collect multiple 'parties' in a list, which list can be used to output the details of multiple parties to the Word document. Again, powerful stuff.

Computing A-Z | Programming | Software

QR Code
QR Code programming_microsoft_word_-_09_-_working_with_a_database (generated for current page)