Mail Merge with Access Query

[gmaxey]

I have an access .accdb format file which contains a table and a query. One of the query values is determined by a function in the VBA project.

My goal is to use this database as the source for a Word mail merge process. However, when I attempt to attach define the source for the mail merge in Word, the only data available is the table data.

From what I've read and (with very limited understanding of Access to start with) it seems that Access queries can't used (or be connected) as the data source in a Word mail merge.

If true, can anyone suggest the best or (generally accepted) method for using the query data as a mail merge source. I can successfully export the query to and Excel file and use the Excel file as the data source, but that just seems cumbersome and I'm thinking there should be a better way.

Thanks.

[ranman256]

Bull. Access tbl/qry ARE data sources....I use them all the time.
Use the data merge wizard and it will let you pick the db, then pick the query. (now old versions of word canT read new versions of Access)

[gmaxey]

ranman,

Throw the bull flag as you wish, but despite your confidence, it is not working here. I will be happy to send you the database to try on your end.

I think the issue is that the query is not a "simple query." One of the query fields is determined by a function.

If I just create a simple query then you are correct, the query appears as a source.

Back to the original question.

[ranman256]

I would have to see this query. It could be that it needs parameters. That my prevent external use.
(just send the sql)

[gmaxey]

ranman,

I think it is the function "fcn" that is causing the problem (that might be what you call a parameter, I just don't know)"

SELECT Table1.[First Name], Table1.[Last Name], Table1.Title, Table1.Business, Table1.[Street Address], Table1.City, Table1.State, Table1.[Zip+4+2], Mid([Zip+4+2],1,10) AS ZipMerge, [Barcode ID] & [SRV Typ ID] & [Mailer ID] & [Serial Num] AS [Tracking Code], Mid([Zip+4+2],1,5) & Mid([Zip+4+2],7,4) & Mid([Zip+4+2],12,2) AS [Routing Code], fsb([Tracking Code],[Routing Code]) AS [Encoded 65-character String], [Barcode ID] & "-" & [SRV Typ ID] & "-" & [Mailer ID] & "-" & [Zip+4+2] AS [Human Readable]
FROM Table1;

Thanks.

[ranman256]

Yep. Thats it. Word dont like those external functions (that work only in Access).
You may have to run a macro to build a report table that resolves the functions, so Word can read from that.

[gmaxey]

ranman,

Thanks for the confirm signal.

Unfortunately I don't know where to begin writing macros in Access. Absent that, is my current clumsy method of exporting the query to Excel a sound approach?

[snb]

Basically:
The file c00
the Table: c01

Sub samenvoegen_met_Access_DDE_Tabel()
    c00 = "G:\access\fiets.mdb"
    c01 = "[tabel1]"
    
    With Documents.Add
        .MailMerge.OpenDataSource c00, , , , True, , , , False, , , , "SELECT * FROM " & c01

        .SaveAs "G:\gekoppeld met databestand fiets.doc"
    End With
End Sub

To use a defined query in the Access database:
The defined Query: c01

Sub samenvoegen_met_Access_DDE_Tabel()
    c00 = "G:\access\fiets.mdb"
    c01 = "[Q_selectie]"
    
    With Documents.Add
        .MailMerge.OpenDataSource c00, , , , True, , , , False, , , , "SELECT * FROM " & c01

        .SaveAs "G:\gekoppeld met databestand fiets.doc"
    End With
End Sub

To use a custom query from Word:
The search string has to be embedded in ' .... '

Sub M_snb()
    c00 = "G:\access\Kopie van fiets.mdb"
    c01 = "[fiets]"
    c02 = "[plaats]"
    
    With Documents.Add
        .MailMerge.OpenDataSource c00, , , , True, , , , False, , , , "SELECT * FROM " & c01 & " WHERE " & c02 & "='MALDEN'"
'        .SaveAs "G:\gekoppeld met databestand fiets.doc"
'        .Close 0
    End With
    
End Sub

[gmaxey]

What does any of that have to do with taking an access query which uses a function to produce one of its resultant fields and building an access table which contains the data in that field?

As all of your code refers to "Document" you must be in a Word project. We have already established that since the Access querry is using a function, then it can't be done in the method you propose.

[jonh]

You could create a temp table in Access from the query

select * into [MyNewTable] from [MyQuery]

but you need to delete it before you run the query again, so you might run into problems if this is a multi user db

Private Sub Command0_Click()
    Const newtbl As String = "mynewtable"
    Const qry As String = "query1"
    
    Dim db As DAO.Database, td As DAO.TableDef
    Set db = CurrentDb
    
    On Error Resume Next
    
    'see if table already exists
    Set td = db.TableDefs(newtbl)
    If Err.Number = 0 Then
        'table exists. delete it
        DoCmd.DeleteObject acTable, newtbl
        If Err.Number <> 0 Then
            'failed to delete the mofo :(
            MsgBox Err.Description
            Exit Sub
        End If
    Else
        Err.Clear
    End If
    
    On Error GoTo 0
    CurrentDb.Execute "select * into " & newtbl & " from " & qry
    RefreshDatabaseWindow
End Sub

[gmaxey]

Jonh,

Great. Thanks!. I got an error when I tried to run the code on the DOA.Database line so I just changed both DOA.Database and DOA.TableDef to object. It seems to work, so I suppose that will be ok.