Word Mail Merge from Access 1

LarryDeLaruelle · May 19, 2000

I am trying to set up a mail merge function in Access '97 (as described in MS Document Q159328) and it runs OK up to a point: a new instance of Access is opened (which is another problem for another day); Word opens; then I get a run time error (5992) "Word was unable to open the data source". Here is the code (pretty much as I picked it up from the MS doc): Public Function Mergeit(strOaklawn As String) Dim objWord As Word.Document Set objWord = GetObject("C:\WordForms\DisSum.dot", "Word.Document&quot

' Make Visible objWord.Application.Visible = True ' Set the mail merge data source objWord.MailMerge.OpenDataSource _ Name:="G:\database\TCC Reports\TCCReports.mdb", _ linktosource:=True, _ Connection:="QUERY qbeWordForm1", _ sqlstatement:="SELECT * FROM [qbeWordForm1] WHERE [OaklawnNum] = '" & strOaklawn & "' ;" ' Execute the Mail Merge objWord.MailMerge.Execute End Function I tried the Help button when I got the run time error and was politely informed that the "Topic does not exist". When I click on debug, it highlights the entire .OpenDataSource line with the yellow arrow pointing at the last line (the SQL statement). My guess is that something is not right with the SQL statement but I am at a loss to figure out just what that might be. My undieing gratitude to anyone who can set me on the path to enlightenment.

DougP · May 20, 2000

I copied your function and will paste it in Access it to see whats up. As for Word Opening Access that's a given cause that's the way it was written. I presume you are using Access for more than just a place to store address for Word mail merge. I've done the same thing slightly differntly. Put a button on an Access form that opens Word and merges the data at the same time. The form of course is where you are making change's to the data for the mail merge. That way you already have Access open and it won't launch Access again The Word merged data source could be a quey so it would return just the records you needed. I create a macro in Word that Merged, Printed, and closed Word. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

DougP · May 20, 2000

Try this:     Dim objWord As Object     Set objWord = GetObject(, "Word.application&quot

Set objWord = GetObject("C:\WordForms\DisSum.dot&quot

' Make Visible     objWord.Application.Visible = True I got it to work. I don't have your data so can't check that DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

LarryDeLaruelle · May 21, 2000

Thanks for the reply, Doug.  I have the merge working OK from within Word but because of the number of documents to be merged I would like to do it via Access. I probably should have included some background with my question.  I work for a Residential Treatment Center for disturbed and abused children.  On admission a number of clinical forms (with basic identifier data) are created and stored in a network folder for that child.  That folder is then available to the therapist to add case notes, etc.  There are a total of 18 documents in this set (with more to be created); some are related to admission, some to on-going service and some to discharge. What I would like to do is to set up an Access form with a combo box to select the name of the new admission and check boxes to identify the specific forms to be created.  Access/Word would then merge the identifier info into each form and save them (Admission Name + Document Type = FileName) to the folder.  This same scenario would be created for on-going service forms and discharge forms. Our Admissions Clerk is very happy with the Merge routine I have set up now, but it is cumbersome and slow because she must open each document, modify the selection criteria, merge, save and close.  From what I have seen so far, I know that doing this via Access may be at least as slow, but it would avoid the repetition and eliminate possible entry errors.. I hope this makes my problem a little clearer at least in terms of why I am trying to do it this way. Thanks. Larry De Laruelle <a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a> <a href= > </a>

JimConrad · May 22, 2000

You may want to look here... <A HREF="

http://www.ulster.net/~hfeddema/"

TARGET="_new">

http://www.ulster.net/~hfeddema/</A>

Jim Conrad <a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a> <a href= > </a>

DougP · May 22, 2000

I made an Access based Quoting System that had 6 word docs but the principle is the same. 1st Create a form that has all of the data on it for each Word doc. Or create several forms that have most of the data for several Similar Word Docs. Next have the Word Docs look at Access Queries Not tables. Those Queries then look at the particular Forms for their Input instead of prompting. So In word when the Documnt is opened it is looking at a query which is looking at a form. No fiddling is done in Word to get a specific record or records the Access quieries handle that. OK Now on your form you have several buttons or a Combo box that when clicked it launches a specific Word Doc. You could also make a Macro in Word that printed the document. So the call to Word would open a particualr doc, Print it out then close and be back in Access ready for the next one. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

anasazii · Feb 4, 2002

Hello Larry!

I was just curious if you ever got this working? I'm trying something similar, with Access 2000, and it works great except that every time I run the merge, I get a message saying

"The database has been placed in a state by user 'Admin' on machine 'Erin' that prevents it from being opened or locked."

Wondered if you ran into this problem at all, and also if you ever managed to work around the second instance of Access opening?

I have basically the same code as you show above...

Set objWord = GetObject("C:\My Documents\N-K Properties\Super Suds\refundLetter.doc", "Word.Document&quot

strCriteria = "[letterNeeded] = 'Yes'"
Set rst = CurrentDb.OpenRecordset("refund_info", dbOpenDynaset)
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox ("No letters found to generate.&quot

Else
' Make Word visible.
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
name:="C:\Windows\Desktop\CarWash Refunds.mdb", _
LinkToSource:=True, _
Connection:="QUERY refundLetterQry"
'Execute the mail merge.
objWord.MailMerge.Execute

End If

I've tried setting permissions differently under Tools-Security, but still am getting message. Any ideas would be greatly appreciated!

Thanks, Janel

LarryDeLaruelle · Feb 5, 2002

Jane:

I was under a time constraint so I just set it up do do the merge from Word. Works OK that way. Never did resolve this issue. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

GaryRW · Sep 12, 2002

anasazii,

just for info, (i assume it's a long dead question but...)

I had exactly the same error coming up,

it was me altering the database in some way which meant I had the database opened exclusively. It took me ages to work out that even just clicking save on the toolbar converted the database to exclusive mode (i think???), meaning no-one else, and no other program could get access to it or the data in it.

I got round the problem of copying the data across into a second database and linking to that, certainly whilst I was developing the mail merge

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Word Mail Merge from Access 1

LarryDeLaruelle

Technical User

DougP

MIS

DougP

MIS

LarryDeLaruelle

Technical User

JimConrad

Programmer

DougP

MIS

anasazii

Programmer

LarryDeLaruelle

Technical User

GaryRW

Programmer

Similar threads

Part and Inventory Search

Sponsor