Insert database query results as a table programmatically in Word 2010


An astute user recently pointed out that buried on the Customize Ribbon tab of the Word Options dialog box is an otherwise hidden command, Insert Database, that lets you create a table in a Word document and populate that table with the results of a database query:

image

You can do all this by making a few selections in the Database dialog box that opens when you choose that command from the customized ribbon:

image

You might also not be aware that you can accomplish the same thing programmatically. The following code sample shows how to insert a table into a Word document that displays the contents of the Category table from the Northwind sample database (Microsoft Access):

Sub InsertCategoriesTable()

‘ Insert database table into document

    Selection.Range.InsertDatabase Format:=11, Style:=191, LinkToSource:=False,  _  
        SQLStatement:="SELECT * FROM `Categories`" & "",  _ 
        DataSource:="C:\Users\username\Documents\Access Databases\Northwind.accdb", _
        From:=-1, To:=-1, IncludeFields:=True

End Sub

The sample uses the InsertDatabase method of the Range object to insert the results of a database query into a table at the selected location in Word. The InsertDatabase method takes a number of optional parameters that let you customize both the database query and the format of the table to be inserted, as shown in the following table:

image

In the sample, I’ve passed values for the Format and Style parameters that result in the table looking like this:

image

The value passed for the SQLStatement parameter, "SELECT * FROM `Categories`", tells Word to query all the records in the Categories table in the Northwind database. The values I’ve passed for the From and To parameters, 1 and –1, tell Word to display all the records in the database table.

Because I’m querying data from Access, it is not necessary for me to pass a value for the optional Connection parameter. See the InsertDatabase topic for more information about this parameter.


Comments (0)