×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Office FAQ

Using Access Database As Word

Alternative Mailmerge In Word Using Access Database That Does Not Use Words Mailmerge. by mdav2
Posted: 5 Feb 01

This is useful when doing a mail merge where you may require a subsequent mail merge.  For instance, you may have a large list of senders and recipients.  Creating a normal mail merge based on the senders would mean that subsequent mail merge to the recipients cannot be achieved (in Word 97).  The method I have used creates text entries based on the selections of the template user and has no ties to the data source, leaving the user free to perform a further mail merge as required.  

---------
STEP 1
---------

The first step is to download two files from Microsoft and install them on each machine.  These can be found on the URLÆs below.  

* DCOM (Distributed Component Object Model)
http://www.microsoft.com/com/resources/downloads.asp

* MDAC (Microsoft Data Access Components)
http://www.microsoft.com/data/download.htm

If you do not install the same version then you will receive a ôCompile Error: CanÆt Find Project Or Libraryö

---------
STEP 2
---------
The second step is to create the database table in Access.  No other coding or setup is required in Access.  

---------
STEP 3
---------

The third step is to use the code below in Word:

Put This code in the form.
-------------------------------

' For the ADODB object to work you must set a reference
' Select TOOL, REFERENCES from the visual basic menu bar
' Select the MICROSOFT ACTIVE X DATA OBJECTS 2.1 LIBRARY

' Once set within the template they do not need setting again
Dim oConn           As ADODB.Connection
Dim oRS             As ADODB.Recordset

Now you need to create an array to store the details

Private Sub UserForm_Activate()

    Dim nRowCount       As Integer
    Dim nColCount       As Integer
    Dim sArray()
    Dim ArrayRowCounter As Integer
    Dim ArrayColCounter As Integer
    
   
    ' Create a new ADO connection
    Set oConn = CreateObject("ADODB.Connection")
    ' Open our connect (NOTE the OLEDB.4.0 depends on what version of DCOM is installed)
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False"
    ' Execute a SQL statement to retrieve the information
    Set oRS = oConn.Execute(" SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test")
    
    ' Store column count to variable
    nColCount = oRS.Fields.count
    
    ' move to first record in file
    oRS.MoveFirst
    
    ' count the number of columns to size the array
    nRowCount = 0
    Do
        nRowCount = nRowCount + 1
        oRS.MoveNext
    Loop Until oRS.EOF = True
    
    ' Build the array x rows long and y columns wide
    ReDim sArray(nRowCount, nColCount)
    
    ' Move to the top of the recordset
    oRS.MoveFirst
    
    ' Put information into the array
    For ArrayRowCounter = 1 To nRowCount
        For ArrayColCounter = 0 To (nColCount - 1)
            ' Put the value of the field in the array cell
            sArray(ArrayRowCounter, ArrayColCounter) = oRS.Fields(ArrayColCounter)
        Next ArrayColCounter
        ' Move to the next recordset
        oRS.MoveNext
    Next
    
    ' Specify the number and size of columns for the dropdown
    cboSender.ColumnCount = nColCount
    ' The size of all the columns, except the name, are hidden by having a 0 width
    cboSender.ColumnWidths = "2.3in;0in;0in;0in;0in;0in;0in;0in;0in;0in"
    ' Populate the combobox with the array
    cboSender.List() = sArray

    ' close the recordset
    oRS.Close

End Sub

---------
STEP 4
---------

The final stage is to populate the document with the details, depending on the selection.  Put the code in below:

Private Sub cmdPopulate_Click()

    If IsNull(cboSender.Value) Then
        MsgBox "You must select a person from the list before the details can be copied to the letter"
    Else
        If Trim(cboSender.Value) = "" Or IsNull(cboSender.Value) = True Then
            MsgBox "You must select a person from the list before the details can be copied to the letter"
        Else
            'Now create a new recordset to search for the value

            ' Create a new ADO connection
            Set oConn = CreateObject("ADODB.Connection")
            ' Open our connect
            oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Persist Security Info=False"
            ' Execute a SQL statement to retrieve the information
            Dim sqlstr As String
            sqlstr = " SELECT SenderName, ID, SenderTitle, SenderDivision, SenderAddress1, SenderAddress2, SenderAddress3, SenderAddress4, SenderAddress5 FROM Test WHERE ID=" + Str(cboSender.Value) + ""
            Set oRS = oConn.Execute(sqlstr)
            
            ' Replace the bookmark values
            ActiveDocument.Bookmarks("SenderName").Select
            Selection.InsertAfter oRS.Fields("SenderName")
            ActiveDocument.Bookmarks("SenderName2").Select
            Selection.InsertAfter oRS.Fields("SenderName")
            ActiveDocument.Bookmarks("Sendertitle").Select
            Selection.InsertAfter oRS.Fields("SenderTitle")
            ActiveDocument.Bookmarks("Sendertitle2").Select
            Selection.InsertAfter oRS.Fields("SenderTitle")
            ActiveDocument.Bookmarks("SenderDivision").Select
            Selection.InsertAfter oRS.Fields("SenderDivision")
            ActiveDocument.Bookmarks("SenderAddress1").Select
            Selection.InsertAfter oRS.Fields("SenderAddress1")
            ActiveDocument.Bookmarks("SenderAddress2").Select
            Selection.InsertAfter oRS.Fields("SenderAddress2")
            ActiveDocument.Bookmarks("SenderAddress3").Select
            Selection.InsertAfter oRS.Fields("SenderAddress3")
            ActiveDocument.Bookmarks("SenderAddress4").Select
            Selection.InsertAfter oRS.Fields("SenderAddress4")
            ActiveDocument.Bookmarks("SenderAddress5").Select
            Selection.InsertAfter oRS.Fields("SenderAddress5")

            ' put in the non database information

            ' close the recordset
            oRS.Close
    
            ' unload the form
            Unload frmSender

        End If
    End If
    
End Sub

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close