How can I query a database from MS Word without an odc file?

Question 1

I want to retrieve data from a SQL Server database into MS Word. I can accomplish that by using the DATABASE field code by inserting the following into the special brackets created by pressing CTRL+F9, then executing the query as a table in MS Word by pressing F9:

DATABASE \d "C:\Users\MyUser\Documents\My Data Sources\MyDataSource.odc" \c "DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=MYDB;Trusted_Connection=Yes;" \s "SELECT TOP 1 Id FROM MyTable"

This works locally because I’ve configured MyDataSource.odc to use the SQL Server driver, but I’d like this to work on the computers of my colleagues without that dependency. Is it possible to make this feature work without specifying a Office Data Connection file?

Here’s the relevant section of my ODC file:

<xml id=docprops><o:DocumentProperties
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns="http://www.w3.org/TR/REC-html40">
  <o:Description>MyDescription</o:Description>
  <o:Name>MyName</o:Name>
 </o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
  xmlns:odc="urn:schemas-microsoft-com:office:odc"
  xmlns="http://www.w3.org/TR/REC-html40">
  <odc:Connection odc:Type="ODBC">
   <odc:ConnectionString>{SQL Server};SERVER=MYSERVER;DATABASE=MYDB;Trusted_Connection=yes;</odc:ConnectionString>
   <odc:CommandType>Table</odc:CommandType>
   <odc:AlwaysUseConnectionFile/>
  </odc:Connection>
 </odc:OfficeDataConnection>
</xml>

The DATABASE Code Field documentation tells me the \d switch is “Used for all database queries except a query to an SQL database table using ODBC”, but only provides examples of connecting to databases held within a file but not to a SQL Server Database.

The use case is we must regularly send a list from a database to a 3rd party that only receives files in MS Word format. I’d like anyone on the team to be able to open the file & update it, or even better, automate within O365 Sharepoint / Power Automate, and this DATABASE field would accomplish that if only it weren’t tied to a specific file on my computer.

Note that I can connect to the data in question using this connection string. For example, I can connect using this code in Excel’s Get & Transform (Power Query): = Odbc.Query("Driver={SQL Server};Server=MYSERVER;Database=MYDB", "SELECT TOP 1 Id FROM MyTable")

Question 2

DATABASE fields can use any datasource that can be used for a mail merge.

You can simplify and make your DATABASE field more portable by coding along the lines of:

{DATABASE \d "{FILENAME \p}/../ MyDataSource.CSV" \s "SELECT TOP 1 Id FROM MyTable" \l "9" \b "63" \h}

This will allow the field to reference a datasource that is kept in the same folder as the document containing the DATABASE field (in the above example, MyDataSource.CSV. Note that the field braces for the {FILENAME \p} field must also be created via Ctrl-F9.

The \l, \b and \h field switches can be used to control the output format. See: http://office.microsoft.com/en-us/word-help/field-codes-database-field-HP010260104.aspx. That link refers to a ‘Table AutoFormat dialog box’, which you can access via Insert|Quick Parts|Field>Database. You’ll need to set up a connection to the datasource before you can access the dialogue.

For a working example, see:
https://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67097

Leave a Comment Cancel reply