From Access to Word merge

[oakleyp2]

Office 2003 - WinXP

I have an access database that sends data to a word template. When
click on the button to merge it take longer than it seems it should.
see a few so called alerts at the bottom of the screen..

"starting microsoft access"
"waiting for microsoft acces to accept dde commands"
"initializing dde link with microsoft access"

then it does the merge. bleow is the code. can somene tell me how t
do this in a more efficient manner???

oDoc.MailMerge.OpenDataSource _
Name:="C:\re\support.mdb", _
Connection:="TABLE tblSUPPORT", _
SQLStatement:="SELECT * From tblSUPPORT", _
Subtype:=wdMergeSubTypeWord2000

With oDoc.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True

With .DataSource
.FirstRecord = Forms!frmSupport.CurrentRecord
.LastRecord = Forms!frmSupport.CurrentRecord
End With

.Execute Pause:=True

End Wit

 

[Cindy M -WordMVP-]

Hi Oakleyp2,

Office 2003 - WinXP

I have an access database that sends data to a word template. When i
click on the button to merge it take longer than it seems it should. i
see a few so called alerts at the bottom of the screen..

"starting microsoft access"
"waiting for microsoft acces to accept dde commands"
"initializing dde link with microsoft access"

then it does the merge. bleow is the code. can somene tell me how to
do this in a more efficient manner???

More efficient would certainly be an ODBC connection, rather than DDE.
DDE is going to be slow because it has to communicate with the Access
application interface. ODBC "talks" directly with the data. The downside
of this is that you won't pick up any of the numeric or date formats, and
you can't use parameter queries or queries that use VBA functions you
define in your database.

In order to test the ODBC connection (and record a macro with the correct
syntax) you need to activate "confirm conversions on open" in
Tools/Options/General. You can then select the connection method after
choosing the *.mdb file.

oDoc.MailMerge.OpenDataSource _
Name:="C:\re\support.mdb", _
Connection:="TABLE tblSUPPORT", _
SQLStatement:="SELECT * From tblSUPPORT", _
Subtype:=wdMergeSubTypeWord2000

With oDoc.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True

With .DataSource
.FirstRecord = Forms!frmSupport.CurrentRecord
.LastRecord = Forms!frmSupport.CurrentRecord
End With

.Execute Pause:=True

End With

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[oakleyp2]

is this not how i would do it??

oDoc.MailMerge.OpenDataSource _
Name:="", _
connection:="DSN=forms;DBQ=c:\re\support.mdb;", _
SQLStatement:="SELECT * FROM tblsupport", _
Subtype:=wdMergeSubTypeWord200

 

[Cindy M -WordMVP-]

Hi Oakleyp2,

is this not how i would do it??

oDoc.MailMerge.OpenDataSource _
Name:="", _
connection:="DSN=forms;DBQ=c:\re\support.mdb;", _
SQLStatement:="SELECT * FROM tblsupport", _
Subtype:=wdMergeSubTypeWord2000

This looks like it could work. I'm not sure I'd expect to
see wdMergeSubTypeWord2000, but if it works, it works

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail