Word and databases

Word and databases

Post by Mohamed Sh » Thu, 14 Dec 2006 13:59:13


Hi,

I have a SQL server database, which contains the contact information about
the staff in an organization. The database is used to store and provide data
for the local intranet. The database is updated with a Microsoft Access
project, which has forms designed for data entry, and which has reports
designed for printing.

After a change in the organization, the hirarchy of the organization changed
so that a person heads the department which is responsible for data entry.
As part of the changes which he made, the department is to separately
maintain a word document, which would be printed, binded, and distributed.
This became a major inconvenience for the data entry staff, because they
have to make more and more entries. The data entry staff was already
involved in other tasks, and the additional task cannot be handled properly.

That is why, I got the idea that I should use the SQL server database, to
aid the data entry in the word document. As a start, I populated the SQL
server database with the Divehi data (Divehi is our language) by using TSQL,
from another database. It worked fine. I modified the data entry forms so as
to allow the updation and addition of Divehi data as well. This has to be
done because the word document is maintained in Divehi.

Then I searched for ways to integrate this data in the word document. One
way was mail merge. This method didn't work, because the word document is
structured so that the staff contacts are grouped by the department names.
If multiple mailmergs can be performed, it could have worked. Another way is
using WordML. WordML also would regenerate the whole document, and the
document contains data outside the staff database, which is updated by
another staff.

So I thought that both the data should be updated by that staff :D. Actually
she does that, and I use the revision toolbar to let me know about those
changes when I update the database. I got the idea that there might be a way
in MSWord which would allow this kind of updation possible... when the
portion of the word document which contains the staff data is updated, the
SQL server database is to be updated automatically. When I say updating, I
mean to say shuffling staff to another department, copying the staff
contacts to another section (as he/she might work in multiple departments),
adding a new staff, and deleting a terminated staff.

The word document was designed so that the staff data is contained a table
with 7 columns. The first column is for numbering the records which fall
under each department. The second column would list the names of the staff.
The third column lists the designations. The fourth lists the intercom
numbers. The fifth lists the direct line of the office for that staff. The
sixth lists the residence phone numbers. The seventh lists the mobile
numbers. All the seven columns are merged for each department, and that cell
would contain the department name.

Any suggestions?

Thanks in advance...
Shafiee.
 
 
 

Word and databases

Post by Cindy M » Sat, 16 Dec 2006 02:29:44

i Mohamed,

Suggestions? Well, my first would be that the data maintenance shouldn't be
done in the Word document, but in a database interface. Then populate the
document with the data from within the database. That can really work quite
well.

Word VBA is capable of communicating with databases, using ADO and OLE DB
connections. If only ODBC is available, then it would mean you need to use DAO
(the Jet engine) with ODBCDirect.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)