SQLStatement exceed 255 characters in word mail merge using ms accessdatabase

[ashish taralekar]

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 OLEDBatabase 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.

 

[Peter Jamieson]

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?

 

[Ashish Taralekar]

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.

 

[Peter Jamieson]

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.
--
Peter Jamieson
http://tips.pjmsn.me.uk

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.

 

[Ashish Taralekar]

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.

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 ofyour 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.
--
Peter Jamiesonhttp://tips.pjmsn.me.uk


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.

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

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?

- Show quoted text -- Hide quoted text -

- Show quoted text -