Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
ashish taralekar ashish taralekar is offline
external usenet poster
 
Posts: 5
Default SQLStatement exceed 255 characters in word mail merge using ms accessdatabase

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(Na me:="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;Pass word="""";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.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default SQLStatement exceed 255 characters in word mail merge using ms access database

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?

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

"ashish taralekar" wrote in message
...
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(Na me:="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;Pass word="""";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.


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
ashish taralekar ashish taralekar is offline
external usenet poster
 
Posts: 5
Default SQLStatement exceed 255 characters in word mail merge using msaccess database

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"
wrote:
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?

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"ashish taralekar" wrote in message

...



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(Na me:="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;Pass word="""";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.- Hide quoted text -


- Show quoted text -


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default SQLStatement exceed 255 characters in word mail merge using ms access database

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

"Ashish Taralekar" wrote in message
...
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"
wrote:
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?

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

"ashish taralekar" wrote in message

...



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(Na me:="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;Pass word="""";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.- Hide quoted text -


- Show quoted text -


  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
ashish taralekar ashish taralekar is offline
external usenet poster
 
Posts: 5
Default SQLStatement exceed 255 characters in word mail merge using msaccess database

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

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

"Ashish Taralekar" wrote in message

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



wrote:
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?


--
Peter Jamiesonhttp://tips.pjmsn.me.uk


"ashish taralekar" wrote in message


...


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(Na me:="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;Pass word="""";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.- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge combines characters into one column LCrowe Mailmerge 1 June 7th 07 05:09 PM
Opendatasource sqlstatement Roberto Mailmerge 1 January 5th 06 10:37 AM
Mail Merge with Chinese Characters Ausmerica Mailmerge 8 May 31st 05 03:22 PM
Word Mail Merge with QueryString longer than 255 characters Sebastian Martinez Mailmerge 5 February 7th 05 01:13 PM
International characters in mail merge sandra Mailmerge 1 December 23rd 04 05:42 AM


All times are GMT +1. The time now is 05:15 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"