Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Connecting to SQL Server for mailmerge
Hi, I am trying to write vba program that will work on Word 2000 & 2002
clients and connect to SQL 2000 to produce a batch of invoices using mailmerge automation. I am having problems with the connection.It gives a runtime error 5174, "This file could not be found", although I have tested the code and both the connection string and SQL works fine (see below). Any help / alternatives appreciated. thanks hals_left Sub MailMergeTest() Dim strConn, strSQL, objRS, strResult Dim objConn As New ADODB.Connection ' SQL Server 2K on local machine integrated security strConn = "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=pubs;" & _ "Integrated Security=SSPI" strSQL = "Select stor_id, stor_name from stores" ' ~~~~~~~~ Test the connection manually objConn.Open strConn Set objRS = objConn.Execute(strSQL) While Not objRS.EOF strResult = strResult & objRS(0) & "-" & objRS(1) & vbCrLf objRS.MoveNext Wend MsgBox strResult objConn.Close Set objConn = Nothing ' ~~~~~~~~~ Now mailmerge - doesnt work ActiveDocument.MailMerge.OpenDataSource strConn, , , , , , , , , , , , , strSQL ActiveDocument.MailMerge.Execute End Sub |
#2
|
|||
|
|||
After some searching, this does seem to be a known problem.
I have managed to get it working now with a file dsn, below, but this is not the ideal solution as the database will be remote hosted and the clients distributed. If anyone has any solutions or know the versions that do support this please let me know. thanks. ' ~~~~~~~~~ using a file dsn ActiveDocument.MailMerge.OpenDataSource mydsn, , , , , , , , , , , "DSN=mydsn", strSQL ActiveDocument.MailMerge.Execute |
#3
|
|||
|
|||
The code in your first message uses an OLEDB connection string which will
not work in Word 2000 because it does not support OLEDB at all (Word 2002 does). i.e. in Word 2000 you either have to connect via ODBC or using some other indirect route such as connecting to a Jet database that has links to a SQL Server database. As you have noticed, the problem is that you have to distribute an additional something when you distribute the document . - The Name parameter in the OpenDataSource call must either contain a file name, or it has to be blank, in which case a. Word expects you to supply a system/user DSN name in the Connection parameter (i.e. the something is a DSN) b. in Word 2002 and later, you have to supply the parameter Subtype:=wdMergeSubtypeWord2000 If you specify a file (e.g. a file DSN) then c. the file must exist d. the file cannot be at a URL address - it must be a local file or addressable via Windows networking, e.g. by using a drive letter connected to a share or using a UNC network name. So if all your clients happen to be able to connect to a windows networking folder somewhere, you /might/ be able to put the necessary file DSN there In the case of an SQL Server connection, you obviously have to have the appropriate ODBC driver on each system as well. FWIW, I am surprised that have managed to get ActiveDocument.MailMerge.OpenDataSource mydsn, , , , , , , , , , , "DSN=mydsn", strSQL to work. In my experience, when you specify a file dsn, you have to specify the file's pathname in the Name parameter (which is presumably what you have done) and at least "FILEDSN=the pathname of the DSN;" in the Connection parameter. I may be wrong of course, but wonder if your current statement only works because you /also/ have a user/system DSN called "mydsn" as well as the file dsn. or maybe it works if the file dsn is in the default location for file DSNs The only other suggestions I can make are a. if you choose to distribute a file DSN as well as a Word document, it can be loacted anywhere so e.g. you can put it in the same folder as the ..doc and use VBA code to build the full pathname of the file. b. if for some reason you need to avoid having a separate /file/, you can create a user DSN dynamically using VBA and Win32 calls (I have some code somewhere if you need it) then reference it in OpenDataSource. Peter Jamieson "hals_left" wrote in message oups.com... After some searching, this does seem to be a known problem. I have managed to get it working now with a file dsn, below, but this is not the ideal solution as the database will be remote hosted and the clients distributed. If anyone has any solutions or know the versions that do support this please let me know. thanks. ' ~~~~~~~~~ using a file dsn ActiveDocument.MailMerge.OpenDataSource mydsn, , , , , , , , , , , "DSN=mydsn", strSQL ActiveDocument.MailMerge.Execute |
#4
|
|||
|
|||
Peter, Thanks for the info on dsn.
It is surprising that word 2K doesnt use OLEDB for mailmerge. The comments you made on my dsn working are probably true - it was late and dsn is new to me, as I usually use dsn-less connections. I dont reacall being promted for a location to store it so it must have been a system dsn. While a dsn isnt ideal it may actually work OK over VPN with a mapped drive letter, I will have to test this. There may be some authentication issues too. Ideally I want the same solution (single distributed word file with signed vba) for Word2K & 2002 Clients. Do you think its a bad approach to re-invent the wheel here and write mailmerge type of functionality using code to create multiple instances of a template and replace placeholders with adodb recordset fields . Something like this strConn = "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=pubs;" & _ "Integrated Security=SSPI" strSQL = "Select stor_id, stor_name from stores" objConn.Open strConn Set objRS = objConn.Execute(strSQL) While Not objRS.EOF Application.Documents.Add ("myTemplate.doc") With Documents(Application.Documents.Count) .Range.Find.Text = "STOR_ID" .Range.Find.Replacement.Text = objRS(0) End With objRS.MoveNext Wend Would this approach be any less efficient or more error prone for clients, for 100 documents than a mailmerge ? |
#5
|
|||
|
|||
and dsn is new to me, as I usually use dsn-less connections. I dont
I've seen tiny bits of evidence that Word does set up DSN-less connections itself in some circumstances but I've never been able to do it using either OpenDataSource or the DATABASE field. While a dsn isnt ideal it may actually work OK over VPN with a mapped drive letter, I will have to test this. There may be some authentication issues too. Never had to try that myself so would be interested in the outcome. Ideally I want the same solution (single distributed word file with signed vba) for Word2K & 2002 Clients. One problem you may face is that you may have to use the Subtype parameter with OpenDataSource in Word 2002, certainly if you use a blank Name parameter whereas it is regarded as invalid in Word 2000. I think you can get around this in VBA by wrapping the calls in different subs/functions and calling the correct one depending on the Word version. Another thing to notice is that if you need to get tthe details of an existing connection you can't usually do it in Word 2002 via the MailMerge.DataSource object because of an error in Word. I suspect in your case neither of these things will be an issue though. Do you think its a bad approach to re-invent the wheel here and write mailmerge type of functionality using code to create multiple instances of a template and replace placeholders with adodb recordset fields . .... Would this approach be any less efficient or more error prone for clients, for 100 documents than a mailmerge ? Generally speaking, I believe it is less efficient, but that would not usually be my primary concern unless the merges were very large. I would usually be more concerned with function (i.e. "does it do what is needed"), stability, distributability (as you are) and maintenance. If you are essentially using Word as a development platform with simple text replacement facilities, and end users never need to modify the document content, insert new "placeholders" etc. I think it's a good approach. As soon as end users need to be able to tweak stuff, you need to be sure your code is robust, they understand how the placeholder system works, and so on. For example, in theory users can nest fields in the built-in mailmerge system. You probably don't need to allow that but if you do, the coding effort increases dramatically. Arguably, "rolling your own" merge code gives you more control and makes certain types of merge simpler, e.g. when you need to output one document per record in the data source, or one print job per record in the data source (important if, for example, documents are being stapled automatically by the printer). Peter Jamieson wrote in message oups.com... Peter, Thanks for the info on dsn. It is surprising that word 2K doesnt use OLEDB for mailmerge. The comments you made on my dsn working are probably true - it was late and dsn is new to me, as I usually use dsn-less connections. I dont reacall being promted for a location to store it so it must have been a system dsn. While a dsn isnt ideal it may actually work OK over VPN with a mapped drive letter, I will have to test this. There may be some authentication issues too. Ideally I want the same solution (single distributed word file with signed vba) for Word2K & 2002 Clients. Do you think its a bad approach to re-invent the wheel here and write mailmerge type of functionality using code to create multiple instances of a template and replace placeholders with adodb recordset fields . Something like this strConn = "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=pubs;" & _ "Integrated Security=SSPI" strSQL = "Select stor_id, stor_name from stores" objConn.Open strConn Set objRS = objConn.Execute(strSQL) While Not objRS.EOF Application.Documents.Add ("myTemplate.doc") With Documents(Application.Documents.Count) .Range.Find.Text = "STOR_ID" .Range.Find.Replacement.Text = objRS(0) End With objRS.MoveNext Wend Would this approach be any less efficient or more error prone for clients, for 100 documents than a mailmerge ? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
directly connecting the MailMerge object to MS SQL server | Mailmerge | |||
Server Change Affecting Mail Merge Macros | Mailmerge | |||
Running document macro from server | New Users | |||
Getting mapped on the server | Microsoft Word Help | |||
Mailmerging data from SQL Server tables | Mailmerge |