Pass SQL statement in word document and then print it off

aman

Registered User.
Local time
Today, 03:27
Joined
Oct 16, 2008
Messages
1,251
Hi All

The following code works fine if We write it in Excel/vba but if my frontend is Access and I want to write same type of code so that it passes a SQL statement to word document and then print the mail merge word document letters.

Code:
Sub Merge_abc()
strWorkbookName = "J:\System.mdb"
With CreateObject("Word.Application").Documents.Add("J:\Letter1.doc").MailMerge
  .MainDocumentType = 0
  .destination = 1
  .OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `tblmaster` where Printpoolno='" & Textbox1.Value & "'"
  .Execute
   .Parent.Close 0
 End With
  MsgBox "The letters have been printed off"
 Exit Sub
 End If

I hope anyone can point me in the right direction.

Thanks
 
Hi Guys

Can anyone please help me to solve this problem.

Thanks
 
Hi All

The following code gives me an error message "The String is longer than 255 characters".

Code:
Sub Merge_snb()
Dim wdApp As New Word.Application
Dim wdMMDoc As Word.Document
Set wdMMDoc = wdApp.Documents.Add("J:Version1.doc")
    wdApp.Visible = True
' Connect to datasource and run sqlstatement to pick the info from the form
   [COLOR=red] wdApp.ActiveDocument.MailMerge.OpenDataSource _
        Name:=Application.CurrentProject.FullName, _
        OpenExclusive:=False, _
        LinkToSource:=True, _
        Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Project Version2.db;" & _
         "Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet" _
         , SQLStatement:="SELECT * FROM tblcustomer Where ProjectRef='" & Text0 & "' and (Grading='Platinum - Next Address' Or Grading='Gold - Next Address' Or Grading='Silver - Next Address')"
[/COLOR]' Run the merge
    wdMMDoc.MailMerge.Execute
' Close the Merge Document
    wdMMDoc.Close False
    wdApp.Visible = True
' Bring the merged letter to the front
    wdApp.Activate
' tidy up
    Set wdApp = Nothing
    Set wdMMDoc = Nothing
 End Sub

Thanks
 
Can anyone please help me to figure out this problem.

Thanks
 

Users who are viewing this thread

Back
Top Bottom