Replace() inside a query for MSWord OpenDatasource using mailmerge with VBA

Question

I have a field in a database, ending with :. My idea was to use { INCLUDEPICTURE "pathname{MERGEFIELD f1}.jpg", having stored previously the images' names in a folder with the names of the field values. However, I cannot name a file with a : character in it, and I thought of using the replace function inside the query that is inside the OpenDatasource function in ms word, but it does not work apparently.

Here goes the code:

Private Sub Document_Open()
    Dim strConnection As String
    With Me.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
           Name:="E:\jobDB.mdb", _
           LinkToSource:=True, AddToRecentFiles:=False, ConfirmConversions:=True, _
           Connection:="TABLE t1", SQLStatement:="SELECT t1.name, replace(t1.f1, """:""", """") as repFld FROM t1;"
    End With
End Sub

Private Sub Document_Close()
   If Me.MailMerge.State = wdMainAndDataSource Then _
      Me.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Doing so, I was hoping to remove the : and make INCLUDETEXT work, but this does not work and when the code is executed, a dialog box is shown for me to select the table of the database.

I don't know if theres is something wrong, or if there is another way of achieving this, or if there is any field or tag or flag to replace characters inside a mergefield.

I wanted to use VBA only for connecting to the datasource andbut I wanted to use mailmerge for this since I researched how it works and I could do what i wanted with conditional fields, otherwise I will have to use VBA and formfields.

Your post is ambiguous. If the ':' part of the field name, or part of the record name? If it's part of the field name, the mailmerge will work just fine - Word will ignore the ':' when you insert the mergefield. If it's part of the field name, you will have to clean up the data at its source, since the ':' cannot be removed via the SQL or by field coding in the document and ':' is not valid in a filename. — macropod, May 03 '21 at 20:09
Your replace function is working on the field name, not the result. You would have to run a replace function on the data that comes back from the query not the query itself. — HackSlash, May 03 '21 at 20:11
@macropod the ":" is part of each record value in that field. — Mister Equis, May 03 '21 at 20:25
@HackSlash But, doesn't access sql have a replace fu nction? I tried it inside ms access in a query and it worked. — Mister Equis, May 03 '21 at 20:28
@macropod I ddi not design the databas enadn even though i have access to it I should not modify it. If I designed it, I would have added the ":" from a query if I wanted or in code. — Mister Equis, May 03 '21 at 20:29
Regardless, your mailmerge simply isn't going to work. Messing with the SQL isn't going to change what the mergefield sees. — macropod, May 03 '21 at 21:16

jonsson · Answer 1 · 2022-12-11T12:24:31.897

There was nothing wrong with your general approach. It's just that Word is very particular about the syntax you use in an OpenDataSource query, and often does not report connection errors, in some cases just retrieving the data that the SQL SELECT * FROM [some table name] would retrieve.

In this case, the chances are that the only problems are that you needed "":"" rather than """:""", to surround the field alias name repFld with straight quote ' characters, and to surround the table name with [], like this:

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, replace(t1.f1, "":"", """") as 'repFld' FROM [t1]"

You shouldn't actually need any of the other parameters in the call.

Personally I prefer to use single quotation marks inside Jet/Access SQL, like this, as it works and differentiates more clearly between the SQL code and the Word VBA code. But you don't have to:

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, replace(t1.f1, ':', '') as repFld FROM [t1]"

ISTR that the replace function did not always work when connecting from Word to Access, because some Access VBA functions were missing from an internal table that the OLE DB provider relied on. If replace() doesn't work and you always have one and only one : character at the end of the field data, you should really be able to use

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, left(t1.f1, len(t1.f1) - 1) as 'repFld' FROM [t1]"

(That works with fields with no text in them, too, but if some fields don't have a terminating colon you'd probably need to add some complication).

Replace() inside a query for MSWord OpenDatasource using mailmerge with VBA

1 Answers1