Results 1 to 4 of 4
  1. #1
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17

    word mailmerge with access/sql

    I am not really sure if this question has to go here or in sql section. Anyhow I confide in your understanding.
    At the moment, I am using the following code in access VBA to mailmerge and print a letter taking the data from an excel sheet
    Code:
    ...
    filepath = ThisWorkbook.Path
    
    
    With WordApp
        .Visible = True
        Set WordDoc = .Documents.Open(filepath & "\LetteraNuoviSoci.docx")
    End With
    'MailMerge selected records from table to Word document
    With WordApp
        .ActiveDocument.MailMerge.OpenDataSource Name:=filepath & "\" & ThisWorkbook.Name, _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, _
            SQLStatement:="SELECT * FROM `rangesoci` WHERE [Numero Tessera] =" & numerotessera, SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess
        With .ActiveDocument.MailMerge
            .Destination = wdSendToPrinter
            .SuppressBlankLines = True
                          
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
               
    End With
    
    
    WordDoc.Close SaveChanges:=False
    WordApp.Quit
    The code is not the best thing around (I am learning slowly...), but it works: the letter is printed with the data belonging to the record in sheet table with key=numerotessera. No problem here.



    For a number of reasons, I am moving the excel table to SQLEXPRESS server, linking the table in the access application: in other words, I keep the Access frontend, and use sqlexpress as my backend. Now the mailmerge has to get its data from the sql table, and I have been struggling with this connection for two weeks now without success. I tried to follow a number of suggestions found here and there, and I am beginning to suspect I am missing something basic that everyone gives for granted (and I have not learnt enough). This is my last attempt:

    Code:
    Set WordApp = New Word.Application
    filepath = CurrentProject.Path
    Debug.Print filepath, Numerotessera
    With WordApp
        .Visible = True
        Set WordDoc = .Documents.Open(filepath & "\LetteraNuoviSoci.docx")
    End With
    'MailMerge selected records from table to Word document
    With WordApp
    
    
        With .ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            strConnection = "Provider=SQLNCLI11;Server = \\DESKTOP-FMBJ0FJ\SQLEXPRESS;" & _
                    "AttachDbFilename=|DataDirectory|RicevuteCPNVersion2.mdf" & _
                   "Database=RicevuteCPNVersion2;Trusted_Connection=yes;"
             .OpenDataSource Name:="c:\testhyp2\RicevuteConPrimaNotaFE.accdb", _
                    Connection:=strConnection, _
                    SQLStatement:="SELECT * FROM `dbo.LibroSoci` WHERE [Numero Tessera] =" & Numerotessera
            .Destination = wdSendToPrinter
            .SuppressBlankLines = True
            
     _
    
    
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
               
    End With
    
    
    WordDoc.Close SaveChanges:=False
    WordApp.Quit
    The error is always the same, "Cannot open the connection". But word freezes complaining that "the database has been put by user admin in a state that prevents it from being opened or locked", and another error "cannot find c:\testhyp2.mdb".
    Sorry for the confusion, I am really confused myself. Any suggestion will be appreciated. Thank you

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why not link the table into the Access front-end so you don't need the connection info in your code?

    You can try to use legacy "SQL Server" driver that comes with Windows?
    https://www.quackit.com/microsoft_ac...ccess_2016.cfm

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17
    hello, thanks for your interest. You are right, of course. It seems I managed to make some progress resorting to the lazy practice of recording a macro. So I linked to my table in a new word document, and recorded two macros, one linking to the sql table and one linking to the access table linked to the real sql. those are the results


    Code:
    'link to sql table, native driver 11.1
    'MailMerge selected records from table to Word document
    With WordApp
        .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
        .ActiveDocument.MailMerge.OpenDataSource Name:= _
            "C:\Users\User\Documents\My Data Sources\RicevuteCPNVersion2 LibroSoci.odc" _
            , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="""";Initial Catalog=RicevuteCPNVersion2;Data Source=DESKTOP-FMBJ0FJ\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DESKTOP-FMBJ0FJ" _
            , SQLStatement:="SELECT * FROM ""LibroSoci"" WHERE [Numero Tessera] =" & Numerotessera, SQLStatement1:="", SubType _
            :=wdMergeSubTypeOther
    
    
    
    'link to linked access table
    
    
        ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
        ActiveDocument.MailMerge.OpenDataSource Name:= _
            "C:\Users\User\Documents\My Data Sources\(Default) dbo_LibroSoci.odc", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.16.0;Password="""";User ID=Admin;Data Source=c:\testhyp2\RicevuteconprimanotaFE.accdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:E" _
            , SQLStatement:="SELECT * FROM `dbo_LibroSoci`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeOther
    Now the link to the real sql table seems to work, whereas the other shows the old error failing to open the connection. But both links work fine in the word test file.I am working on it, it seens more logical to refer to the access table, also because the other forms in the frontend refer to it. Thanks again for your interest

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is some code I use for mail merge. You'll notice it exports the selected record to a text file (like a comma delimited file) then uses that as the source for the mail merge (avoiding the error you describe towards the end of your initial post).
    Code:
    Private Sub cmdPrintCurrentRecord_Click()
    Dim ReadOnlyMode As Boolean, strDocument As String
    
    
    strDocument = Me.txtPathToYourWordDocument 'uses a text box on your form to get the full name of the Word mail-merge template or use a dlookup to get it from a settings table
    If Dir(strDocument) = "" Then
        MsgBox strDocument & " not found!", vbInformation, "Document not found!"
        Exit Sub
    End If
    response = MsgBox("Do you wish to generate a Read-Only Mail Merge document using " & Chr(13) & _
            strDocument & Chr(13) & Chr(13) & _       
            Chr(13) & Chr(13) & "Select YES for Read-Only" & _
            Chr(13) & "Select NO for Read-Write" & _
            Chr(13) & "Select CANCEL to halt.", vbYesNoCancel + vbQuestion, "Generate a Mail Merge Document")
    
    
    If response = vbCancel Then Exit Sub
    
    
    If response = vbYes Then
        ReadOnlyMode = True
    Else
        ReadOnlyMode = False
    End If
    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    
    
    Dim iFileCount As Integer
    Dim strMailMergeFolder as string
    
    
    strMailMergeFolder = CurrentProject.path
    vcKillMailMerge
    If Right(strMailMergeFolder, 1) <> "\" Then strMailMergeFolder = strMailMergeFolder & "\"
    iFileCount = 1
    On Error GoTo Error_Kill
    Kill_File:
    If Len(Dir(strMailMergeFolder & "DB_MailMerge" & iFileCount & ".txt")) > 0 Then Kill strMailMergeFolder & "DB_MailMerge" & iFileCount & ".txt"
    GoTo relink
    
    
    Error_Kill:
    iFileCount = iFileCount + 1
    GoTo Kill_File
    
    
    relink:
         DoCmd.TransferText acExportDelim, , "qryYourQueryForCurrentID", strMailMergeFolder & "DB_MailMerge" & iFileCount & ".txt", True
         RelinkDocMailMergeText strMailMergeFolder & "DB_MailMerge" & iFileCount & ".txt", strDocument, ReadOnlyMode
         
    
    
    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    
    
    End Sub
    
    
    Sub RelinkDocMailMergeText(strMailMergeFileName As String, strDoc As String, boReadOnlyMode As Boolean)
    Dim WordApp As Object
    Dim retcode
    Dim strFileName
    Dim sPathNotmalTemplate
    'On Error Resume Next
    
    
    
    
    strFileName = strDoc
    
    
    If Dir(strFileName) = "" Then
        MsgBox strFileName & " was not found!  Is it hidden?", vbExclamation, "Document not in this folder!"
        Exit Sub
    End If
    
    
    
    
    Set WordApp = CreateObject("Word.Application")
             
    With WordApp
    
    
              .Application.Visible = True
              On Error GoTo telluser
              .StatusBar = "Preparing to add a new Mail-Merge document in Word format.  Please wait..."
              
              .Documents.Open strFileName, ReadOnly:=boReadOnlyMode, AddToRecentFiles:=False, Revert:=True
    
    
        .ActiveDocument.MailMerge.OpenDataSource Name:= _
            strMailMergeFileName, ConfirmConversions:=False, ReadOnly:= _
            boReadOnlyMode, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False
        .NormalTemplate.Saved = True
        .ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
        .ActiveDocument.MailMerge.DataSource.ActiveRecord = -4 'first record
        .ActiveDocument.MailMerge.Destination = 0    'new record
        .ActiveDocument.MailMerge.Execute
        .Documents(1).Printout 'print merged doc
        .Documents(1).Close 0 'wdDoNotSaveChanges  close merged doc
        .ActiveDocument.Close 0 'close mail-merge template
    End With
    Set WordApp = Nothing
    Exit Sub
    
    
    telluser:
    Set WordApp = Nothing
    MsgBox "An error occurred while attempting to open a Mail-Merge document:" & Chr(13) & strFileName, vbExclamation, "Add New Mail-Merge Document"
    End Sub
    
    
    
    
    
    
    
    
    Public Sub vcKillMailMerge()
        Dim strFileName As String
        Dim iFolderCount As Integer
        Dim strFolders() As String
        Dim i As Integer
        Dim strFolder As String, strFilePattern As String
        
        On Error Resume Next
        
        strFolder = CurrentProject.path
        strFilePattern = "*DB_MailMerge*"
         'Collect child folders
        strFileName = Dir$(strFolder & "\", vbDirectory)
        Do Until strFileName = ""
            If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
                If Left$(strFileName, 1) <> "." Then
                    ReDim Preserve strFolders(iFolderCount)
                    strFolders(iFolderCount) = strFolder & "\" & strFileName
                    iFolderCount = iFolderCount + 1
                End If
            End If
            strFileName = Dir$()
        Loop
         'process files in current folder
        strFileName = Dir$(strFolder & "\" & strFilePattern)
        Do Until strFileName = ""
            '*******************************************
            Kill strFolder & "\" & strFileName
             '*******************************************
            strFileName = Dir$()
        Loop
    End Sub
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access VBA Mailmerge Word Document
    By sjhime in forum Programming
    Replies: 2
    Last Post: 02-26-2018, 12:32 PM
  2. MS Access and MS Word MailMerge
    By tgall in forum Access
    Replies: 1
    Last Post: 11-14-2015, 11:02 PM
  3. Access and Word MailMerge
    By saharmon in forum Access
    Replies: 0
    Last Post: 10-28-2014, 02:37 PM
  4. Access and mailmerge in Word
    By Popnorth in forum Access
    Replies: 2
    Last Post: 01-21-2013, 08:25 AM
  5. Mailmerge: Making a list - Access to word
    By Ribido in forum Access
    Replies: 3
    Last Post: 01-07-2012, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums