OpenDataSource SQL Server xpress problem Poppa Bear 11/11/2008 3:40:10 PM using : VS 2005 SE 2 ............ NOT VSTO Word 2003 Office Interop v 10 Word Interop v 10 Trying to Mailmerge Word 2003 Document with Database. 1. With ACCESS.mdb works fine (lines (A) and (B) below active (C) and (D) commented out 2. With SQL Server get message about failed to connect. (C) and (D) active (A) and (B) commented out. Using same Template file (CVSTemplate3.dot). Access and SQL server Xpress DBs have same tables and data Also get a dialog box about "Header Record Delimiters" telling me Data fields and Data Records must be separated by delimiters. Haven't the faintest idea what this refers to (sorry) . Can someone help please ? The connection string for the SQL Server version is the same as I use in the main program code to open the SQLServer Database - which works fine there, using Windows Authentification. Have trawled the MS website and others for info with no success. You are my last hope ! Help --- Please !! Code snippets : Private Sub btnOpenWord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenWord.Click Dim wrdApp As Word.Application = CType(CreateObject("Word.Application"), Word.Application) Dim wrdDoc As Word.Document = CType(wrdApp.Documents.Add("CVSTemplate3.dot", False, Word.WdDocumentType.wdTypeDocument, True), Word.Document) Dim sqlQuery As String = "SELECT * FROM [tblEmployees] WHERE EmployeeLastName LIKE '" & "Jones" & "';" '' For ACCESS version 'Dim sDBPath As String = "C:\WORK\DBTestEmp.mdb" '*********************** (A) 'Dim strConnect = System.Type.Missing '*********************** (B) '' FOR SQL SERVER Express version Dim sDBPath As String = "C:\WORK\dbSQLEmpTest.mdf" ' **********************(C) Dim strConnect As String = "Data Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" ' *********************(D) Try With wrdDoc.MailMerge .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=sDBPath, Connection:=strConnect, SQLStatement:=sqlQuery) .Destination = Word.WdMailMergeDestination.wdSendToNewDocument .Execute(Pause:=False) End With Catch ex As Exception MessageBox.Show(ex.Message) End Try wrdApp.Visible = True End Sub -- Poppa

Re: OpenDataSource SQL Server xpress problem "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 11/11/2008 7:12:10 PM Typically you do not access the .mdf file directly, but via the SQL Server database manager which in this case is probably running on your local system. To do that from Word you provide either a .odc ("Office Data Connection") file or a .udl "Universal Data Link" file, which in turn contains the connection information. ..odc files can be created manually by clicking the New Source button in the Open Data Source dialog, selecting the "Microsoft SQL Server" option, then follow the dialogs. However, you can also try using a completely empty .odc file (e.g. create an empty .txt file in Notepad, save it, and rename it to empty.odc. Then (assuming you put that in c:\work) try [Quoted Text] > '' FOR SQL SERVER Express version Dim sDBPath As String = "C:\WORK\empty.odc" ' > **********************(C) Dim strConnect As String = "Provider=SQLOLEDB.1;Data Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" for the older SQL OLE DB provider or maybe Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" for the newer "Native Client". For the Data Source parameter you will either need the machine name of the server - if that is c7e6i3 then you may be OK, or that name + "\SQLEXPRESS", e.g. Data Source=c7e6i3\SQLEXPRESS There may be more to it than that, but start there... -- Peter Jamieson http://tips.pjmsn.me.uk "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message news:5D58148F-3976-4270-837F-FB756E406866[ at ]microsoft.com... > using : > VS 2005 SE 2 ............ NOT VSTO > Word 2003 > Office Interop v 10 > Word Interop v 10 > > > Trying to Mailmerge Word 2003 Document with Database. > 1. With ACCESS.mdb works fine (lines (A) and (B) below active (C) and > (D) > commented out > > 2. With SQL Server get message about failed to connect. (C) and (D) active > (A) and (B) commented out. > > Using same Template file (CVSTemplate3.dot). Access and SQL server Xpress > DBs have same tables and data > > Also get a dialog box about "Header Record Delimiters" telling me Data > fields and Data Records must be separated by delimiters. Haven't the > faintest > idea what this refers to (sorry) . Can someone help please ? The > connection > string for the SQL Server version is the same as I use in the main program > code to open the SQLServer Database - which works fine there, using > Windows > Authentification. Have trawled the MS website and others for info with no > success. You are my last hope ! Help --- Please !! > > Code snippets : > > Private Sub btnOpenWord_Click(ByVal sender As System.Object, ByVal e As > System.EventArgs) Handles btnOpenWord.Click > Dim wrdApp As Word.Application = > CType(CreateObject("Word.Application"), Word.Application) > Dim wrdDoc As Word.Document = > CType(wrdApp.Documents.Add("CVSTemplate3.dot", False, > Word.WdDocumentType.wdTypeDocument, True), Word.Document) > > Dim sqlQuery As String = "SELECT * FROM [tblEmployees] WHERE > EmployeeLastName LIKE '" & "Jones" & "';" > > '' For ACCESS version > 'Dim sDBPath As String = "C:\WORK\DBTestEmp.mdb" > '*********************** (A) > 'Dim strConnect = System.Type.Missing > '*********************** (B) > > '' FOR SQL SERVER Express version > Dim sDBPath As String = "C:\WORK\dbSQLEmpTest.mdf" ' > **********************(C) > Dim strConnect As String = "Data > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" ' > *********************(D) > > Try > With wrdDoc.MailMerge > .MainDocumentType = > Word.WdMailMergeMainDocType.wdFormLetters > .OpenDataSource(Name:=sDBPath, Connection:=strConnect, > SQLStatement:=sqlQuery) > .Destination = > Word.WdMailMergeDestination.wdSendToNewDocument > .Execute(Pause:=False) > End With > Catch ex As Exception > MessageBox.Show(ex.Message) > End Try > wrdApp.Visible = True > End Sub > -- > Poppa

Re: OpenDataSource SQL Server xpress problem Poppa Bear 11/12/2008 9:00:11 AM Thanks a lot Peter. Have not had chance to try it yet - but will as soon as I can. People like you are wonderful but it baffles me why I can't find any explanation of these things on the Microsoft Help or elsewhere, and why are all the examples for Access files and none (that I could find) for SQL Server ?. Maybe I'm looking in the wrong place. Are there any good books on the subject ? Yes I should have said that the Server is a localhost but you guessed that correctly. Many thanks for your time and help. My partner is grateful as well !!! Will let you know if it works or doesn't ! . -- Poppa "Peter Jamieson" wrote: [Quoted Text] > Typically you do not access the .mdf file directly, but via the SQL Server > database manager which in this case is probably running on your local > system. To do that from Word you provide either a .odc ("Office Data > Connection") file or a .udl "Universal Data Link" file, which in turn > contains the connection information. > > ..odc files can be created manually by clicking the New Source button in the > Open Data Source dialog, selecting the "Microsoft SQL Server" option, then > follow the dialogs. However, you can also try using a completely empty .odc > file (e.g. create an empty .txt file in Notepad, save it, and rename it to > empty.odc. Then (assuming you put that in c:\work) try > > > '' FOR SQL SERVER Express version > Dim sDBPath As String = "C:\WORK\empty.odc" ' > > **********************(C) > Dim strConnect As String = "Provider=SQLOLEDB.1;Data > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" > > for the older SQL OLE DB provider > > or maybe > > Dim strConnect As String = "Provider=SQLNCLI;Data > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" > > for the newer "Native Client". > > For the Data Source parameter you will either need the machine name of the > server - if that is c7e6i3 then you may be OK, or that name + "\SQLEXPRESS", > e.g. > > Data Source=c7e6i3\SQLEXPRESS > > There may be more to it than that, but start there... > > -- > Peter Jamieson > http://tips.pjmsn.me.uk > > "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message > news:5D58148F-3976-4270-837F-FB756E406866[ at ]microsoft.com... > > using : > > VS 2005 SE 2 ............ NOT VSTO > > Word 2003 > > Office Interop v 10 > > Word Interop v 10 > > > > > > Trying to Mailmerge Word 2003 Document with Database. > > 1. With ACCESS.mdb works fine (lines (A) and (B) below active (C) and > > (D) > > commented out > > > > 2. With SQL Server get message about failed to connect. (C) and (D) active > > (A) and (B) commented out. > > > > Using same Template file (CVSTemplate3.dot). Access and SQL server Xpress > > DBs have same tables and data > > > > Also get a dialog box about "Header Record Delimiters" telling me Data > > fields and Data Records must be separated by delimiters. Haven't the > > faintest > > idea what this refers to (sorry) . Can someone help please ? The > > connection > > string for the SQL Server version is the same as I use in the main program > > code to open the SQLServer Database - which works fine there, using > > Windows > > Authentification. Have trawled the MS website and others for info with no > > success. You are my last hope ! Help --- Please !! > > > > Code snippets : > > > > Private Sub btnOpenWord_Click(ByVal sender As System.Object, ByVal e As > > System.EventArgs) Handles btnOpenWord.Click > > Dim wrdApp As Word.Application = > > CType(CreateObject("Word.Application"), Word.Application) > > Dim wrdDoc As Word.Document = > > CType(wrdApp.Documents.Add("CVSTemplate3.dot", False, > > Word.WdDocumentType.wdTypeDocument, True), Word.Document) > > > > Dim sqlQuery As String = "SELECT * FROM [tblEmployees] WHERE > > EmployeeLastName LIKE '" & "Jones" & "';" > > > > '' For ACCESS version > > 'Dim sDBPath As String = "C:\WORK\DBTestEmp.mdb" > > '*********************** (A) > > 'Dim strConnect = System.Type.Missing > > '*********************** (B) > > > > '' FOR SQL SERVER Express version > > Dim sDBPath As String = "C:\WORK\dbSQLEmpTest.mdf" ' > > **********************(C) > > Dim strConnect As String = "Data > > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" ' > > *********************(D) > > > > Try > > With wrdDoc.MailMerge > > .MainDocumentType = > > Word.WdMailMergeMainDocType.wdFormLetters > > .OpenDataSource(Name:=sDBPath, Connection:=strConnect, > > SQLStatement:=sqlQuery) > > .Destination = > > Word.WdMailMergeDestination.wdSendToNewDocument > > .Execute(Pause:=False) > > End With > > Catch ex As Exception > > MessageBox.Show(ex.Message) > > End Try > > wrdApp.Visible = True > > End Sub > > -- > > Poppa > >

Re: OpenDataSource SQL Server xpress problem "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 11/12/2008 9:28:07 AM [Quoted Text] > it baffles me why I can't find any > explanation of these things on the Microsoft Help or elsewhere, and why > are > all the examples for Access files and none (that I could find) for SQL > Server You are not alone, but I suspect it's because until recently most SQL Server users would be corporates who are probably expected to be able to look after themselves (i.e. have a technical support function). > Are there any good books on the > subject ? The only reasonably in-depth coverage I have seen was written by me but is in German (Microsoft Word-Programmierung - Das Handbuch. Entwicklung und Automatisierung mit VBA, XML und VSTO (several authors), and its 2nd edition successor. For the connection strings themselves, www.connectionstrings.com is probably the most useful site I know (NB, if your database appears in the relevant SQL Server management tool, it isn't a "linked database" that would require you to reference the .mdf in the connection string). However, Word is quite picky about connecting to data sources, and AFAIK the site does not document the .odc format. One of these days I'll probably do my own web articles but there's quite a lot to test and present! -- Peter Jamieson http://tips.pjmsn.me.uk "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message news:B3E218B6-EB05-472D-B0D0-1B6A56DE2511[ at ]microsoft.com... > Thanks a lot Peter. Have not had chance to try it yet - but will as soon > as I > can. People like you are wonderful but it baffles me why I can't find any > explanation of these things on the Microsoft Help or elsewhere, and why > are > all the examples for Access files and none (that I could find) for SQL > Server > ?. Maybe I'm looking in the wrong place. Are there any good books on the > subject ? > > Yes I should have said that the Server is a localhost but you guessed that > correctly. Many thanks for your time and help. My partner is grateful as > well !!! Will let you know if it works or doesn't ! . > -- > Poppa > > > "Peter Jamieson" wrote: > >> Typically you do not access the .mdf file directly, but via the SQL >> Server >> database manager which in this case is probably running on your local >> system. To do that from Word you provide either a .odc ("Office Data >> Connection") file or a .udl "Universal Data Link" file, which in turn >> contains the connection information. >> >> ..odc files can be created manually by clicking the New Source button in >> the >> Open Data Source dialog, selecting the "Microsoft SQL Server" option, >> then >> follow the dialogs. However, you can also try using a completely empty >> .odc >> file (e.g. create an empty .txt file in Notepad, save it, and rename it >> to >> empty.odc. Then (assuming you put that in c:\work) try >> >> > '' FOR SQL SERVER Express version >> Dim sDBPath As String = "C:\WORK\empty.odc" ' >> > **********************(C) >> Dim strConnect As String = "Provider=SQLOLEDB.1;Data >> Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" >> >> for the older SQL OLE DB provider >> >> or maybe >> >> Dim strConnect As String = "Provider=SQLNCLI;Data >> Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" >> >> for the newer "Native Client". >> >> For the Data Source parameter you will either need the machine name of >> the >> server - if that is c7e6i3 then you may be OK, or that name + >> "\SQLEXPRESS", >> e.g. >> >> Data Source=c7e6i3\SQLEXPRESS >> >> There may be more to it than that, but start there... >> >> -- >> Peter Jamieson >> http://tips.pjmsn.me.uk >> >> "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message >> news:5D58148F-3976-4270-837F-FB756E406866[ at ]microsoft.com... >> > using : >> > VS 2005 SE 2 ............ NOT VSTO >> > Word 2003 >> > Office Interop v 10 >> > Word Interop v 10 >> > >> > >> > Trying to Mailmerge Word 2003 Document with Database. >> > 1. With ACCESS.mdb works fine (lines (A) and (B) below active (C) and >> > (D) >> > commented out >> > >> > 2. With SQL Server get message about failed to connect. (C) and (D) >> > active >> > (A) and (B) commented out. >> > >> > Using same Template file (CVSTemplate3.dot). Access and SQL server >> > Xpress >> > DBs have same tables and data >> > >> > Also get a dialog box about "Header Record Delimiters" telling me Data >> > fields and Data Records must be separated by delimiters. Haven't the >> > faintest >> > idea what this refers to (sorry) . Can someone help please ? The >> > connection >> > string for the SQL Server version is the same as I use in the main >> > program >> > code to open the SQLServer Database - which works fine there, using >> > Windows >> > Authentification. Have trawled the MS website and others for info with >> > no >> > success. You are my last hope ! Help --- Please !! >> > >> > Code snippets : >> > >> > Private Sub btnOpenWord_Click(ByVal sender As System.Object, ByVal e As >> > System.EventArgs) Handles btnOpenWord.Click >> > Dim wrdApp As Word.Application = >> > CType(CreateObject("Word.Application"), Word.Application) >> > Dim wrdDoc As Word.Document = >> > CType(wrdApp.Documents.Add("CVSTemplate3.dot", False, >> > Word.WdDocumentType.wdTypeDocument, True), Word.Document) >> > >> > Dim sqlQuery As String = "SELECT * FROM [tblEmployees] WHERE >> > EmployeeLastName LIKE '" & "Jones" & "';" >> > >> > '' For ACCESS version >> > 'Dim sDBPath As String = "C:\WORK\DBTestEmp.mdb" >> > '*********************** (A) >> > 'Dim strConnect = System.Type.Missing >> > '*********************** (B) >> > >> > '' FOR SQL SERVER Express version >> > Dim sDBPath As String = "C:\WORK\dbSQLEmpTest.mdf" ' >> > **********************(C) >> > Dim strConnect As String = "Data >> > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" ' >> > *********************(D) >> > >> > Try >> > With wrdDoc.MailMerge >> > .MainDocumentType = >> > Word.WdMailMergeMainDocType.wdFormLetters >> > .OpenDataSource(Name:=sDBPath, Connection:=strConnect, >> > SQLStatement:=sqlQuery) >> > .Destination = >> > Word.WdMailMergeDestination.wdSendToNewDocument >> > .Execute(Pause:=False) >> > End With >> > Catch ex As Exception >> > MessageBox.Show(ex.Message) >> > End Try >> > wrdApp.Visible = True >> > End Sub >> > -- >> > Poppa >> >>

Re: OpenDataSource SQL Server xpress problem Poppa Bear 11/13/2008 10:03:01 AM Thanks for all your Help. I tried your suggestions with some success. I tried setting up the .odc file through the Word Mail Merge wizard as you suggested This seems to work OK as long as the strConnection parameter is set to 'missing' in the OpenDataSource method. Trying your other suggestion with an empty.odc file (NOT empty.odc.txt !) I tried the following 3 scenarios with SDBPath set to "C:\WORK\empty.odc" in each . 1) Dim strConnect As String = "Provider=SQLOLEDB.1;Data Source=c7e6i3; Database=dbSQLEmpTest;Integrated Security=True;" 2) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3; Database=dbSQLEmpTest;Integrated Security=True;" 3) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3\SQLEXPRESS; Database=dbSQLEmpTest;Integrated Security=True;" In all 3 cases I get a 'Data Link Properties' dialog box appear. If I work through the dialog to connect and then click the 'Test Connection' button in each case it fails to connect. However ........ In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' dialog and set this to SQL Native Client and then work through the boxes it then succeeds in the Test Connection and the subsequent Mail Merge !! Weird ! (Providers is already set to SQLNative in the other two scenarios) As I can find no way to get round the dialog boxes and get a successful connection I had to give up. I do have a solution (though not ideal) in your first method for which I am very grateful. Ideally I want all this to run from a Web Server but with the Word documents being produced on the Clients machine. This however is I realise another ball game way beyond the scope of this forum I think. I have a book 'Visual Studio Tools for Office' by Carter/Lippert which shows how to do if from VSTO. However I don't want to go down the VSTO route if I can help it. If you can point me in the direction of any good (non German !) books or articles, then I would be grateful. However I suspect I already know the answer to that. Thanks again for all your help. Poppa -- Poppa "Peter Jamieson" wrote: [Quoted Text] > > it baffles me why I can't find any > > explanation of these things on the Microsoft Help or elsewhere, and why > > are > > all the examples for Access files and none (that I could find) for SQL > > Server > > You are not alone, but I suspect it's because until recently most SQL Server > users would be corporates who are probably expected to be able to look after > themselves (i.e. have a technical support function). > > > Are there any good books on the > > subject ? > > The only reasonably in-depth coverage I have seen was written by me but is > in German (Microsoft Word-Programmierung - Das Handbuch. Entwicklung und > Automatisierung mit VBA, XML und VSTO (several authors), and its 2nd edition > successor. > > > For the connection strings themselves, www.connectionstrings.com is probably > the most useful site I know (NB, if your database appears in the relevant > SQL Server management tool, it isn't a "linked database" that would require > you to reference the .mdf in the connection string). However, Word is quite > picky about connecting to data sources, and AFAIK the site does not document > the .odc format. > > One of these days I'll probably do my own web articles but there's quite a > lot to test and present! > > -- > Peter Jamieson > http://tips.pjmsn.me.uk > > "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message > news:B3E218B6-EB05-472D-B0D0-1B6A56DE2511[ at ]microsoft.com... > > Thanks a lot Peter. Have not had chance to try it yet - but will as soon > > as I > > can. People like you are wonderful but it baffles me why I can't find any > > explanation of these things on the Microsoft Help or elsewhere, and why > > are > > all the examples for Access files and none (that I could find) for SQL > > Server > > ?. Maybe I'm looking in the wrong place. Are there any good books on the > > subject ? > > > > Yes I should have said that the Server is a localhost but you guessed that > > correctly. Many thanks for your time and help. My partner is grateful as > > well !!! Will let you know if it works or doesn't ! . > > -- > > Poppa > > > > > > "Peter Jamieson" wrote: > > > >> Typically you do not access the .mdf file directly, but via the SQL > >> Server > >> database manager which in this case is probably running on your local > >> system. To do that from Word you provide either a .odc ("Office Data > >> Connection") file or a .udl "Universal Data Link" file, which in turn > >> contains the connection information. > >> > >> ..odc files can be created manually by clicking the New Source button in > >> the > >> Open Data Source dialog, selecting the "Microsoft SQL Server" option, > >> then > >> follow the dialogs. However, you can also try using a completely empty > >> .odc > >> file (e.g. create an empty .txt file in Notepad, save it, and rename it > >> to > >> empty.odc. Then (assuming you put that in c:\work) try > >> > >> > '' FOR SQL SERVER Express version > >> Dim sDBPath As String = "C:\WORK\empty.odc" ' > >> > **********************(C) > >> Dim strConnect As String = "Provider=SQLOLEDB.1;Data > >> Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" > >> > >> for the older SQL OLE DB provider > >> > >> or maybe > >> > >> Dim strConnect As String = "Provider=SQLNCLI;Data > >> Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" > >> > >> for the newer "Native Client". > >> > >> For the Data Source parameter you will either need the machine name of > >> the > >> server - if that is c7e6i3 then you may be OK, or that name + > >> "\SQLEXPRESS", > >> e.g. > >> > >> Data Source=c7e6i3\SQLEXPRESS > >> > >> There may be more to it than that, but start there... > >> > >> -- > >> Peter Jamieson > >> http://tips.pjmsn.me.uk > >> > >> "Poppa Bear" <PoppaBear[ at ]discussions.microsoft.com> wrote in message > >> news:5D58148F-3976-4270-837F-FB756E406866[ at ]microsoft.com... > >> > using : > >> > VS 2005 SE 2 ............ NOT VSTO > >> > Word 2003 > >> > Office Interop v 10 > >> > Word Interop v 10 > >> > > >> > > >> > Trying to Mailmerge Word 2003 Document with Database. > >> > 1. With ACCESS.mdb works fine (lines (A) and (B) below active (C) and > >> > (D) > >> > commented out > >> > > >> > 2. With SQL Server get message about failed to connect. (C) and (D) > >> > active > >> > (A) and (B) commented out. > >> > > >> > Using same Template file (CVSTemplate3.dot). Access and SQL server > >> > Xpress > >> > DBs have same tables and data > >> > > >> > Also get a dialog box about "Header Record Delimiters" telling me Data > >> > fields and Data Records must be separated by delimiters. Haven't the > >> > faintest > >> > idea what this refers to (sorry) . Can someone help please ? The > >> > connection > >> > string for the SQL Server version is the same as I use in the main > >> > program > >> > code to open the SQLServer Database - which works fine there, using > >> > Windows > >> > Authentification. Have trawled the MS website and others for info with > >> > no > >> > success. You are my last hope ! Help --- Please !! > >> > > >> > Code snippets : > >> > > >> > Private Sub btnOpenWord_Click(ByVal sender As System.Object, ByVal e As > >> > System.EventArgs) Handles btnOpenWord.Click > >> > Dim wrdApp As Word.Application = > >> > CType(CreateObject("Word.Application"), Word.Application) > >> > Dim wrdDoc As Word.Document = > >> > CType(wrdApp.Documents.Add("CVSTemplate3.dot", False, > >> > Word.WdDocumentType.wdTypeDocument, True), Word.Document) > >> > > >> > Dim sqlQuery As String = "SELECT * FROM [tblEmployees] WHERE > >> > EmployeeLastName LIKE '" & "Jones" & "';" > >> > > >> > '' For ACCESS version > >> > 'Dim sDBPath As String = "C:\WORK\DBTestEmp.mdb" > >> > '*********************** (A) > >> > 'Dim strConnect = System.Type.Missing > >> > '*********************** (B) > >> > > >> > '' FOR SQL SERVER Express version > >> > Dim sDBPath As String = "C:\WORK\dbSQLEmpTest.mdf" ' > >> > **********************(C) > >> > Dim strConnect As String = "Data > >> > Source=c7e6i3;Database=dbSQLEmpTest;Integrated Security=True;" ' > >> > *********************(D) > >> > > >> > Try > >> > With wrdDoc.MailMerge > >> > .MainDocumentType = > >> > Word.WdMailMergeMainDocType.wdFormLetters > >> > .OpenDataSource(Name:=sDBPath, Connection:=strConnect, > >> > SQLStatement:=sqlQuery) > >> > .Destination = > >> > Word.WdMailMergeDestination.wdSendToNewDocument > >> > .Execute(Pause:=False) > >> > End With > >> > Catch ex As Exception > >> > MessageBox.Show(ex.Message) > >> > End Try > >> > wrdApp.Visible = True > >> > End Sub > >> > -- > >> > Poppa > >> > >> > >

Re: OpenDataSource SQL Server xpress problem Peter Jamieson <pjj[ at ]KillMAPSpjjnet.demon.co.uk> 11/13/2008 12:33:19 PM << In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' dialog and set this to SQL Native Client and then work through the boxes it then succeeds in the Test Connection and the subsequent Mail Merge !! Weird ! >> Assuming this created a new .odc, maybe you should use that (.odc's are human-readable HTML files so you should, for example, be able to extract the connection string if you want to use that in an OpenDataSource instead. If you post that connection string here I may be able to spot something useful.) FWIW as long as the supporting "behaviour" file DATACONN.HTC is in the same folder as the .odc, you /may/ be able to open your data source directly in Internet Explorer, which can be helpful when you are testing. Some things to know about Word and .odc files, though: a. When you have set up a data source using a .odc, the necessary connection info. is actually embedded in the Word document. So when you close and re-open the document, it will point at the original data source, even if (for example) you have modified the .odc so that it points to a different server. That might be a significant fact in a web-based scenario. b. Word does not take advantage of all the facilities in a .odc (e.g. in a .odc you can define a SQL query, but I do not think Word honours it) > Ideally I want all this to run from a Web Server but with the Word documents > being produced on the Clients machine. To produce the Word documents on the client machine using Word mailmerge, the client currently has to have Word running locally. Assuming you really want to host your SQL Server on your Web server, or perhaps another web-based box, the real difficulty is that Word still has to connect to that. FWIW I never managed to get Word to connect to SQL Server beyond about v. 2005 I think without using Windows authentication, so that may pose a problem in this case. > if from VSTO. However I don't want to go down the VSTO route if I can > help it. VSTO is one possibility. Using .NET on the server side to construct ..docx format documents without the need to automate Word (which is in effect what VSTO does) is another. I would try to find a group where Cindy Meister is a regular contributor as she may have some useful pointers on this one. Peter Jamieson http://tips.pjmsn.me.uk Poppa Bear wrote: [Quoted Text] > Thanks for all your Help. I tried your suggestions with some success. > > I tried setting up the .odc file through the Word Mail Merge wizard as > you suggested This seems to work OK as long as the strConnection parameter > is set to 'missing' in the OpenDataSource method. > > Trying your other suggestion with an empty.odc file (NOT empty.odc.txt !) > > I tried the following 3 scenarios with SDBPath set to "C:\WORK\empty.odc" > in each . > > 1) Dim strConnect As String = "Provider=SQLOLEDB.1;Data Source=c7e6i3; > Database=dbSQLEmpTest;Integrated Security=True;" > > 2) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3; > Database=dbSQLEmpTest;Integrated Security=True;" > > 3) Dim strConnect As String = "Provider=SQLNCLI;Data > Source=c7e6i3\SQLEXPRESS; > Database=dbSQLEmpTest;Integrated Security=True;" > > In all 3 cases I get a 'Data Link Properties' dialog box appear. > If I work through the dialog to connect and then click the 'Test Connection' > button in each case it fails to connect. > > However ........ > In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' > dialog and set this to SQL Native Client and then work through the boxes it > then > succeeds in the Test Connection and the subsequent Mail Merge !! Weird ! > (Providers is already set to SQLNative in the other two scenarios) > > As I can find no way to get round the dialog boxes and get a successful > connection I had to give up. I do have a solution (though not ideal) > in your first method for which I am very grateful. > > Ideally I want all this to run from a Web Server but with the Word documents > being produced on the Clients machine. This however is I realise another > ball game way beyond the scope of this forum I think. I have a book > 'Visual Studio Tools for Office' by Carter/Lippert which shows how to do > if from VSTO. However I don't want to go down the VSTO route if I can > help it. If you can point me in the direction of any good (non German !) > books or articles, then I would be grateful. However I suspect I already > know the answer to that. > > Thanks again for all your help. > > Poppa >

Re: OpenDataSource SQL Server xpress problem Poppa Bear 11/14/2008 8:02:01 AM Thanks again Peter lots of interesting detail there and interesting things to investigate and try when time allows. Can't really thank you enough. Will sign off this thread for now while I investigate further. I guess I'll be back if (when) I get stuck again ! Many, many Thanks -- Poppa "Peter Jamieson" wrote: [Quoted Text] > << > In scenario 1) If I go to the Providers Tab in the 'Data Link > Properties' dialog and set this to SQL Native Client and then work > through the boxes it then succeeds in the Test Connection and the > subsequent Mail Merge !! Weird ! > >> > > Assuming this created a new .odc, maybe you should use that (.odc's are > human-readable HTML files so you should, for example, be able to extract > the connection string if you want to use that in an OpenDataSource > instead. If you post that connection string here I may be able to spot > something useful.) > > FWIW as long as the supporting "behaviour" file DATACONN.HTC is in the > same folder as the .odc, you /may/ be able to open your data source > directly in Internet Explorer, which can be helpful when you are > testing. Some things to know about Word and .odc files, though: > a. When you have set up a data source using a .odc, the necessary > connection info. is actually embedded in the Word document. So when you > close and re-open the document, it will point at the original data > source, even if (for example) you have modified the .odc so that it > points to a different server. That might be a significant fact in a > web-based scenario. > b. Word does not take advantage of all the facilities in a .odc (e.g. > in a .odc you can define a SQL query, but I do not think Word honours it) > > > Ideally I want all this to run from a Web Server but with the Word > documents > > being produced on the Clients machine. > > To produce the Word documents on the client machine using Word > mailmerge, the client currently has to have Word running locally. > Assuming you really want to host your SQL Server on your Web server, or > perhaps another web-based box, the real difficulty is that Word still > has to connect to that. FWIW I never managed to get Word to connect to > SQL Server beyond about v. 2005 I think without using Windows > authentication, so that may pose a problem in this case. > > > if from VSTO. However I don't want to go down the VSTO route if I can > > help it. > > VSTO is one possibility. Using .NET on the server side to construct > ..docx format documents without the need to automate Word (which is in > effect what VSTO does) is another. I would try to find a group where > Cindy Meister is a regular contributor as she may have some useful > pointers on this one. > > > Peter Jamieson > > http://tips.pjmsn.me.uk > > Poppa Bear wrote: > > Thanks for all your Help. I tried your suggestions with some success. > > > > I tried setting up the .odc file through the Word Mail Merge wizard as > > you suggested This seems to work OK as long as the strConnection parameter > > is set to 'missing' in the OpenDataSource method. > > > > Trying your other suggestion with an empty.odc file (NOT empty.odc.txt !) > > > > I tried the following 3 scenarios with SDBPath set to "C:\WORK\empty.odc" > > in each . > > > > 1) Dim strConnect As String = "Provider=SQLOLEDB.1;Data Source=c7e6i3; > > Database=dbSQLEmpTest;Integrated Security=True;" > > > > 2) Dim strConnect As String = "Provider=SQLNCLI;Data Source=c7e6i3; > > Database=dbSQLEmpTest;Integrated Security=True;" > > > > 3) Dim strConnect As String = "Provider=SQLNCLI;Data > > Source=c7e6i3\SQLEXPRESS; > > Database=dbSQLEmpTest;Integrated Security=True;" > > > > In all 3 cases I get a 'Data Link Properties' dialog box appear. > > If I work through the dialog to connect and then click the 'Test Connection' > > button in each case it fails to connect. > > > > However ........ > > In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' > > dialog and set this to SQL Native Client and then work through the boxes it > > then > > succeeds in the Test Connection and the subsequent Mail Merge !! Weird ! > > (Providers is already set to SQLNative in the other two scenarios) > > > > As I can find no way to get round the dialog boxes and get a successful > > connection I had to give up. I do have a solution (though not ideal) > > in your first method for which I am very grateful. > > > > Ideally I want all this to run from a Web Server but with the Word documents > > being produced on the Clients machine. This however is I realise another > > ball game way beyond the scope of this forum I think. I have a book > > 'Visual Studio Tools for Office' by Carter/Lippert which shows how to do > > if from VSTO. However I don't want to go down the VSTO route if I can > > help it. If you can point me in the direction of any good (non German !) > > books or articles, then I would be grateful. However I suspect I already > > know the answer to that. > > > > Thanks again for all your help. > > > > Poppa > > >