We help IT Professionals succeed at work.

How to create a data source to use in Microsoft Word 2003.

1,808 Views
Last Modified: 2008-01-09
I have Microsoft Word 2003 and I can create a mail merge using one table just fine.  However, I would like to know if I can setup a datasource to use in Word by combining two tables.  I can either use a stored procedure or a select statement with a join in there.  For example I would like to use the following query that pulls from two tables:
SELECT

tblRenDet.RenDUnitNo,
tblRenDet.RenDLett,
tblRenDet.RenDTitle,
tblRenDet.RenDLName,
tblRenDet.RenDFName,
tblRenDet.RenDGreet,
tblRenDet.RenDAdd1,
tblRenDet.RenDAdd2,
tblRenDet.RenDCity,
tblRenDet.RenDState,
tblRenDet.RenDZip,
tblCabAttr.CabA911Addr,
tblCabAttr.CabACity,
tblCabAttr.CabAState,
tblCabAttr.CabAZip

FROM
      tblRenDet
FULL OUTER JOIN
      tblCabAttr
ON
      tblRenDet.RenDUnitNo = tblCabAttr.CabAUnitNo


Instead of the simple Select * From tblTable


Does anybody know how I can do this?  I am using Word 2003 and SQL Server 2000.  Microsoft Excel 2003 can see more than one table.  

I tried to create a datasource like this one below.  It works with excel but does not work with word:

<html>

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=CabinsDB>
<meta name=Schema content=dbo>
<meta name=Table content=tblRenDet>
<xml id=docprops></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="OLEDB">
   <odc:ConnectionString>Provider=SQLOLEDB.1;Password=cabins;Persist Security Info=True;User ID=cabins;Data Source=RL-SQL;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RL00213;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=CabinsDB</odc:ConnectionString>
   <odc:CommandType>SQL</odc:CommandType>
   <odc:CommandText>
SELECT

tblRenDet.RenDUnitNo,
tblRenDet.RenDLett,
tblRenDet.RenDTitle,
tblRenDet.RenDLName,
tblRenDet.RenDFName,
tblRenDet.RenDGreet,
tblRenDet.RenDAdd1,
tblRenDet.RenDAdd2,
tblRenDet.RenDCity,
tblRenDet.RenDState,
tblRenDet.RenDZip,
tblCabAttr.CabA911Addr,
tblCabAttr.CabACity,
tblCabAttr.CabAState,
tblCabAttr.CabAZip

FROM
      tblRenDet
FULL OUTER JOIN
      tblCabAttr
ON
      tblRenDet.RenDUnitNo = tblCabAttr.CabAUnitNo

   </odc:CommandText>
  </odc:Connection>
 </odc:OfficeDataConnection>
</xml>
<style>
<!--
    .ODCDataSource
    {
    behavior: url(dataconn.htc);
    }
-->
</style>
 
</head>

<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
  <tr>
    <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
      &nbsp;
    </td>
     <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

      &nbsp;
    </td>
  </tr>
  <tr>
    <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>

      &nbsp;
    </td>
  </tr>
  <tr>
    <td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
      <div id='pt' style='height: 100%' class='ODCDataSource'></div>
    </td>
  </tr>
</table>

 
<script language='javascript'>

function init() {
  var sName, sDescription;
  var i, j;
 
  try {
    sName = unescape(location.href)
 
    i = sName.lastIndexOf(".")
    if (i>=0) { sName = sName.substring(1, i); }
 
    i = sName.lastIndexOf("/")
    if (i>=0) { sName = sName.substring(i+1, sName.length); }

    document.title = sName;
    document.getElementById("tdName").innerText = sName;

    sDescription = document.getElementById("docprops").innerHTML;
 
    i = sDescription.indexOf("escription>")
    if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }

    if (i>=0 && j >= 0) {
      j = sDescription.lastIndexOf("</", j);

      if (j>=0) {
          sDescription = sDescription.substring(i+11, j);
        if (sDescription != "") {
            document.getElementById("tdDesc").style.fontSize="x-small";
          document.getElementById("tdDesc").innerHTML = sDescription;
          }
        }
      }
    }
  catch(e) {

    }
  }
</script>

</body>
 
</html>


If anyone could provide me any help I would GREATLY appreciate it.  If I can not do this mail merge with word I might have to resort to crystal reports or something.  Thanks for the help!
Comment
Watch Question

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
If you have a stored Select query, you should be able to use it as a Datasource in the same way as a table.

Author

Commented:
Can you explain a little more for me please?  The datasource connection that I created was listed above. This was the datasource that was created by clicking the "new source" button inside of word. I tried to add the SQL statement inside the .odc file.  Thanks for your help
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
In word there is a Browse button you can click to get information from an existing datasource during step 3 of the mail merge process.  The Select Data Source window appears where you can create or choose a data source.  I go through this process of creating a new datasource and it creates a simple datasource for me.  

I get to the screen where it says Select Database and Table.  It will only let me select one table there and I need to be able to create a query or select a stored procedure.

I guess I don't understand how to edit this datasource once I get it created.  I don't see anywhere that I can create a simple query.  

However, I can go to the My Data Sources folder and double click the datasource I have created.  It opens up in internet explorer and displays the information exactly like I want it.  For some reason word does not like it.  I could be missing something but I appreciate your quick response!
techieblueTechnical Writer / Content Manager
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
GrahamSkan,
You were right to begin with but I just could not understand what to do.

techieblue,
The link that you provided was great and lead me in the right direction.

I decided to split the points between you two since you both gave me information that I needed in order to get the query to work.  I used excel to create a datasource, I then created an MS Query with my two tables, and I used word to open the MS Query that I created.  It works perfectly.  Thanks for the help!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.