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
myActiveRecord.MoveNext
Loop
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub