[Peter Jamieson]

It certainly makes you wonder. There could I suppose be a timing issue, or
something to do with the fact that Word may end up openig the .csv using a
converter rather than an OLE DB provider.


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

"Andrew Kennard" wrote in message
...
Morning Peter !

I'm just looking back at this now and the stranbge thing is the following
code when used with a CSV file data source works fine in terms of the
toolbars displaying but when I change it to the sql source they are not
there ?

I shal have a play movid around the active etc and let you know

Cheers

Andrew


Send ComOpenDataSource of oMailMerge
C_WordOneOffDataFileName OLEwdOpenFormatText False True False Nothing
Nothing Nothing Nothing Nothing Nothing Nothing Nothing Nothing Nothing
Nothing

// Send ComOpenDataSource of oMailMerge ;
//
((psHome(phoWorkspace(ghoApplication)))+"Programs\ BlankSQLServerConnection.odc")
;
// OLEwdOpenFormatAuto ;
// False True True False Nothing Nothing False Nothing
Nothing ;
// "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=System3000;Data Source=AGKBIGDELL;" ;
// "SELECT A.* FROM [System3000].[dbo].[AGKTemp9] A"
Nothing Nothing OLEwdMergeSubTypeOther

Set ComDestination of oMailMerge to eMergeType // set
output destinatination to NewDoc or Printer
Send ComExecute of oMailMerge True // actually perform the
merge
Get ComActiveDocument of oWordApplication to vDocument //
BEFORE doing ANYTHING else get the Doument object for the NEW document (if
not printed)
Send ComClose of oDocument False Nothing Nothing // close
the LAYOUT document
If (eMergeType=OLEwdSendToNewDocument) Begin
Set ComVisible of oWordApplication to 1 // make sure
word is visible (can run in background)
Send ComActivate to oWordApplication // make sure word
is active
Set pvComObject of oDocument to vDocument // set VDF
document obj to the NEW merged document
Get ComName of oDocument to sDocName // get the "name"
of this document
Send ActivateWordDoc to oWordApplication sDocName //
make it the active window in word incase it has other documents open
End



"Peter Jamieson" wrote in message
...
Is this normal when automating mailmerge via COM and you have to "switch
them on" ?

You have to Activate the App and/or ddocument.

"Ed" mentioned a few months ago that they had been through this loop and
he thought the following sequence had workd for them:

Set the app visible.
Activate the app.
Perform the merge (and maybe do other stuff).
Activate the app.



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

"Andrew Kennard" wrote in message
...
Ah! The single letter was just a bit too consice for me !

The alias seems to hjave done the trick and the odc file I am using is
empty. I'll do some more tests but we seem to be cooking with gas at the
moment !

One other question .... I've not looked into it yet but my "Form
Letters1" window displays the result of the merge but it does not have
the usual toolbars and menus at the top

Is this normal when automating mailmerge via COM and you have to "switch
them on" ?

Thanks VERY much for all of your help I would have never cracked this on
my own !

I'm surprised there is not a Microsoft KB article on how to do it (or
maybe I havn't found it) but it is crying out for one as it seem like
the logical thing someone would want to do with their new "free"
database !!

PS wierd one about the connection string Word creates in the marcro
record is truncated?


"Peter Jamieson" wrote in message
...
The "A" is a "table alias". As far as I can tell, standard SQL should
not require you to use an alias in this case but Word seems to insist
on it.

FWIW, table aliases have various uses, e.g. shortening/clarifying your
code, but more importantly you have to use table aliases when you use
more complex queries that reference the same table in more than one
"role"

e.g. if you have an employee table with

EmployeeID, ManagerID, EmployeeName

and each manager is also listed in the table, to get a list of

Employee ID, Employee name, Manager ID, Manager name

you might use something like

SELECT E.EmployeeID, E.EmployeeName, E.ManagerID, M.EmployeeName
FROM Employee E INNER JOIN Employee M on M.EmployeeID = E.ManagerID

There are also "columne aliases" that let you rename a column or an
expression, e.g. something like

SELECT E.EmployeeID, E.EmployeeName AS [Employee's name], E.ManagerID,
M.EmployeeName AS [Manager's name]
FROM Employee E INNER JOIN Employee M on M.EmployeeID = E.ManagerID

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

"Andrew Kennard" wrote in message
...
Thanks Peter I'll try that

I've already just tried leaving out the Connection and SQL and I've
managed to get it to work but would prefere to connection string
option as it is more flexible

What are the significance of the A's in your example ? I'm not an SQL
guru !

Thanks

Andrew

"Peter Jamieson" wrote in message
...
Off the top of my head I suggest you try modifying the OpenDataSource
call so that
a. the query is

"SELECT A.* FROM [System3000].[dbo].[AGKTemp11] A"

b. If that isn't enough, try leaving the connection parameter out of
the OpenDataSource call, if you can (setting it to Nothing or "" may
not work either, though)

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

"Andrew Kennard" wrote in message
...
OK having go it working as a 'user' I'm now trying VIA com and not
having much luck !

The best i've got so far is no error opening the data source but it
only thinks it has two fields M_ and M_1 which is not correct

My command in the Visual Dataflex language I am using is

Send ComOpenDataSource of oMailMerge
((psHome(phoWorkspace(ghoApplication)))+"Programs\ SQLServerConnection.odc")
OLEwdOpenFormatAuto False True True False Nothing Nothing False
Nothing Nothing "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial
Catalog=System3000;Data Source=AGKBIGDELL;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=AGKBIGDELL;Use Encryption for Data=False;Tag with col" "SELECT *
FROM AGKTemp11" Nothing Nothing OLEwdMergeSubTypeOther

I made this up from a recorded macro from Word

The meat of the odc file is

meta http-equiv=Content-Type content="text/x-ms-odc;
charset=utf-8"
meta name=ProgId content=ODC.Table
meta name=SourceType content=OLEDB
meta name=Catalog content=System3000
meta name=Schema content=dbo
meta name=Table content=AGKTemp11
xml id=docprops/xmlxml id=msodcodc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40"
odc:Connection odc:Type="OLEDB"
odc:ConnectionStringProvider=SQLOLEDB.1;Integrat ed
Security=SSPI;Persist Security Info=True;Data Source=AGKBIGDELL;Use
Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=AGKBIGDELL;Use Encryption for
Data=False;Tag with column collation when possible=False;Initial
Catalog=System3000/odc:ConnectionString
odc:CommandTypeTable/odc:CommandType

odc:CommandText"System3000"."dbo& quot;."AGKTemp11"/odc:CommandText
/odc:Connection
/odc:OfficeDataConnection
/xml

Any clues would be most appreciated

Thanks

Andrew

"Andrew Kennard" wrote in message
...
I've selected this table INTO a new table and Word seems to be able
to connect to that one OK ?

All of the columns are there so I assume it must be something to do
with the indexs or other 'options' on this table ??

Thanks

Andrew

"Andrew Kennard" wrote in message
...
....

CREATE TABLE [words].[Person](

[PK_Person] [int] NOT NULL,

[Title] [varchar](10) NOT NULL CONSTRAINT [DF_Person_Title]
DEFAULT (''),

[Forename] [varchar](35) NOT NULL CONSTRAINT [DF_Person_Forename]
DEFAULT (''),

[Surname] [varchar](35) NOT NULL CONSTRAINT [DF_Person_Surname]
DEFAULT (''),

[Salutation] [varchar](50) NOT NULL CONSTRAINT
[DF_Person_Salutation] DEFAULT (''),

[Badge_Name] [varchar](35) NOT NULL CONSTRAINT
[DF_Person_Badge_Name] DEFAULT (''),

[Sort_Code] [varchar](40) NOT NULL CONSTRAINT
[DF_Person_Sort_Code] DEFAULT (''),

[External_ID] [varchar](20) NOT NULL CONSTRAINT
[DF_Person_External_ID] DEFAULT (''),

[Status] [tinyint] NOT NULL CONSTRAINT
[DF__Person__Status__278EDA44] DEFAULT ((0)),

[Perm_Bidder_No] [varchar](10) NOT NULL CONSTRAINT
[DF_Person_Perm_Bidder_No] DEFAULT (''),

[Update_Timestamp] [timestamp] NOT NULL,

[Update_User] [tinyint] NOT NULL CONSTRAINT
[DF_Person_Update_User] DEFAULT ((0)),

[Invalid] [bit] NOT NULL CONSTRAINT [DF_Person_Invalid] DEFAULT
((0)),

[Cheque_Limit] [decimal](10, 2) NOT NULL CONSTRAINT
[DF_Person_Cheque_Limit] DEFAULT ((0.00)),

[No_Card] [bit] NOT NULL CONSTRAINT [DF_Person_No_Card] DEFAULT
((0)),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

[PK_Person] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY],

CONSTRAINT [UQ_Person_PK_Person] UNIQUE NONCLUSTERED

(

[PK_Person] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]

) ON [PRIMARY]





"Andrew Kennard" wrote in message
...
Hi All

I'm trying to connect to an SQL2005Express Table as the source
for a Word merge

Ulimately I want to automate this via COM but for now am just
trying it as a user

I can get the connection setup fine and choose the table I want
however I get the following error when I try and use it

"Record 1 contained too few dataflields"

Looking on the MS KB this seems to be a known problem and you
need to update to the latest office service pack (SP3) which I
have done but the problem is still there

The Word about dialog shows as version "11.8169.8172.SP3"

This is just a test but the structure of the table I am conecting
to is