Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    We have VBA code within our database.

    We have numerous PC's with the database running on. However when the database runs in a Terminal Services session it generates a non csv file. The exact same code on the standalone PC's generates the file perfectly normal and generate the csv file.

    This is causing the word mailmerge to not print, as obviously it doesn't understand the format.

    The vba code is:

    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    Dim strletterpath As String
    Dim strletterfile As String
    Dim strworddoc As String
    Dim objWord As Word.Application
    Dim objletter As Word.Document
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim intEnd As Integer
    Dim varsalesid As Variant
    Dim varoffice As Variant
    Dim strlogon As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim varofficebased As Variable
    Dim ol As New Outlook.Application
    Dim ns As Outlook.NameSpace
    Dim newMail As Outlook.MailItem
    Dim varAddress

    If IsNull(PurchFinance) = True Or IsNull(LocalAuth) = True Or IsNull(countauth) = True Or IsNull(PriceAgreed) = True Or IsNull(DepHeld) = True Or IsNull(VSols) = True Or IsNull(Psols) = True Or IsNull(ProgSalesID) = True Or IsNull(VSolContactID) = True Or IsNull(PSolContactID) = True Then

    msgbox "There is not the required information to complete the Sales Memo and Instruct Solicitors", vbOKOnly, "Issue Sales Memo's"

    Else

    strlogon = Environ("Username")
    strletterpath = DLookup("tblSalesMan![LetterPath]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))
    varHipRecDate = DLookup("[HipRecDate]", "tblProperty", "PropertyID=" & Me.PROPERTYID)


    If IsNull(varHipRecDate) = True Then

    strletterfile = "qry1061.txt"
    strworddoc = "1056.doc"

    'On Error GoTo NoMergeError

    'Open Query add Property Condition and then run export

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qry1061")
    strSQL = qdf.SQL

    'lose last character
    'Find position of ;
    intEnd = InStr(strSQL, ";")
    strSQL = Left(strSQL, intEnd - 1)

    Set qdf = Nothing

    'add property condition current one one
    strSQL = strSQL & " where OffersID= " & OffersID


    'Delete old export query and make new one
    'On Error Resume Next
    dbs.QueryDefs.Delete "qry1061Export"

    'On Error GoTo NoMergeError
    dbs.CreateQueryDef "qry1061Export", strSQL

    Set qdf = Nothing
    Set dbs = Nothing

    'Now export as text file
    DoCmd.SetWarnings False
    DoCmd.TransferText acExportDelim, , "qry1061Export", strletterpath & strletterfile, True
    DoCmd.SetWarnings True

    'Because we are using a query with NO Form parameters we could have used the query
    'directly with ODBC

    'Open Word
    Set objWord = New Word.Application
    'Letter document would be open
    Set objletter = objWord.Documents.Open(strletterpath & strworddoc)
    objletter.MailMerge.OpenDataSource (strletterpath & strletterfile)
    objletter.MailMerge.Destination = wdSendToPrinter 'normally print
    objletter.MailMerge.Execute
    'not normally here
    'objWord.Visible = True

    'tidy up
    objletter.Close False 'close without saving
    objWord.Quit False

    'Tidy PC memory
    Set objletter = Nothing
    Set objWord = Nothing

    msgbox "Sales Memo Letters Printed"

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There are numerous possibilities when you swith from local execution to remote execution using Terminal Services. First, are you using the exact same version of Access on the local workstations as the Terminal Services server? Second, you undoubtedly have different versions of Windows on the local workstation and on Terminal Services. Also, does each Terminal Services User have his own copy of the database? Finally, you have Warnings turned off when you do the export, so you may be missing an error message, or some other key piece of info during the export. Do you actually get a file, and if so, what sort of format does it have? Finally, you should probably use an Export Specification to make sure that it defaults to a CSV format - there are a number of other options that can be chosen.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •