f



How to set Data source in MS Word as MS Access Databases via DDE(*.mdb;*.mde).

How to set Data source in MS Word as MS Access Databases via
DDE(*.mdb;*.mde).
Everytime i click the command button to merge the data, it gives me
item not found and i know that its a problem with my datasource. when i
click ok then it opens the word doucment prompting me which datasource
you want to use, when i click DDE then it gives me the merged data. Is
there anyway i can set it permanantely in my codes below.

Private Sub SetQuery(strQueryName As String, strSQL As String)
 On Error GoTo ErrorHandler
        'set the query from which the merge
' document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description,
vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub cmdMergeIt_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler
' user enters a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code

Dim strSYS_ID_CODE  As String
strSYS_ID_CODE = Me.SYS_ID_CODE.Value
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes
strSQL = "SELECT dbo_SYS_INFO.SYS_NME, dbo_SYS_INFO.SYS_URL,
dbo_SYS_INFO.TEST_BEGIN_DATE, dbo_SYS_INFO.TEST_END_DATE," & _
"dbo_FINDG.FINDG_STAT_DATE, dbo_FINDG_STAT.FINDG_STAT, dbo_FINDG.SMRY
AS SMRY, dbo_FINDG.RSK_DESC AS RSK_DESC, dbo_FINDG.RCMN AS RCMN," & _
"dbo_FINDG.FINDG_NO, dbo_FINDG_RSK_LVL.FINDG_RSK_LVL, dbo_FINDG.CMNT AS
CMNT, dbo_FINDG.FINDG_RSK_LVL_ID, dbo_FINDG.FINDG_NME,
dbo_FINDG.PLCY1," & _
"dbo_FINDG.PLCY2, dbo_FINDG.PLCY3, dbo_FINDG.PLCY4, dbo_FINDG.PLCY5,
dbo_FINDG.PLCY6, dbo_FINDG.PLCY7, dbo_FINDG.PLCY8, dbo_FINDG.PLCY9," &
_
"dbo_FINDG.NEW_CMNT AS NEW_CMNT, dbo_FINDG.URL1, dbo_FINDG.URL2,
dbo_FINDG.URL3, dbo_FINDG.URL4, dbo_FINDG.URL5, dbo_FINDG.URL6,
dbo_FINDG.URL7," & _
"dbo_FINDG.URL8, dbo_FINDG.URL9, dbo_SYS_INFO.SYS_ID_CODE FROM
((dbo_SYS_INFO INNER JOIN dbo_FINDG ON
dbo_SYS_INFO.SYS_ID_CODE=dbo_FINDG.SYS_ID_CODE)" & _
"LEFT JOIN dbo_FINDG_STAT ON
dbo_FINDG.FINDG_STAT_ID=dbo_FINDG_STAT.FINDG_STAT_ID) LEFT JOIN
dbo_FINDG_RSK_LVL ON
dbo_FINDG.FINDG_RSK_LVL_ID=dbo_FINDG_RSK_LVL.FINDG_RSK_LVL_ID" & _
"WHERE dbo_SYS_INFO.SYS_ID_CODE = strSYS_ID_CODE   ORDER BY
dbo_FINDG_RSK_LVL.FINDG_RSK_LVL DESC, dbo_FINDG_FINDG_NO ASC ;"
 'WHERE Contacts.PostalCode = ' " & strPostalCode & " ' ;"

Dim strDocumentName As String  'name of the Word template document
strDocumentName = "\Findings.doc"
'use your template document name above

Call SetQuery("qryFindingRepordForWord", strSQL)
'use your query name above
Dim strNewName As String  'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc
strNewName = Me.SYS_NME & Format(CStr(Date), "MMM dd yyyy")
'use your file name pattern above
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description,
vbOKOnly, "Error"
    Exit Sub
End Sub


Private Sub OpenMergedDoc(strDocName As String, strSQL As String,
strMergedDocName As String)
On Error GoTo WordError

    Const strDir As String = "C:\Word"
'use your directory and folder name above
   Dim objWord As New Word.Application
   Dim objDoc As Word.Document
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strDir & strDocName)
   ' Make Word visible so that if any errors occur,

   objWord.Application.Visible = True

'replace the file path  and query name below with the path
'to your database and your query name
  objDoc.MailMerge.OpenDataSource _
        Name:="C:\Documents and
Settings\sxufba\Desktop\PMTS-Admin\PMTS-Admin.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="QUERY qryFindingReportForWord", _
        SQLStatement:="SELECT * FROM [qryFindingReportForWord]"
'notice that the SQLStatement above is not the SQL
'statement that makes up the QueryDef of the query.  It is
'the SQL statement that tells Word whether or not to use all the
records returned by the Query

   objDoc.MailMerge.Destination = wdSendToNewDocument
   objDoc.MailMerge.Execute
    'save the merged document with a descriptive name
' you can delete this next line if you don't want to save
' the merged document
' it will leave the document with the default name
'"Labels 1"  or "Letters 1"
    objWord.Application.Documents(1).SaveAs (strDir & "\" &
strMergedDocName & ".doc")
'close the merge template without saving
    objWord.Application.Documents(2).Close wdDoNotSaveChanges
    'release the variables
    Set objWord = Nothing
    Set objDoc = Nothing

Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description,
vbOKOnly, "Word Error"
        objWord.Quit
        
End Sub

0
Moe
3/20/2006 8:10:48 PM
comp.windows.apps.word-proc 948 articles. 0 followers. Post Follow

1 Replies
1588 Views

Similar Articles

[PageSpeed] 46

Hello Moe

Moe wrote:
> How to set Data source in MS Word as MS Access Databases via
> DDE(*.mdb;*.mde).
> Everytime i click the command button to merge the data, it gives me
> item not found and i know that its a problem with my datasource. when i
> click ok then it opens the word doucment prompting me which datasource
> you want to use, when i click DDE then it gives me the merged data. Is
> there anyway i can set it permanantely in my codes below.
[lots of code]

I won't even try to sift through your code (that would take _me_ ages, 
anyway, and most probably result in anything helpful :-)).

But in your case, I'd go check Cindy Meister's homepage at 
http://homepage.swissonline.ch/cindymeister/MergFram.htm .

Also, the VBA/MailMerge sections in the FAQ of http://word.mvps.org is 
certainly worth a visit, too, as might be Microsoft's MSDN articles.

HTH
Robert
-- 
  /"\  ASCII Ribbon Campaign |   MS
  \ /                        |  MVP
   X        Against HTML     |  for
  / \     in e-mail & news   | Word
0
Robert
3/21/2006 8:18:18 AM
Reply: