Export (0) Print
Expand All

Walkthrough: Loading Jet Data into a Table in Word

Office 2003
 

Mary Chipman
MCW Technologies, LLC

September 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Word 2003
    Microsoft Visual Studio .NET 2003

Summary: Demonstrates how to programmatically load data into a Word table from an Access/Jet database by taking advantage of ADO.NET to access data, and using Visual Studio Tools for the Microsoft Office System to create your project. (10 printed pages)

Contents

Introduction
Prerequisites
Getting Started
Creating the Word Table
Connecting to the Database and Inserting Data
Conclusion

Introduction

In this walkthrough, you'll first create a Microsoft® Office Word 2003 table to contain data retrieved from the Microsoft Office Access Northwind sample database. You'll then use ADO.NET to connect to and retrieve data from the Northwind.mdb Access/Jet database, selecting the top ten most expensive products with their prices. You'll insert the data into the Word table.

Prerequisites

To complete this walkthrough, the following software and components must be installed on the development computer:

  • Microsoft Visual Studio® .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
  • Microsoft Visual Studio Tools for the Microsoft Office System
  • Microsoft Office Professional Edition 2003
Tip   This demonstration assumes that if you're a Visual Basic .NET programmer, you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project), although it is not required. Setting the Option Strict setting to On requires a bit more code, as you'll see, but it also ensures that you don't perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option far outweighs the difficulties it adds as you write code.

Getting Started

To get started, you'll need to use Visual Studio Tools for the Microsoft Office System to create a Visual Studio .NET project that works with Microsoft Office Word 2003.

To create a Word Document project

  1. Start Visual Studio .NET, and on the File menu, point to New, and click Project.
  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.
  3. In the Templates pane, select Word Document.
  4. Name the project JetDataWordTable, and store it in a convenient local path.
  5. Accept the defaults in the Microsoft Office Project Wizard, and click Finish to create the project and the new Word document.

    Visual Studio .NET opens the ThisDocument.vb or ThisDocument.cs file in the Code Editor for you.

Creating the Word Table

In this example, you'll use the document already created by the project. You'll add a procedure that creates the Word table, formats it, and inserts the data from the Northwind sample Jet database. You'll then call the procedure from the Open event handler for the ThisDocument object, although you could also invoke the procedure from a button, menu, or form.

To create the table in Word

  1. Within the OfficeCodeBehind class, add the following procedure:
    ' Visual Basic
    Private Sub CreateWordTable()
        ' Clear the current contents.
        ThisDocument.Range.Delete()
    
        ' Move to start of document.
        Dim rng As Word.Range = _
            ThisDocument.Range(Start:=0, End:=0)
    
        ' Insert Title and paragraph marks.
        rng.InsertBefore("Top Ten Products")
        rng.Font.Name = "Verdana"
        rng.Font.Size = 16
        rng.InsertParagraphAfter()
        rng.InsertParagraphAfter()
        rng.SetRange(rng.End, rng.End)
    
        ' Add the table.
        rng.Tables.Add( _
            Range:=ThisDocument.Paragraphs(2).Range, _
            NumRows:=1, NumColumns:=2)
    
        ' Set variable to point to new table.
        Dim tbl As Word.Table = ThisDocument.Tables(1)
    
        ' Format the table.
        tbl.Range.Font.Size = 12
        tbl.Range.Font.Name = "Verdana"
        tbl.Borders.InsideLineStyle = _
            Word.WdLineStyle.wdLineStyleSingle
        tbl.Borders.OutsideLineStyle = _
            Word.WdLineStyle.wdLineStyleDouble
    
        ' Set the column widths.
        tbl.Columns(1).SetWidth( _
            ColumnWidth:=ThisApplication.InchesToPoints(2.5), _
            RulerStyle:=Word.WdRulerStyle.wdAdjustNone)
        tbl.Columns(2).SetWidth( _
            ColumnWidth:=ThisApplication.InchesToPoints(1.5), _
            RulerStyle:=Word.WdRulerStyle.wdAdjustNone)
        tbl.Cell(1, 1).Range.Text = "Product Name"
    
        ' Right-align second column.
        Dim rngCell As Word.Range = tbl.Cell(1, 2).Range
        rngCell.Text = "Unit Price"
        rngCell.ParagraphFormat.Alignment = _
            Word.WdParagraphAlignment.wdAlignParagraphRight
    End Sub
    
    // C#
    private void CreateWordTable() 
    {
        // Clear the current contents.
        Object start = Type.Missing;
        Object end = Type.Missing;
        Object unit = Type.Missing;
        Object count = Type.Missing;
        ThisDocument.Range(ref start, ref end).
            Delete(ref unit, ref count);
    
        // Move to start of document.
        start = 0;
        end = 0;
        Word.Range rng = 
            ThisDocument.Range(ref start, ref end);
    
        // Insert Title and paragraph marks.
        rng.InsertBefore("Top Ten Products");
        rng.Font.Name = "Verdana";
        rng.Font.Size = 16;
        rng.InsertParagraphAfter();
        rng.InsertParagraphAfter();
        rng.SetRange(rng.End, rng.End);
    
        // Add the table.
        Object defaultTableBehavior = Type.Missing;
        Object autoFitBehavior = Type.Missing;
        rng.Tables.Add(ThisDocument.Paragraphs[2].Range, 1, 2, 
            ref defaultTableBehavior, ref autoFitBehavior);
    
        // Set variable to point to new table.
        Word.Table tbl = ThisDocument.Tables[1];
    
        // Format the table.
        tbl.Range.Font.Size = 12;
        tbl.Range.Font.Name = "Verdana";
        tbl.Borders.InsideLineStyle =
            Word.WdLineStyle.wdLineStyleSingle;
        tbl.Borders.OutsideLineStyle =
            Word.WdLineStyle.wdLineStyleDouble;
    
        // Set the column widths.
        tbl.Columns[1].SetWidth(
        ThisApplication.InchesToPoints((float)2.5),
        Word.WdRulerStyle.wdAdjustNone);
        tbl.Columns[2].SetWidth(
            ThisApplication.InchesToPoints((float)1.5),
            Word.WdRulerStyle.wdAdjustNone);
        tbl.Cell(1, 1).Range.Text = "Product Name";
    
        // Right-align second column.
        Word.Range rngCell = tbl.Cell(1, 2).Range;
        rngCell.Text = "Unit Price";
        rngCell.ParagraphFormat.Alignment =
            Word.WdParagraphAlignment.wdAlignParagraphRight;
    }
    
    
  2. Modify the ThisDocument_Open procedure, adding the following code:
    ' Visual Basic
    CreateWordTable()
    
    // C#
    CreateWordTable();
    
    
  3. Select Save All on the File menu to save your project.
  4. Press F5 to run the project, verifying that the Word document contains the correct header information, as shown in Figure 1.

    Figure 1. The new table

  5. Close Word and return to Visual Studio .NET.

Connecting to the Database and Inserting Data

You'll use the System.Data.OleDb classes to connect to and query the Jet database.

To connect to the database

  1. Scroll to the top of the open code file and type the following statements:
    ' Visual Basic
    Imports System.Data.OleDb
    
    // C#
    using System.Data;
    using System.Data.OleDb;
    
    
  2. Scroll to the end of the CreateWordTable procedure, and press ENTER to insert some new lines of code. Create a String variable to point to the location of the Northwind.mdb Jet database on your computer, and another String variable to submit a query to return the top ten products by price. Modify the path as necessary to match your computer's configuration:
    ' Visual Basic
    Dim strMDB As String = _
        "C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb"
    
    Dim strSQL As String = _
        "SELECT TOP 10 ProductName, UnitPrice FROM Products " & _
        "ORDER BY UnitPrice DESC"
    
    // C#
    string strMDB = 
        "C:\\program files\\microsoft office\\" + 
        "Office11\\Samples\\Northwind.mdb";
    string strSQL =     
        "SELECT TOP 10 ProductName, UnitPrice FROM Products " +     
        "ORDER BY UnitPrice DESC";
    
    
  3. Continuing in the sample procedure, declare OleDbConnection, OleDbDataReader, and OleDbCommand object variables:
    ' Visual Basic
    Dim cnn As OleDbConnection
    Dim sdr As OleDbDataReader
    Dim cmd As OleDbCommand
    
    // C#
    OleDbConnection cnn = null;
    OleDbDataReader sdr = null;
    OleDbCommand cmd = null;
    
    
  4. Add the following code to connect to the Jet database (you can leave out the comments within the code, when typing):
    ' Visual Basic
    Try
        ' Open the connection.
        cnn = New OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strMDB)
    
        ' If your database is secured with user-level security, 
        ' use a connection string like this:
        'Dim strConnection As String = _
            ' "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            ' "Data Source=" strMDB & _
            ' "User ID=TheUserName;" & _
            ' "Password=ThePassword" 
    
        cnn.Open()
    
        ' Open the Command and DataReader.
        cmd = New OleDbCommand(strSQL, cnn)
        sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    
    Finally
        If Not sdr Is Nothing Then
            sdr.Close()
        End If
    End Try
    
    // C#
    try 
    {
        // Open the connection
        cnn = new OleDbConnection(
            "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + strMDB);
    
        // if your database is secured with user-level security, 
        // use a connection string like this:
        // string  strConnection =      
        //   "Provider=Microsoft.Jet.OLEDB.4.0;" +      
        //   "Data Source=" strMDB +      
        //   "User ID=TheUserName;" +      
        //   "Password=ThePassword" ;
    
        cnn.Open();
    
        // Open the Command and DataReader.
        cmd = new OleDbCommand(strSQL, cnn);
        sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
    } 
    catch (Exception ex) 
    {
        MessageBox.Show(ex.Message);
    } 
    finally 
    {
        if (sdr != null ) 
        {
            sdr.Close();
        }
    }
    
    
  5. Within the Try block (at the end of the block), add the following code, which retrieves the data. This code inserts the data into new rows in the Word table starting on the second row. The code also formats the first row of the table in bold:
    ' Visual Basic
    ' Start on second row.
    Dim intRow As Integer = 2
    
    ' Retrieve the data and insert into new rows.
    While sdr.Read()
        tbl.Rows.Add()
        tbl.Cell(intRow, 1).Range.Text = _
            sdr(0).ToString
        tbl.Cell(intRow, 2).Range.Text = _
            String.Format("{0:c}", sdr(1))
        intRow += 1
    End While
    
    ' Bold the column heads.
    tbl.Rows(1).Range.Bold = 1
    
    // C#
    // Start on second row.
    int intRow = 2;
    Object beforeRow = Type.Missing;
    
    // Retrieve the data and insert into new rows.
    while (sdr.Read())
    {
        tbl.Rows.Add(ref beforeRow);
        tbl.Cell(intRow, 1).Range.Text =
            sdr[0].ToString();
        tbl.Cell(intRow, 2).Range.Text =
            String.Format("{0:c}", sdr[1]);
        intRow += 1;
    }
    // Bold the column heads.
    tbl.Rows[1].Range.Bold = 1;
    
    
  6. Select Save All on the File menu to save your project.
  7. Press F5 to run the project, verifying that the Word table contains the correct data, as shown in Figure 2.

    Figure 2. A portion of the completed Word table

Conclusion

Programmatically connecting to an Access/Jet database to load data into a Word table is easy once you understand how to take advantage of ADO.NET for data access and how to create a Word table to contain the data.

Show:
© 2014 Microsoft