locked
How do I connect to SQL SERVER database using Field Codes? RRS feed

  • Question

  • I am trying to use field codes to connect to my SQL Server database, but field codes resolve to "Error! Cannot open data source."

    The field codes string I have is:
     DATABASE \c "DSN=mySystemDSN"  \s  "SELECT * FROM tableName" \l "9" \b "47" \h

    And I get the error:
     ... The cursor was not declared.... Invalid object name 'ableName'.

    Trying to connect without a DSN doesn't make sense to me; I don't understand why I need to provide a connection file (I would rather include the connection details in the field code switches instead of having another file somewhere else I need to manage).

    What is the field code configuration I need to connect to my SQL Server database?

    Thanks for you help.


    TIA

    Tuesday, July 14, 2015 4:53 PM

Answers

  • Your 'tableName' string probably should appear as [tableName].

    If you set up a normal mailmerge document with a connection to your database, the following macro should return source name, the connection string and the query string in separate message boxes:

    Sub Test()
    With ActiveDocument.MailMerge
      If .MainDocumentType <> wdNotAMergeDocument Then
        MsgBox "Mail Merge Data Source Name:" & vbCr & .DataSource.Name
        MsgBox "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
        MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
      Else
        MsgBox "Not A Merge Document"
      End If
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Tuesday, July 14, 2015 10:40 PM
    • Proposed as answer by George123345 Monday, July 27, 2015 7:58 AM
    • Marked as answer by George123345 Monday, July 27, 2015 7:58 AM
    Tuesday, July 14, 2015 10:35 PM

All replies

  • Your 'tableName' string probably should appear as [tableName].

    If you set up a normal mailmerge document with a connection to your database, the following macro should return source name, the connection string and the query string in separate message boxes:

    Sub Test()
    With ActiveDocument.MailMerge
      If .MainDocumentType <> wdNotAMergeDocument Then
        MsgBox "Mail Merge Data Source Name:" & vbCr & .DataSource.Name
        MsgBox "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
        MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
      Else
        MsgBox "Not A Merge Document"
      End If
    End With
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Tuesday, July 14, 2015 10:40 PM
    • Proposed as answer by George123345 Monday, July 27, 2015 7:58 AM
    • Marked as answer by George123345 Monday, July 27, 2015 7:58 AM
    Tuesday, July 14, 2015 10:35 PM
  • Thank you Paul.  Your code helped me to build the proper connection string. I was able to pull the correct data into the document.

    But now when I try to "Update Field", I get a dialog box to select a data source, which I don't understand why it is looking for a file (by default), isn't the connection string I provided in the field codes switch enough? Why do I need a file data source? Even when I provide an odc file, subsequent Update attempts still ask me for the file (as if it doesn't remember it; do I NEED to provide the file through the d/ switch?

    Thanks again.


    TIA

    Wednesday, July 15, 2015 10:26 PM
  • According to: https://support.office.com/en-us/article/Field-codes-Database-field-04398159-a2c9-463f-bb59-558a87badcbc?ui=en-US&rs=en-US&ad=US, you need to specify the path via the \d switch for all database queries except a query to an SQL database table using ODBC. That said, if you're using ODBC and adding the path to your existing field resolves the prompt issue you now have, I'd be inclined to do so.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by George123345 Monday, July 27, 2015 7:58 AM
    Thursday, July 16, 2015 1:28 AM