Re: OpenDataSource SQL Server xpress problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B3E218B6-EB05-472D-B0D0-1B6A56DE2511@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5D58148F-3976-4270-837F-FB756E406866@xxxxxxxxxxxxxxxx
> 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



.

Quantcast