 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / December 2007Word 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 .... 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] > Hi All > [quoted text clipped - 16 lines] > > This is just a test but the structure of the table I am conecting to is 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 > .... > [quoted text clipped - 80 lines] >> >> This is just a test but the structure of the table I am conecting to is 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></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns="http://www.w3.org/TR/REC-html40"> <odc:Connection odc:Type="OLEDB"> <odc:ConnectionString>Provider=SQLOLEDB.1;Integrated 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:CommandType>Table</odc:CommandType> <odc:CommandText>"System3000"."dbo"."AGKTemp11"</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection> </xml> Any clues would be most appreciated Thanks Andrew > I've selected this table INTO a new table and Word seems to be able to > connect to that one OK ? [quoted text clipped - 90 lines] >>> >>> This is just a test but the structure of the table I am conecting to is 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)  SignaturePeter Jamieson http://tips.pjmsn.me.uk > OK having go it working as a 'user' I'm now trying VIA com and not having > much luck ! [quoted text clipped - 140 lines] >>>> >>>> This is just a test but the structure of the table I am conecting to is 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 > Off the top of my head I suggest you try modifying the OpenDataSource call > so that [quoted text clipped - 151 lines] >>>>> This is just a test but the structure of the table I am conecting to >>>>> is 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 > Thanks Peter I'll try that > [quoted text clipped - 172 lines] >>>>>> This is just a test but the structure of the table I am conecting to >>>>>> is > 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. SignaturePeter Jamieson http://tips.pjmsn.me.uk > I've TRied a couple of things and am now getting this attached error alot. > [quoted text clipped - 197 lines] >>>>>>> This is just a test but the structure of the table I am conecting to >>>>>>> is 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 SignaturePeter Jamieson http://tips.pjmsn.me.uk > Thanks Peter I'll try that > [quoted text clipped - 164 lines] >>>>>> This is just a test but the structure of the table I am conecting to >>>>>> is 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? > 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. [quoted text clipped - 195 lines] >>>>>>> This is just a test but the structure of the table I am conecting to >>>>>>> is > 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. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Ah! The single letter was just a bit too consice for me ! ;) > [quoted text clipped - 219 lines] >>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>> to is 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 >> Is this normal when automating mailmerge via COM and you have to "switch >> them on" ? [quoted text clipped - 235 lines] >>>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>>> to is 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. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Morning Peter ! > [quoted text clipped - 289 lines] >>>>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>>>> to is 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 >> Is this normal when automating mailmerge via COM and you have to "switch >> them on" ? [quoted text clipped - 235 lines] >>>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>>> to is Activating the /Document/ as well as the App could also be worth trying. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Peter > [quoted text clipped - 265 lines] >>>>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>>>> to is > 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. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Ah! The single letter was just a bit too consice for me ! ;) > [quoted text clipped - 219 lines] >>>>>>>> This is just a test but the structure of the table I am conecting >>>>>>>> to is |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.