Automating Mail merge between Word 2003 and Access 2003 using VB.NET

[ajagadambe]

Hi all!

I am using VB.Net Pro to open a mail merge word document and use an MS
Access 2003 table.

The thing is the mail merge is working, but I want to automate the
process of selecting the table.

Right now, it opens the correct word document, and looks at the correct
database, but the thing is the use is PROMPTED to select the table. I
want to know how i can automate this. I have been looking for a
solution for the past week and still not successful! - the table i want
to select is the customer table.

The code i am using is the following:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrdApp As New Word.Application
Dim wrdDoc As Word.Document
Dim wrdMerger As Word.MailMerge
Dim strTemplateName As String
Dim strDataSourceDB As String
Dim strDataSourceTable As String
Dim testCmd As New OleDbCommand("SELECT * FROM Customer")

strDataSourceDB = Application.StartupPath.ToString &
"\East-West Travel Agents.mdb"
strTemplateName = Application.StartupPath.ToString &
"\testMergeLetter.doc"
strDataSourceTable = "Customer"

wrdDoc = wrdApp.Documents.Add(strTemplateName)

wrdMerger = wrdDoc.MailMerge


With wrdMerger

.MainDocumentType =
Word.WdMailMergeMainDocType.wdFormLetters

.OpenDataSource(Name:=strDataSourceDB, _
linktosource:=True, addtorecentfiles:=False, _
connection:="TABLE " & "'" & strDataSourceTable &
"'")

.Destination = Word.WdMailMergeDestination.wdSendToPrinter
.Execute()


wrdDoc.Close(False)
wrdApp.Quit()

End With
End Sub



Thank you in advance for any help!!! )

 

[Peter Jamieson]

Try

..OpenDataSource(Name:=strDataSourceDB, _
SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _
SubType:=wdMergeSubTypeAccess)

(I haven't checked in VB.NET but those are the parameters you need in VBA -
In this case the connection type will be OLEDB, and you don't need a
connection string. Many of the parameters to OpenDataSource have no effect,
even in some cases when the documentation suggests they do. The values of
Name (obviously), Connection, SQLStatement and SubType are usually the only
ones you need)

However, you may also need to take account of the following KB Article:

http://support.microsoft.com/kb/825765/en-us

Peter Jamieson

 

[Doug Robbins - Word MVP]

I believe that

.OpenDataSource(Name:=strDataSourceDB, _
linktosource:=True, addtorecentfiles:=False, _
connection:="TABLE " & "'" & strDataSourceTable &
"'")

Should be:

.OpenDataSource Name:=strDataSourceDB, _
linktosource:=True, addtorecentfiles:=False, _
connection:="TABLE " & & strDataSourceTable


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[ajagadambe]

That's excellent Peter, it worked just perfect!!

Thanks a lot for your help!! )

Aparna

Try

.OpenDataSource(Name:=strDataSourceDB, _
SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _
SubType:=wdMergeSubTypeAccess)

(I haven't checked in VB.NET but those are the parameters you need in VBA -
In this case the connection type will be OLEDB, and you don't need a
connection string. Many of the parameters to OpenDataSource have no effect,
even in some cases when the documentation suggests they do. The values of
Name (obviously), Connection, SQLStatement and SubType are usually the only
ones you need)

However, you may also need to take account of the following KB Article:

http://support.microsoft.com/kb/825765/en-us

Peter Jamieson

Hi all!

I am using VB.Net Pro to open a mail merge word document and use an MS
Access 2003 table.

The thing is the mail merge is working, but I want to automate the
process of selecting the table.

Right now, it opens the correct word document, and looks at the correct
database, but the thing is the use is PROMPTED to select the table. I
want to know how i can automate this. I have been looking for a
solution for the past week and still not successful! - the table i want
to select is the customer table.

The code i am using is the following:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrdApp As New Word.Application
Dim wrdDoc As Word.Document
Dim wrdMerger As Word.MailMerge
Dim strTemplateName As String
Dim strDataSourceDB As String
Dim strDataSourceTable As String
Dim testCmd As New OleDbCommand("SELECT * FROM Customer")

strDataSourceDB = Application.StartupPath.ToString &
"\East-West Travel Agents.mdb"
strTemplateName = Application.StartupPath.ToString &
"\testMergeLetter.doc"
strDataSourceTable = "Customer"

wrdDoc = wrdApp.Documents.Add(strTemplateName)

wrdMerger = wrdDoc.MailMerge

With wrdMerger

.MainDocumentType =
Word.WdMailMergeMainDocType.wdFormLetters

.OpenDataSource(Name:=strDataSourceDB, _
linktosource:=True, addtorecentfiles:=False, _
connection:="TABLE " & "'" & strDataSourceTable &
"'")

.Destination = Word.WdMailMergeDestination.wdSendToPrinter
.Execute()

End With
End Sub

Thank you in advance for any help!!! )- Hide quoted text -- Show quoted text -

 

[Manuel João]

Hi all

wich code should i add if the Access Bd has a password???

Thank you in advance.

Manuel João

 

[Peter Jamieson]

Unfortunately, if you have an old-style database password, I think your
options are limited. You can go back to the DDE connection type: Access will
open, and the user will be prompted for the database password. But let's not
go down that route.

You can create an Office Database Connection file (.odc) but you will have
to specify that you want to persist the security information, and specify
the password in plain text using Jet OLEDBatabase Password.

The only way I know to avoid storing the database password in plain text is
to create a UDL file (all it needs is the name of the OLEDB provider, so you
can use it for any Jet .mdb pathname of the database, then open the .udl
instead of the .mdb. Then in the OpenDataSource, you have to specify the
database password.

So for example, if the database is at c:\a\apw.mdb and has password "mypw",
you can create a .udl called c:\a\jet.udl containing the following:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;

Then your OpenDataSource has to be more like:

strDataSourceUDL = "c:\a\jet.udl"
strDataSourceDB = "c:\a\apw.mdb"
' Ideally, you do not embed your password in the code, but
' it is up to you to find a way to avoid doing that
strDatabasePassword = "mypw"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDataSourceDB & _
& ";;Jet OLEDBatabase Password=" & strDatabasePassword & ";"
..OpenDataSource(Name:=strDataSourceUDL, _
COnnection:=strConnection, _
SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _
SubType:=wdMergeSubTypeOther)

(Sorry if I got the VB.NET syntax wrong, and you may need to experiment to
get this exactly right).

You may also be able to do it using an ODBC DSN of some kind and a similar
approach, but the problems are similar.

Unfortunately, if you try to use exactly the same connection string but
specify Name:=strDataSourceDB instead of Name:=strDataSourceUDL, the method
fails, despite the fact that it looks as if you are providing exactly the
same information. I think that if you specify a .mdb or .odc in the Name
parameter, Word just ignores the Connection string. But I'm not sure. Also,
if you get it wrong, Word pops up its datalink dialog box, then if it still
cannot connect with OLEDB, it pops up its ODBC dialog (and probably selects
the wrong database initially), and if that fails, it tries to connect via
DDE. So if anything goes worng, it is a very poor experience for the user.



Peter Jamieson