OpenDataSource in vba creates an error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

YT - 02 Apr 2004 03:41 GMT

Hi,
I have a problem connecting to data source in mail merge.

I created a datasource through the Select Data Source screen to connect to SQL Server. And everything was fine. Then I recorded a process to open this datasource on a macro and run it, and it returned a message
Record 1 contained too few data fields, Record 2 contained too few data fields, ... (3 times, because the table that I connected to has 3 columns), and I clicked OK each time.
Then it returned with Invalid Merge Field, and on it is has this
This merge field is used in the main document, but it does not exist in the data source [FieldName
-----------------------------------------------------------------------------------------------------------------
You can remove the invalid merge field from the main document. [Remove Field button
-----------------------------------------------------------------------------------------------------------------
Or, you can replace it with a valid merge field from the data source [M__ (drop down combo box)
Sample data
[empty box] [OK button] [Cancel button
------------------------------------------------------------------------------------------------------------------

Now, the thing is that M__ field does not even exist in the table that I connect to.

If I click OK, then it just returned empty string.
If I click Remove Field Button, then it removes the merge field
If I click Cancel, then it returned 'Error! MergeField was not found in header record of data source.

When I step into the code in VB Editor, the error happened when trying to set the datasource
----------------------------------------------------------------------------------------------------------------------------------
ActiveDocument.MailMerge.OpenDataSource Name:=
"C:\Documents and Settings\yenny\My Documents\My Data Sources\svr_sql_itdev Cis_Dev vFactFindClient.odc"
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto, Connection:=
"Provider=SQLOLEDB.1;Password=unisys;Persist Security Info=True;User ID=ordcisuser;Initial Catalog=Cis_Dev;Data Source=svr_sql_itdev;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SVR_DOT_NET;Use Encryption for Data=False;T"
, SQLStatement:="SELECT * FROM ""vFactFindClient""", SQLStatement1:="",
SubType:=wdMergeSubTypeOthe
-------------------------------------------------------------------------------------------------------------------------------------------

Does anyone have any idea on how to proceed from here

Thanks.
Yenn

Reply to this Message

Peter Jamieson - 03 Apr 2004 20:10 GMT

No guarantees that the folowing will work, but try:
a. removing the "T" at the end of the connection string (Word truncates
OLEDB connectoins strings to 255 chars when it records them, and the
resulting string may be regarded as invalid)
b. if all the connection information is in the Connection string, edit the
.odc using notepad and remove all the text in it (i.e. the file can be
completely blank)
c. if you are using Integrated Security for SQL Server access, remove the
user ID and password info from the connection string and ensure you specify
the Integrated Security keyword instead (Can't remember exactly what it
looks like).
c. a few variations on the SQL string. e.g. you may find

SELECT * FROM vFactFindClient

works since you don't need quote marks round the table name in this case, or

SELECT v.* FROM vFactFindClient v

(i.e. use an alias name)

Signature

Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

> Hi,
> I have a problem connecting to data source in mail merge.
>
> I created a datasource through the Select Data Source screen to connect to SQL Server. And everything was fine. Then I recorded a process to open this

datasource on a macro and run it, and it returned a message:

> Record 1 contained too few data fields, Record 2 contained too few data fields, ... (3 times, because the table that I connected to has 3 columns),

and I clicked OK each time.

> Then it returned with Invalid Merge Field, and on it is has this:
> This merge field is used in the main document, but it does not exist in the data source [FieldName]
[quoted text clipped - 5 lines]
> [empty box] [OK button] [Cancel button]
> --------------------------------------------------------------------------

-----------------------------------------

> Now, the thing is that M__ field does not even exist in the table that I connect to.
>
[quoted text clipped - 4 lines]
> When I step into the code in VB Editor, the error happened when trying to set the datasource:
> --------------------------------------------------------------------------

---------------------------------------------------------

> ActiveDocument.MailMerge.OpenDataSource Name:= _
> "C:\Documents and Settings\yenny\My Documents\My Data Sources\svr_sql_itdev Cis_Dev vFactFindClient.odc" _
[quoted text clipped - 3 lines]
> Format:=wdOpenFormatAuto, Connection:= _
> "Provider=SQLOLEDB.1;Password=unisys;Persist Security Info=True;User ID=ordcisuser;Initial Catalog=Cis_Dev;Data

Source=svr_sql_itdev;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=SVR_DOT_NET;Use Encryption for Data=False;T" _

> , SQLStatement:="SELECT * FROM ""vFactFindClient""", SQLStatement1:="", _
> SubType:=wdMergeSubTypeOther
> --------------------------------------------------------------------------

------------------------------------------------------------------

> Does anyone have any idea on how to proceed from here?
>
> Thanks.
> Yenny

Reply to this Message