Pass SQL statement in word document and then print it off

[aman]

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.

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

 

[aman]

Hi Guys

Can anyone please help me to solve this problem.

Thanks

 

[aman]

Hi All

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

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

 

[aman]

Can anyone please help me to figure out this problem.

Thanks