mail merge locking

[judith]

I am trying to mail merge from Access. The first merge works fine but if i
close word and try to run a further merge it opens word but fails to link
back to the
database table. I am using


objWordDoc.Documents.Add templateName


' Set the mail merge data source .
ActiveDocument.MailMerge.OpenDataSource _
Name:=wordLinkBase, _
LinkToSource:=True, _
Connection:="TABLE " & tableName, _
SQLStatement:="SELECT * FROM [" & tableName & "]"

' Execute the mail merge.

Set closeFileName = ActiveDocument
ActiveDocument.MailMerge.Execute
closeFileName.Close wdDoNotSaveChanges



I am using Access XP but with 2000 format

Any suggestions please

 

[Harry]

Hi Judith,

I have faced the same as I want to make a new document based on a template
with connections to my database. Having the database (access2003) opened for
some modification there is a lock on the database.
However I do a mail-merge towards office2003 using a script working real fine.
The only problem I face is that while opening a new document based on the
template I am asked everytime for the fileDSN I use.
perhaps this code can be usefull for you.

'//begin code
Private Sub MS_Word_Click()

Dim WordApp As Object
Dim WordDoc As Word.Document
Dim strConnect As String

Location = "path to your templates\"
FileName = "TemplateName.dot"

Set WordApp = CreateObject("Word.Application")

Set WordDoc = WordApp.Documents.Add(Location & FileName)

Set objView = WordApp.ActiveDocument.ActiveWindow.View
objView.ShowRevisionsAndComments = False
objView.RevisionsView = wdRevisionsViewFinal


WordDoc.MailMerge.OpenDataSource Name:= _
"path and name of your dsn" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=""DSN=MS Access Database;DBQ=path and name of your
database;DefaultDir=path of directory where db is located ;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"";Initial Catalog" _
, SQLStatement:="SELECT * FROM `Q-RelatiePostAdres`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeOther
WordDoc.MailMerge.ViewMailMergeFieldCodes = wdToggle


WordApp.Visible = True

End Sub

'//end code

 

[judith]

Hi Harry

Thanks for your code.

I managed to get mine working by setting the open file to a file name

objWord.Documents.Add templateName
Set MergeFileName = objWord.ActiveDocument


' Set the mail merge data source .
MergeFileName.MailMerge.OpenDataSource _
Name:=wordLinkBase, _
LinkToSource:=True, _
Connection:="TABLE " & tableName, _
SQLStatement:="SELECT * FROM [" & tableName & "]"

Seems to have done the trick but thanks for your ideas.

Hi Judith,

I have faced the same as I want to make a new document based on a template
with connections to my database. Having the database (access2003) opened for
some modification there is a lock on the database.
However I do a mail-merge towards office2003 using a script working real fine.
The only problem I face is that while opening a new document based on the
template I am asked everytime for the fileDSN I use.
perhaps this code can be usefull for you.

'//begin code
Private Sub MS_Word_Click()

Dim WordApp As Object
Dim WordDoc As Word.Document
Dim strConnect As String

Location = "path to your templates\"
FileName = "TemplateName.dot"

Set WordApp = CreateObject("Word.Application")

Set WordDoc = WordApp.Documents.Add(Location & FileName)

Set objView = WordApp.ActiveDocument.ActiveWindow.View
objView.ShowRevisionsAndComments = False
objView.RevisionsView = wdRevisionsViewFinal


WordDoc.MailMerge.OpenDataSource Name:= _
"path and name of your dsn" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=""DSN=MS Access Database;DBQ=path and name of your
database;DefaultDir=path of directory where db is located ;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"";Initial Catalog" _
, SQLStatement:="SELECT * FROM `Q-RelatiePostAdres`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeOther
WordDoc.MailMerge.ViewMailMergeFieldCodes = wdToggle


WordApp.Visible = True

End Sub

'//end code

I am trying to mail merge from Access. The first merge works fine but if i
close word and try to run a further merge it opens word but fails to link
back to the
database table. I am using


objWordDoc.Documents.Add templateName


' Set the mail merge data source .
ActiveDocument.MailMerge.OpenDataSource _
Name:=wordLinkBase, _
LinkToSource:=True, _
Connection:="TABLE " & tableName, _
SQLStatement:="SELECT * FROM [" & tableName & "]"

' Execute the mail merge.

Set closeFileName = ActiveDocument
ActiveDocument.MailMerge.Execute
closeFileName.Close wdDoNotSaveChanges



I am using Access XP but with 2000 format

Any suggestions please