Trying to edit data source

[Leslie Isaacs]

Hello All

I'm confused!

I have a Word2003 document that uses an Access2000 data source for a merge.
It all works OK, in that data updated in the data source comes through in
the document.
But I would like to be able to edit the access query that is being used as
the data source (by bringing in another linked table so that more fields are
available to the document).
I cannot open the data source, however.
I had thought it would simply be a query in the normal (frontend) mdb that
is used, but when I click on 'Open data Source' from the Word document I am
directed to the My Data Sources folder in My Documents, where there is a
file called "telnumbers_be staff.odc", which I presume is somehow being used
as the data source. When I try to open this I am asked to confirm the data
source and the only option available in the 'Open data source' box is 'OLE
DB Database Files', but when I click OK nothing happens.

2 questions:
1. How do I open "telnumbers_be staff.odc"?
2. The actual backend mdb that is used is "telnumbers_be.mdb", which is
where the document's data comes from: but this is in a different location to
"telnumbers_be staff.odc", and is obviously a seperate file, so how come the
document is looking at "telnumbers_be staff.odc"?

Hope someone can help: I'm a bit out of my depth!
Many thanks
Leslie Isaacs

 

[Peter Jamieson]

1. .odc files are "Office Database Connection" files that contain an
HTML-format description of the data source that your mail merge main
document is actually trying to link to. They were arguably designed for use
by Excel, are not documented by Microsoft in any detail anywhere as far as I
know, and are only part of the story when it comes to Mail merge Data
Sources in Word.

2. Since .odc files are HTML files, you can open them in Notepad to see what
they contain. In some cases you can also open them in Internet Explorer to
see what they actually "do" - you may see a list of tables, the results of
the query, or an error message. It depends.

3. In this case, someone (or something) has clearly set up a .odc file for a
specific reason (it could be because they needed to embed security
(login/password) information in it), so you should probably avoid tampering
with it. However, if you look inside, you will probably find that the
connection string specifies that the "Data source" is telnumbers_be.mdb and
the table or query specified below is for a table or query that retrieves
the necessary staff information.

4. At this point it may look as if you ought to be able to make a copy of
your .odc, modify the table/query part of it, and use the new odc as your
"data source". And that will probably work, as long as you can get your data
from an existing table or query. However, although it looks as if you might
be able to specify a more complex SQL SELECT query in there (with the
necessary JOINs etc.) I don't believe it works with Word. To do that, you
really have to use VBA to issue an OpenDataSource command and specify
a. the .mdb or .odc
b. the query

Becuaase Word basically ignores the connectioin string in an OpenDataSource
call when you connect via OLEDB, if you do have login info. or other
connection string stuff that is non-standard, you will end up having to use
a .odc.

That's not a particularly good explanation but it may help you get a bit
further. If you search groups.google.com for jamieson opendatasource you
should find loads of examples of opendatasource method calls, some of which
may even work

Peter Jamieson

 

[Leslie Isaacs]

Hello Peter

Many thanks for your comprehensive reply!
I will search groups.google.com for jamieson opendatasource as you suggest,
and see how I get on: I may post again here!

Cheers
Les