FYI: How to populate a document using ADO/ODBC without the use of mailmerge.

[ImOkYoureNot]

Took me a while to think of this, but I thought I would share it.
Pissed of at MS for not doing Mailmerge right and using the useless
ODC instead of allowing true SQL statements. Anyhow here it is:

1) Create a document and put some Docvariables in it using
Insert/Fields/Docvariable. You must use the same name as you have in
the database recordset you are interested in.

2) Paste the macro below in the documents project Tools/Macro/Visual
Basic. Or put in Normal if you want.
Edit the oConn.Open to reflect your ODBC or direct database connection
string.

3) Change the select statement to use whatever WHERE statetement you
want for the recordset.

If you have other internal variables from other sources you will have
to adjust the code.

Plz, no emails. Thanks.

=========================================================================
Option Explicit

Sub main()
Dim splitCode As Variant, code As String, cnt As Integer
Dim oConn As ADODB.Connection, oRs As ADODB.Recordset
Dim i As Integer, j As Integer
Set oConn = New ADODB.Connection
Set oRs = New ADODB.Recordset
' Change to your own connection or use DSN/ODBC if you like.
Call oConn.Open("Provider=MSDASQL.1;Persist Security
Info=True;Extended Properties=""DSN=spec;UID=;PWD=;SourceDB=c:\Prop6;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"";Initial
Catalog=(Default)")
' Use your own SQL or pass it as parameter.
Call oRs.Open("select * from spec where uniqcode=3000", oConn,
adOpenDynamic)

If Not oRs.EOF Then
' Delete all variables. If you have non-database variables you have to
write
' extra code to bypass them.
For i = 1 To ActiveDocument.Variables.Count
ActiveDocument.Variables.Item(i).Delete
Next
' Get count of fields on document.
cnt = ActiveDocument.Fields.Count
For i = 1 To cnt
' split the code for each field and get the name of the field
from the array
' it actually follows the word DOCVARIABLE and space(s)
splitCode = Split(ActiveDocument.Fields.Item(i).code, " ")
For j = 0 To 3
If splitCode(j) = "DOCVARIABLE" Then
code = splitCode(j + 2)
On Error GoTo varExist
' Add variable and value. If it already exists (you
could use the same field more than once)
' then it will goto error routine to simply set it.
Call ActiveDocument.Variables.Add(code,
oRs.Fields(code))
Exit For
End If
Next
Next
' force refresh
ActiveDocument.StoryRanges(wdMainTextStory).Fields.Update
Else
MsgBox ("No such record")
End If
' cleanup
oRs.Close
oConn.Close
Set oRs = Nothing
Set oConn = Nothing
Exit Sub

varExist:
If Err = 5903 Then
ActiveDocument.Variables.Item(code).Value = oRs.Fields(code)
Else
MsgBox ("Document error " & Error)
End If
Resume Next
End Sub

 

[Peter Jamieson]

Oh, looks like you worked out what you needed.

But FWIW you can use SQL directly in OpenDataSource.If you're using an ODBC
data source you need a DSN of some kind (Word won't make DSN-less
connections). With OLEDB, in some cases you can do something similar by
putting the connection info. in the Connection parameter and having an empty
..odc, and you may be able to use a .udl file instead of a .odc anyway.