Consulting

Results 1 to 1 of 1

Thread: SQL and Word Connection - finding data to put into word

  1. #1
    VBAX Newbie
    Joined
    Mar 2010
    Posts
    1
    Location

    SQL and Word Connection - finding data to put into word

    We are upgrading from Act 6 to Act 2010, the old Act used word templates and seached through the document to find anything with [[word]] and replaced that with the data in the field... simple... however the new version uses mail merge fields, and the conversion process provided by Sage Act 2010 within the program fails stating the documents are corrupt (rubbish!!!). we have about 300 templates each with atleast 100 fields in... so not an easy task to just recreate and manually replace each field with the new mailmerge type in word...

    this is where i have had a little experiance with VBA and knowing i can connect the to sql database have written a little script to find the old fieldname within [[]] and replace it the the new mailmerge field... this is the theory...

    1. word seaches document extracts word(s) between [[]]
    2. connects to sql act database, go to field table and finds field name word found from 1.
    3. cross reference this to another column in the same sql table to find a value.
    4. cross reference the above value in another table to get the new mailmerge field name
    5. uses all the above values to create the mailmerge field and replace what word found in 1. with the new field.
    6. move on to next [[]] find

    below is the vba script so far.... but i cant get it to get the information from sql, i think it connects ok, but i dont really understand what to do next... i have searched around but i dont think im using the correct statement with the SELECT command. anyway the script is what i have writen so far but i need some expert help!!!!

    [VBA]
    Sub Convert_V6_to_V12()
    '
    ' This will convert act v6 data fields into sql mail merge fields
    ' Providing when the database was updated the field names are the same
    '
    Dim rplOLD As String
    Dim rplNEW As String
    Dim strSQLFINDFIELD As String
    Dim strSQLFINDTBL As String
    Dim strSQLTBLID As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim rplMM As String
    Dim bFound As Boolean
    Dim objWdRange As Range
    Set objWdRange = ActiveDocument.Content
    Dim oCon As ADODB.Connection
    Dim qdfCurr As DAO.QueryDef
    '
    'Connect to Act 2010 SQL Database
    '
    Set oCon = New ADODB.Connection
    oCon.ConnectionString = "DRIVER={SQL Native Client};SERVER=Localhost\ACT7;UID=ACTREADER;PWD=password123;"
    oCon.Open
    ' Find Field Name between [[ ]]
    With objWdRange.FIND
    .ClearFormatting
    .Replacement.ClearFormatting
    .MatchWildcards = True
    .Text = "(\[\[)(*)(\]\])"
    .Replacement.Text = "\2"
    bFound = .Execute(Replace:=wdReplaceOne)
    If bFound Then
    rplOLD = objWdRange.Text
    MsgBox rplOLD
    ' Connect to SQL Act Database
    Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
    If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
    Set qdfCurr = CurrentDb.CreateQueryDef("TempQuery")
    End If

    ' Use the found Act v6 Field Name to find Act v12 SQL Field Name
    strSQLFINDFIELD = "SELECT TBL_SYSCOLUMN.COLUMNNAME FROM TBL_SYSCOLUMN WHERE TBL_SYSCOLUMN.DISPLAYNAME=""" & rplOLD & """;"
    qdfCurr.SQL = strSQLFINDFIELD
    DoCmd.OpenQuery "TempQuery"

    ' Use the found Act v12 SQL Field Name to find Matching Act v12 SQL Table ID
    strSQLFINDTBL = "SELECT TBL_SYSCOLUMN.TABLEID FROM Blueforce WHERE TBL_SYSCOLUMN.COLUMNNAME=""" & strSQLFINDFIELD & """;"
    ' Use the found Act v12 SQL Table ID to find Act v12 SQL Table Name
    strSQLTBLID = "SELECT TBL_SYSTABLE.TABLENAME FROM Blueforce WHERE TBL_SYSTABLE.TABLEID=" & strSQLFINDTBL & ";"
    ' Run the SQL statements to create Variables to use later...
    DoCmd.RunSQL strSQLFINDFIELD
    DoCmd.RunSQL strSQLFINDTBL
    DoCmd.RunSQL strSQLTBLID
    ' Combine SQL statement variables to create new SQL Mail Merge Field
    rplNEW = strSQLTBLID & "." & strSQLFINDFIELD
    ' Create Word Mail Merge Field
    strSQL1 = "Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _ "
    strSQL2 = """DOCVARIABLE [ACTFIELD]&rplNEW& """
    strSQL3 = " _, PreserveFormatting:=True"

    rplMM = strSQL + strSQL2 + strSQL3
    ' Replace the found Act v6 Field with new Act v12 SQL Mail Merge Field

    objWdRange.Text = rplMM
    oCon.Close
    End If
    End With
    End Sub
    [/VBA]
    Last edited by customit; 03-19-2010 at 08:56 AM.

Posting Permissions

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