Word MailMerge OpenDataSource - password protected ACCDE (1 Viewer)

[francdatabase]

Hello all,
I hope this will be a quick one for someone who has done this before, but I am going round in circles!
I am trying to use a password-protected ACCDE as the data source for a Word MailMerge but keep getting the 'Error has occurred: Not a valid Password' pop-up. I have checked the password and it is correct. Here is the code I am trying with:

    With theDoc.MailMerge
            .OpenDataSource Name:=myDatabase, _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, Revert:=False, PasswordDocument:="myWord", _
            Format:=wdOpenFormatAuto, Connection:="TABLE " & tableName, SQLStatement:= _
            "SELECT * FROM [" & tableName & "]", SQLStatement1:=""

            .Destination = wdSendToNewDocument
            .Execute
    End With

 

[arnelgp]

you create a CSV from the recordset of your table and use it as Datasource of your Mailmerge:

Sub CreateCSVFromRecordset()
    Dim conn As Object
    Dim rs As Object
    Dim strDataSource As String
    Dim strPassword As String
    Dim strConnection As String
    Dim strSQL As String
    Dim strCSVFile As String
    Dim fso As Object
    Dim txtFile As Object
    Dim i As Integer
    Dim strLine As String
  
    ' Set your parameters
    ' put the .accde path below
    strDataSource = "C:\Path\To\Your\Database.accde"
    ' put the password
    strPassword = "myWord"
    ' put the correct table name
    strSQL = "SELECT * FROM [YourTableOrQueryName]"
  
    strCSVFile = Environ$("Temp") & "\MailMergeData.csv"
  
    On Error Resume Next
    Kill strCSVFile
  
    If Err Then
        i = InStrRev(strCSVFile, ".")
        strCSVFile = Left$(strCSVFile, i - 1) & "1" & Mid$(strCSVFile, i)
    End If
  
    ' Build connection string
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & strDataSource & ";" & _
                   "Jet OLEDB:Database Password=" & strPassword
  
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set fso = CreateObject("Scripting.FileSystemObject")
  
    On Error GoTo ErrorHandler
  
    conn.Open strConnection
    rs.Open strSQL, conn, 3, 1
  
    Set txtFile = fso.CreateTextFile(strCSVFile, True)
  
    ' Write header row
    strLine = ""
    For i = 0 To rs.Fields.Count - 1
        If i > 0 Then strLine = strLine & ","
        strLine = strLine & """" & rs.Fields(i).Name & """"
    Next i
    txtFile.WriteLine strLine
  
    ' Write data rows
    Do While Not rs.EOF
        strLine = ""
        For i = 0 To rs.Fields.Count - 1
            If i > 0 Then strLine = strLine & ","
          
            If IsNull(rs.Fields(i).Value) Then
                strLine = strLine & ""
            Else
                ' Escape quotes and wrap in quotes if contains comma
                Dim fieldValue As String
                fieldValue = CStr(rs.Fields(i).Value)
                fieldValue = Replace(fieldValue, """", """""")  ' Escape quotes
              
                If InStr(fieldValue, ",") > 0 Or InStr(fieldValue, vbCrLf) > 0 Then
                    strLine = strLine & """" & fieldValue & """"
                Else
                    strLine = strLine & fieldValue
                End If
            End If
        Next i
        txtFile.WriteLine strLine
        rs.MoveNext
    Loop
  
    txtFile.Close
    rs.Close
    conn.Close
  
    Set txtFile = Nothing
    Set fso = Nothing
    Set rs = Nothing
    Set conn = Nothing
  
    'MsgBox "CSV file created: " & strCSVFile, vbInformation
  
    ' Set up mail merge
    With ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=strCSVFile
    End With
  
    'MsgBox "Mail merge ready!", vbInformation
    Exit Sub
  
ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical
    If Not txtFile Is Nothing Then txtFile.Close
    If Not rs Is Nothing Then If rs.State = 1 Then rs.Close
    If Not conn Is Nothing Then If conn.State = 1 Then conn.Close
End Sub

 

[francdatabase]

Hi @arnelgp ,

Thank you for your reply. That would definitely do it!

The application I am looking at uses the OpenDataSource method in a lot of places so I would need to replace a lot of code.

Will it definitely not work trying to supply the password to the protected accde whilst using .OpenDataSource?

If not, then your method is great, but if there is a way to make .OpenDataSource work with a password it would save a lot of time. Is it possible?

 

[Mark_]

If you really want to keep this in an ACCESS database, you can create a "Side end" that has JUST what you want your query to return. This is filled by an append query in your primary database, is unencrypted, and filled BEFORE you run your mail merge. After you do your merge, you then have a delete query that removes the data. The "Side end" can be in a different file location than your primary database.

This approach is useful when you don't want some users to have access to the back end and the back end's directory, but they still need to be able to do something with the data. Most often this is done for reporting, but your mail merge is another.

 

[Gasman]

How would one run that query from word, before attempting the mailmerge?

 

[Mark_]

A user with rights in the ACCESS database would do the export. Goal is to make sure someone who is just doing the merge / send letters wouldn't be the one putting their fingers in the database.

Real world where I had to do this (admittedly 30 odd years ago) we were kicking data out from casino management software. Tightly controlled who can/can't touch the "real data" but we had a couple SQL servers set up for various reporting and analysis use that had just what they needed exported.

For OP, odds are the same person doing the mail merge will need to go in and "Press the button", but also allows them to have "Boss presses button and tells worker bee to do merge".

 

[Gasman]

Thank you.
I keep track of my cards and blood pressure in Access DB.
I use Excel because the graphing is way better for graphs.
I run the queries from Excel via connections, no vba.
I myself, would not want to have to pen the dB, and run a query, just to get the latest data.
Now after I asked the question, I was wondering whether the query could be in the side end, and access the main dB?, and so emulate my setup?

 

[Mark_]

IF you do decide to export to an intermediate database, just remember you can do a LOT of pre-formatting with the export query.

For the casino software, we built up consolidated results for some of it. Think "Instead of submitting what each player made on a per-game basis, sum it up by game type for a period".

Made it easier for reporting and analysis if they track "Customer made $375 on Black Jack on 2026 01 10".