SQLStatement exceed 255 characters in word mail merge using ms access database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ashish taralekar - 27 Dec 2007 12:56 GMT

Hi,
I am doing mail merge automation using vb.net and MS Access as
database. I passed the following parameters to the OpenDataSource
method of MailMerge.
Wordapp.ActiveDocument.MailMerge.OpenDataSource(Name:="C:\Test.mdb",
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
Format:=Word.WdOpenFormat.wdOpenFormatText,
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\Test.mdb;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5",
SQLStatement:=SelectQuery1forMailMerge,
SQLStatement1:=SelectQuery2forMailMerge,
SubType:=Word.WdMergeSubType.wdMergeSubTypeAccess)

In above code i have used two sql queries (SelectQuery1forMailMerge,
SelectQuery2forMailMerge) because my select query exceeds 255
characters.
But, by using the above code I am getting the "Confirm Data Source"
dialog box.
So, please suggest me some solution to suppress this dialog and to
work the mail merging successful.

Thanks,
Ashish Taralekar.

Reply to this Message

Peter Jamieson - 27 Dec 2007 17:36 GMT

Which version of Word/Access?

If you join the two pieces of SQL code together, ensure that there are
spaces where they are required. eg.

QueryPart1 = "SELECT *"
QueryPart2 = "FROM mytable"

will fail because the complete statement will be
SELECT *FROM mytable

but

QueryPart1 = "SELECT *"
QueryPart2 = " FROM mytable"

Should be OK.

If that is not the problem, can you post the two pieces of SQL query code
here?

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Hi,
> I am doing mail merge automation using vb.net and MS Access as
[quoted text clipped - 23 lines]
> Thanks,
> Ashish Taralekar.

Reply to this Message

Ashish Taralekar - 28 Dec 2007 14:56 GMT

Hi,
I am using Word 2003, Access XP.
and the query parts are having the correct spaces.

SelectQuery1forMailMerge = "SELECT Salutation As [Civilité], FirstName
As [Prenom],LastName As [Nom],Company As [Entreprise],JobTitle As
[JobTitle],Email As [E-mail],Address1 As [Adresse1],Address2 As
[Adresse2], "

SelectQuery2forMailMerge = "Address3 As [Adresse3],City As
[Ville],PostalCode As [Codepostal],State As [État],Country As
[Pays],WebPage As [WebPage],Phone As [Téléphone],Fax As [Fax],Category
As [Categorie],* FROM `MailMergeContacts`"

Regards,
Ashish T.

On Dec 27, 10:36 pm, "Peter Jamieson"
<p...@KillmapSpjjnet.demon.co.uk> wrote:

> Which version of Word/Access?
>
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Reply to this Message

Peter Jamieson - 28 Dec 2007 16:00 GMT

Yes, there is sometimes a limit of 256 characters for the whole query when
you connect via OLE DB (perhaps only with some OLE DB providers.

In this case, you might be able to use ODBC, e.g.

OpenDataSource _
Name:="", _
Connection:="DSN=MS Access Database;DBQ=the full pathname of your mdb;",
_
SQLStatement:="SELECT Salutation As [Civilité],FirstName As
[Prenom],LastName As [Nom],Company As [Entreprise]," & _
"JobTitle,Email As [E-mail],Address1 As [Adresse1],Address2 As
[Adresse2],", _
SQLStatement1:="Address3 As [Adresse3],City As [Ville],PostalCode As
[Codepostal],State As [État]," & _
"Country As [Pays],WebPage, Phone As [Téléphone], Fax, Category As
[Categorie],* FROM [MailMergeContacts]", _
subtype:=wdMergeSubTypeWord2000

Notice that you cannot use "Something As [Something]" . You either need
"Something" or "Something As [Something Else]"

Or you can use a FILE DSN, e.g.

OpenDataSource _
Name:="the full pathname of the FILE DSN that specifies your mdb", _
Connection:="FILEDSN=the full pathname of the FILE DSN that specifies
your mdb;", _
SQLStatement:="SELECT Salutation As [Civilité],FirstName As
[Prenom],LastName As [Nom],Company As [Entreprise]," & _
"JobTitle,Email As [E-mail],Address1 As [Adresse1],Address2 As
[Adresse2],", _
SQLStatement1:="Address3 As [Adresse3],City As [Ville],PostalCode As
[Codepostal],State As [État]," & _
"Country As [Pays],WebPage,Phone As [Téléphone],Fax,Category As
[Categorie],* FROM [MailMergeContacts]"

However, ODBC will not pass non-ANSI Unicode characters correctly. If you
need to do that, but cannot alter the source database, you can consider
creating a second .mdb with
a. a table linked to MailMerge Contacts
b. the query you want

and use that query as the data source.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Which version of Word/Access?
>
[quoted text clipped - 53 lines]
>
> - Show quoted text -

Reply to this Message

Ashish Taralekar - 31 Dec 2007 09:38 GMT

Thanks.
Its working now.
The alias name in the query for some of the columns were same that's
why it was giving me error "Word cannot open the database."
But, after that i changed the alias name and now the mail merge is
working successfully.
Thanks once again.

Regards,
Ashish Taralekar.

On Dec 28, 9:00 pm, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
wrote:

> Yes, there is sometimes a limit of 256 characters for the whole query when
> you connect via OLE DB (perhaps only with some OLE DB providers.
[quoted text clipped - 122 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: