Adam D
asked on
Access 2003 & Word 2003 - Mail Merge from Access
Can I open a Word 2003 Mail Merge document from Access 2003 in VBA code if I am using that same database as the source for the mail merge document?
Currently receiving "Word was unable to open the data source" most likely due to exclusivity issues.
Code Below:
Currently receiving "Word was unable to open the data source" most likely due to exclusivity issues.
Code Below:
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim strdbPath As String
Dim strSavedName As String
'Word
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(Application.CurrentProject.Path & "\TestLetter.doc")
objWord.Visible = True
With objDoc.MailMerge
.MainDocumentType = wdFormLetters
strdbPath = Application.CurrentProject.FullName
.OpenDataSource Name:=strdbPath, LinkToSource:=True, OpenExclusive:=False, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM qryMergeLetter"
.Destination = wdSendToNewDocument
.Execute
End With 'objDoc.MailMerge
With objWord
.Activate
.Documents.Parent.Visible = True
.Application.WindowState = 1
.ActiveWindow.WindowState = 1
End With 'objWord
ASKER
Good Morning.
I do not specifically open it as exclusive. I choose the database and select Open. There is an option to choose your open method but I do not choose the Exclusive method.
I have read issues where something causes Access to enter exclusive mode when the code is running. I do not see any reason why my code would be causing this issue though.
Thoughts? Thanks.
I do not specifically open it as exclusive. I choose the database and select Open. There is an option to choose your open method but I do not choose the Exclusive method.
I have read issues where something causes Access to enter exclusive mode when the code is running. I do not see any reason why my code would be causing this issue though.
Thoughts? Thanks.
Good evening from this side of the world :-)
The first thing to check is the default open mode - go to Tools > Options > Advanced, and make sure "Default open mode" is set to "Shared".
Best,
Graham
The first thing to check is the default open mode - go to Tools > Options > Advanced, and make sure "Default open mode" is set to "Shared".
Best,
Graham
You don't have any security on the database do you? Either user-level security or DB password?
I found in Word 2003 that there were queries that it did not like. Since you are merging with the query qryMergeLetter tis may be the issue.
As a text try creating a make table query that save the data from qryMergeLetter to a table. Try merging with the table.
I would urge you not to merge with an Access database. Word actually opens another instance of Access that load another copy of your database. This can cause lots of issues.
What I find best is to export your merge data to a csv file with teh first row being the field names. Have Word merge with the csv file.
As a text try creating a make table query that save the data from qryMergeLetter to a table. Try merging with the table.
I would urge you not to merge with an Access database. Word actually opens another instance of Access that load another copy of your database. This can cause lots of issues.
What I find best is to export your merge data to a csv file with teh first row being the field names. Have Word merge with the csv file.
ASKER
Good Morning/Morning (much later morning now on this side) :)
1. Default open is Shared in Access 2000 format (if that matters)
2. No security
3. I do understand Word will open another instance unfortunately but I would like to keep everything contained. I could try it that way though.
As an alternative, which really would be the ultimate goal I think, is to create the merge document from the query, save that document so it is not an active merge then re-open the final document with no merge connections.
Best way to do that?
Thanks.
1. Default open is Shared in Access 2000 format (if that matters)
2. No security
3. I do understand Word will open another instance unfortunately but I would like to keep everything contained. I could try it that way though.
As an alternative, which really would be the ultimate goal I think, is to create the merge document from the query, save that document so it is not an active merge then re-open the final document with no merge connections.
Best way to do that?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Graham.
The code looks good and although I would have preferred to keep it all together I decided to extract the data needed into an excel file format and tell word to look there instead.
However; instead of using your code. I used an easier one line method (see below). Of course I had to have my other previously mentioned code and point Word to this new file. But it works and works well.
Thanks for your and everyone's help.
The code looks good and although I would have preferred to keep it all together I decided to extract the data needed into an excel file format and tell word to look there instead.
However; instead of using your code. I used an easier one line method (see below). Of course I had to have my other previously mentioned code and point Word to this new file. But it works and works well.
Thanks for your and everyone's help.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMergeLetter", strMMFilePath, True
Best regards,
Graham Mandeno