Access a database and insert into a Word document the data that you find there

Article contributed by Bill Coan

The basic concepts involved in doing this aren't very difficult. The following code assumes that you have an Access database called DataBase.mdb stored in a folder called C:\DataBaseFolder. Further, it assumes that this database contains a table called Table_1 and that this table contains two fields, one called Field_1 and the other called Field_2. Finally, it assumes that Field_1 is a numeric field and Field_2 is a text field. 

The code looks up the value in Field_1. If the value is non-zero, the code inserts the text from Field_2 into the currently active Word document, at the current cursor location.  In a real situation, you might need to have bookmarks in your document (and its underlying template). Instead of inserting the database items at the current cursor location, you would insert them at the bookmarked locations. One final tip: In order for this code to run, you must establish a reference, in your template, to Microsoft DAO 3.51 Object Library and Microsoft Datasource Interfaces. To do this, open the VB editor and choose References on the Tools menu. 

Sub GetDataFromDataBase()

'allocate memory for the database object as a whole and for the active record
Dim myDataBase As Database
Dim myActiveRecord As Recordset

'Open a database
Set myDataBase = OpenDatabase("C:\DataBaseFolder\Database.mdb")

'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Table_1", dbOpenForwardOnly)

'Loop through all the records in the table until the end-of-file marker is reached

Do While Not myActiveRecord.EOF

    'If field #1 contains a non-zero value,
    'insert the value of field #2 into the document
    'after the current cursor or selection location

    If myActiveRecord.Fields("Field_1") <> 0 Then
        Selection.InsertAfter myActiveRecord.Fields("Field_2")
    End If

     'access the next record


'Then close the database

End Sub