| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003 SQL Express Datasource
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 |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
.....
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 |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns  dc="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 |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
I've TRied a couple of things and am now getting this attached error alot.
One thing I have noticed is that the connection string created by the Word macro is longer than 255 and trucanted I've also tried using a connection string found via google that you had help someone else with an odc less connection .... which is what I am trying to achieve If it's not posible I supose I could write code to edit the odc file on the fly but this does seem wrong ! Thanks Andrew "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 xmlns dc="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 |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
One thing I have noticed is that the connection string created by the Word
macro is longer than 255 and trucanted Yes. If you use the recorder to create your OpenDataSource this can lead to problems. Typically you can reduce these strings, e.g. in yours (as below) you probably do not need Use Procedure for Prepare, Auto Translate, Packet Size, Use Encryption for Data, Tag with... if you do not need to change the defaults, and you don't really need Workstation ID either. But in fact when Word opens the data source, it sends the connection string to the driver/provider, which modifies it anyway, and Word actually ends up storing the modified version which tends to contain all the default values. In some versions of Word, this gets truncated, but it doesn't usually appear to cause any damage unless it truncates something "important" like the "Data Source" name. 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 I've also tried using a connection string found via google that you had help someone else with an odc less connection .... which is what I am trying to achieve You can't avoid having either a DSN (ODBC connections), .odc or a .udl for a Word mailmerge data source to a server-style DBMS. But the .odc can be completely empty if you put all the connection and query info. in the OpenDataSource At the moment I can't spot what is going wrong with your connection info. but if necessary I will try to pick this up again later tomorrow (UK time) if I can. -- Peter Jamieson http://tips.pjmsn.me.uk "Andrew Kennard" wrote in message ... I've TRied a couple of things and am now getting this attached error alot. One thing I have noticed is that the connection string created by the Word macro is longer than 255 and trucanted I've also tried using a connection string found via google that you had help someone else with an odc less connection .... which is what I am trying to achieve If it's not posible I supose I could write code to edit the odc file on the fly but this does seem wrong ! Thanks Andrew "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 xmlns dc="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 |
|
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 !! I keep meaning to an article myself. PS wierd one about the connection string Word creates in the marcro record is truncated? It's because of some old limitation on string length. The problem seems to have been at least partially fixed in recent versions, but it actually creates havoc when people have OLE DB file type data sources such with long path names. IME the length problems do not affect server type connections so much because the amount of info needed to locate the database is typically quite small and everything else can usually go in the query. -- 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 xmlns dc="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 |
|
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
|
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
Peter
Looks like the tick is to Set ComVisible of oWordApplication to 1 Send ComActivate to oWordApplication Before starting the merge process. I've also added Move (FindWindow("","")) to hWnd Move (UpdateWindow(hWnd)) to iVoid Straight after the Activate to get it to paint the template document before starting Unfortuneatley I cant seem to force it to paint the toolbars of the output window until it has finshed the merge process which means you have a part drawn window with a hole in it where the menu and toolbars should be and the merge counter at the bottom zooming through the first part of the merge process which looks a bit 'naff' if there is a large number of documents to merge. Thanks again for your thoughts Andrew "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 xmlns dc="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 |
|
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
Activating the /Document/ as well as the App could also be worth trying.
-- Peter Jamieson http://tips.pjmsn.me.uk "Andrew Kennard" wrote in message ... Peter Looks like the tick is to Set ComVisible of oWordApplication to 1 Send ComActivate to oWordApplication Before starting the merge process. I've also added Move (FindWindow("","")) to hWnd Move (UpdateWindow(hWnd)) to iVoid Straight after the Activate to get it to paint the template document before starting Unfortuneatley I cant seem to force it to paint the toolbars of the output window until it has finshed the merge process which means you have a part drawn window with a hole in it where the menu and toolbars should be and the merge counter at the bottom zooming through the first part of the merge process which looks a bit 'naff' if there is a large number of documents to merge. Thanks again for your thoughts Andrew "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 xmlns dc="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 |
|
#16
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Word 2003 SQL Express Datasource
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 xmlns dc="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 |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can I send email messages via Outlook Express using Word 2003 | Microsoft Word Help | |||
| word 2003 and emailing using microsoft outlook express | Mailmerge | |||
| New Word 2003 datasource from wizard? | Mailmerge | |||
| Send Word 2003 document using Outlook Express | Microsoft Word Help | |||
| Send Word 2003 document using Outlook Express | Microsoft Word Help | |||
![Andrew Kennard[_2_] is offline](images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
