Microsoft Word Merge with Bookmarks and Multiple Pages

[Tony_VBACoder]

With Access 2000, I am trying to output the results of a
query (multiple rows) to a Word Document that includes
Bookmarks. My Word document is one page long, with a
bunch of Bookmarks that I want to populate from my query
in Access. I am able to populate my Word document for 1
page, but how do I accomplish this when my query has many
rows and I need to create a new page within Word for each
row of data in my query? My sample function to populate
my Bookmarks is below which will produce one page:

Option Compare Database
Option Explicit

Private objWord As Word.Application
Private objDocument As Word.Document

Public Sub CreateWordLetter(sWordTemplate As String)

' Create and launch Word
Set objWord = New Word.Application

' Make Word visible
objWord.Visible = True

' Point the Document object at a new document based on the
template
Set objDocument = objWord.Documents.Add(sWordTemplate)

' Populate all the bookmarks in the document
' Loop through the query results
Set rst = CurrentDb.OpenRecordset("qryWordData")
Do While Not rst.EOF
UpdateBookmarkProc "LetterDate", rst!LetterDate
<<< update the other fields from the query >>>
rst.MoveNext
Loop

Set objDocument = Nothing

End Sub


Public Sub UpdateBookmarkProc(sBookmarkToUpdate As String,
sTextToUse As String)
Dim bmRange As Range

' Covert a Null value to empty string - if the value is
NULL it will delete the bookmark
sTextToUse = IIf(IsNull(sTextToUse), "", sTextToUse)

Set bmRange = objDocument.Bookmarks
(sBookmarkToUpdate).Range
bmRange.Text = sTextToUse
objDocument.Bookmarks.Add sBookmarkToUpdate, bmRange

End Sub

 

[Stewart Tanner]

Tony sounds like you should be using a mail merge instead of bookmarks, but
to do what you want you would have to

do until rst.eof
copy the original page from word including bookmarks
update bookmarks
insert word page break
past text and bookmarks
rst.movenext

etc

 

[Tony_VBACoder]

How does a MailMerge behave when my Access DB is Secured?
My users have to log into the Secured Access DB in order
to use it. I don't want to have to hard code any
passwords or ID's into my Word Template.

 

[Stewart Tanner]

Tony,

I have performed mail merges from secure dbs via code plenty of times
without hardcoding anything. you are opening the merge document from access
so you are controlling the process. If you are really concerned put an
unsecured db with your app, create a temptable in there with the merge data,
merge the document and delete the temp table.

 

[Tony_VBACoder]

Stewart, I am having a problem getting my Word 2002 to
connect to my Secured Access DB. I have created a Query
that has all the fields that I need to merge with a Word
Document, however, when I go through the Wizard
(Tools>Letters and Mailings>Mail Merge Wizard) and at Step
#3 under the "Use an existing list" I select "Browse".
Then when I select my secured MDB, I am prompted to log
into the database from the "Data Link Properties" dialog
box. On this form, in #1, my MDB is listed and in #2, I
enter the User Name of the owner of the database, that way
I am certain of not having any problems. I uncheck
the "Blank Password" and enter in this Owner's password.
If I click the "Test Connection" button, I get the
following error "You do not have the neccessary
permissions to use...", which is the same error I get if I
double-click on my secured MDB. To launch my secured DB,
I use the icon that the Security wizard created that has
the path to my MDB along with the MDW file.


What am I doing wrong?

 

[Tony_VBACoder]

Could you post some of your sample code showing how you
were able to successfully perform a mail merge against a
Secured DB? Also, any help in terms of getting my Word
Document to connect to a secured DB would be of great
help, as I am having no luck finding any simple examples
of how to accomplish this - against an unsecure DB it is
quite easy, but once the DB is Secure, it becomes more
difficult.