How to get Word using sharepoint list?

[Søren Dalhoff]

Not sure if this is the right place to post this.

I have a sharepoint list with userinfo data eg. email, street, phonenumber
and so on. Is there a way to get Word connection to the sharepoint list as
data source for mail merge?

Product using Word 2007, Word 2003, SBS 2003

 

[Peter Jamieson]

Product using Word 2007, Word 2003, SBS 2003

Can you also use Access 2003/2007, or set up an Access database using them?

(The simplest approach is to create a linked table to a Sharepoint list
using Access, then use that as the data source for a Word MailMerge).

Peter Jamieson

 

[Søren Dalhoff]

To me it does not make sence. Sharepoint is running on a SQL database and
then having another DB pointing to it. This shounds not like the right
appoarch.

 

[Peter Jamieson]

To me it does not make sence. Sharepoint is running on a SQL database and

then having another DB pointing to it. This shounds not like the right
appoarch.

My understanding of Sharepoint is that its SQL database is for internal use
and that accessing it directly rather than going through Sharepoint
interfaces is poor practice and likely to compromise security. I have
managed to do it before, but I'm not sure I can work it all out again.

If you already have an OLEDB (or ODBC) connection string that works for
another application on the system you want to use, it may be possible to
re-use that to make an office .odc that will connect correctly.

If you don't, it's probably better to ask in a Sharepoint group if anyone
has examples of such stuff. Otherwise, I think the following connectivity
problems have to be considered and solved:
a. do you have access to the Sharepoint SQL Server database at all (I mean,
when logged in as the user who will be doing the merge)? As I understand it,
Sharepoint has access, but no-one else does.
b. if you do have access, fine. If you don't, you are probably going to
have to grant general permissions to that user, or specific permissions for
lists you want to use in MailMerge. So you need to know how to connect to
the Sharepoint SQL Server instance, log in with administrator rights, and
change stuff
c. you may also find that the Sharepoint SQL Server has been configured so
that it will not allow remote access at all, in which case you would have to
be in a position to change that setting. That may only apply to stuff based
on SQL Server 2005.
d. if you've done all that then you'll have a chance of making a
connection - in Word the simplest approach is probably to use a .odc file to
do it.

Which is why I said that connecting via an Access database is probably the
simplest approach. All the facilities are there to make connections to
Sharepoint lists - as far as I know these connections are not directly to
the SQL Server instance that Sharepoint uses, but via some WSS framework
(probably uses SOAP calls or some such). Once you have created a linked list
in Access, theonly problem Word has is that when it tries to connect it
defaults to OLEDB, which doesn't see the linked table - or any query that
does "SELECT * FROM [mylinkedtablename]". But if you make the connection
using VBA, it seems to work anyway, e.g.

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mywssdbs\wssdb.mdb", _
SQLStatement:="SELECT * FROM [mylinkedtablename]"

I even have some code here to create a linked WSS table using ADO/ADOX, but
the problem I came across is that you have to know the internal list name
for the Sharepoint list. These names are some form of GUID and whereas the
Access WSS stuff obviously knows how to go and find them, I don't (yet).

Peter Jamieson


frankly, for the amount of data you would typically have
to store /its/ data and that direct access to that database should be and is
restricted. Sharepoint users see the interfaces that Sharepoint presents,
not its valuable data store. So to make a connection to that Sharepoint
store you have to solve all the connectivity issues, which will include:
a. Identifying the correct SQL Server server and instance name
b. discovering how database security is set up (I assume it's always
integrated security these days)

In other words, if you want to wade through all the connectivity issues
associated with connecting to the correct Sharepoint server instance on the
correct server, that's up to you. I have managed to do that before, but it
was a non-trivial exercise and I'm not sure I can do it again with the
current version of SharePoint. For example
a. the user making the connection will need the necessary permissions to
access the list data. I doubt if any users in a typical configuration have
those permissions
b.

If you want to connect directly to the Sharepoint SQL Server database,
So you already know how to do it in theory - create a .odc file that points
to the Sharepoint SQL Server database and connect to that.

 

[Søren Dalhoff]

I see what you are saying now that is has been pinpointed all the aspects.
Defiantly do not want Word access SQL directly even that I have the full
control of SQL. That will make a security issue out of it especially if
others are going to use this Word doc.
Honestly I haven’t used Access at all and it sounded strange that Word
didn’t have the same features to connect to the SharePoint list. So I started
Access and less than a split second (not more than a minute or 2 ) I could
see all live data from the Sharepoint list. I didn’t believe it could be that
easy.
Now trying to figure out to “save†so Word can use it.

Thanks Peter

To me it does not make sence. Sharepoint is running on a SQL database and
then having another DB pointing to it. This shounds not like the right
appoarch.

My understanding of Sharepoint is that its SQL database is for internal use
and that accessing it directly rather than going through Sharepoint
interfaces is poor practice and likely to compromise security. I have
managed to do it before, but I'm not sure I can work it all out again.

If you already have an OLEDB (or ODBC) connection string that works for
another application on the system you want to use, it may be possible to
re-use that to make an office .odc that will connect correctly.

If you don't, it's probably better to ask in a Sharepoint group if anyone
has examples of such stuff. Otherwise, I think the following connectivity
problems have to be considered and solved:
a. do you have access to the Sharepoint SQL Server database at all (I mean,
when logged in as the user who will be doing the merge)? As I understand it,
Sharepoint has access, but no-one else does.
b. if you do have access, fine. If you don't, you are probably going to
have to grant general permissions to that user, or specific permissions for
lists you want to use in MailMerge. So you need to know how to connect to
the Sharepoint SQL Server instance, log in with administrator rights, and
change stuff
c. you may also find that the Sharepoint SQL Server has been configured so
that it will not allow remote access at all, in which case you would have to
be in a position to change that setting. That may only apply to stuff based
on SQL Server 2005.
d. if you've done all that then you'll have a chance of making a
connection - in Word the simplest approach is probably to use a .odc file to
do it.

Which is why I said that connecting via an Access database is probably the
simplest approach. All the facilities are there to make connections to
Sharepoint lists - as far as I know these connections are not directly to
the SQL Server instance that Sharepoint uses, but via some WSS framework
(probably uses SOAP calls or some such). Once you have created a linked list
in Access, theonly problem Word has is that when it tries to connect it
defaults to OLEDB, which doesn't see the linked table - or any query that
does "SELECT * FROM [mylinkedtablename]". But if you make the connection
using VBA, it seems to work anyway, e.g.

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mywssdbs\wssdb.mdb", _
SQLStatement:="SELECT * FROM [mylinkedtablename]"

I even have some code here to create a linked WSS table using ADO/ADOX, but
the problem I came across is that you have to know the internal list name
for the Sharepoint list. These names are some form of GUID and whereas the
Access WSS stuff obviously knows how to go and find them, I don't (yet).

Peter Jamieson


frankly, for the amount of data you would typically have
to store /its/ data and that direct access to that database should be and is
restricted. Sharepoint users see the interfaces that Sharepoint presents,
not its valuable data store. So to make a connection to that Sharepoint
store you have to solve all the connectivity issues, which will include:
a. Identifying the correct SQL Server server and instance name
b. discovering how database security is set up (I assume it's always
integrated security these days)

In other words, if you want to wade through all the connectivity issues
associated with connecting to the correct Sharepoint server instance on the
correct server, that's up to you. I have managed to do that before, but it
was a non-trivial exercise and I'm not sure I can do it again with the
current version of SharePoint. For example
a. the user making the connection will need the necessary permissions to
access the list data. I doubt if any users in a typical configuration have
those permissions
b.

If you want to connect directly to the Sharepoint SQL Server database,
So you already know how to do it in theory - create a .odc file that points
to the Sharepoint SQL Server database and connect to that.

To me it does not make sence. Sharepoint is running on a SQL database and
then having another DB pointing to it. This shounds not like the right
appoarch.