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