Mail Merge DDE

[Yang]

I'm using MS Word 2003 to merge data from MS Access 2003 database. The access
data is using Workgroup that is why I have to use the DDE link. However, I
understand that everytime if we start the mail merge it will launch Access as
well. That is OK for me. But I'm curious that if I save the doc file as dot
(Because I don't want anyone to change the content so I make it as a
template), each time I launch the dot file it will launch two instance of Ms.
Access. My question is, is there anyway we can get rid of opening the Ms.
Access? Thanks in advances.

 

[Peter Jamieson]

You can avoid using DDE if the /only/ problem is that you are using a
Workgroup (e.g. you are not trying to connect to a parameter query) /and/
you do not mind having the Workgroup security details stored in either a
..odc file or the Word document

For example, if you have
a. a database called c:\mydbs\mydb.mdb
b. a workgroup security database called c:\mysec\secured.mdw
c. a user id called "me"with password "pw" that can access the data you
need

and you want to get the data from a table called "mytable",

then you should be able to connect to your data source using the following
VBA code. You will also need an empty .odc file (you can create an empty
file in Notepad, then rename it):

Sub myConnect()

Dim strConnection As String
Dim strDBName As String
Dim strODCName As String
Dim strPassword As String
Dim strSecurityDBName As String
Dim strUserID As String

strDBName = "c:\mydbs\mydb.mdb"
strODCName = "c:\mydbs\mydb.odc"
strPassword = "pw"
strSecurityDBName = "c:\mysec\secured.mdw"
strUserID = "me"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & strPassword & ";" & _
"User ID=" & strUserID & ";" & _
"Data Source=" & strDBName & ";" & _
"Jet OLEDB:System database=" & strSecurityDBName & ";"
Debug.Print strConnection


ActiveDocument.MailMerge.OpenDataSource _
Name:=strODCName, _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM [mytable]", _
SubType:=wdMergeSubTypeOther
End Sub

There are a couple of other ways to do this using a .odc