So you are giving me another learning opportunity - Javascript.
I hope not! The javascript in .odc files is completely standard and
isn't used by Word.
FWIW I posted my reply before you posted your "export to Excel"
solution, which I guess renders the whole .odc thing obsolete.
However, if you do need to know the answer to this...
I'm
afraid I do not follow what you say about naming the table on the
odc:CommandTextmytable/odc:CommandText line. I was wondering where the
SQL statement would go in. Is this the place?
I think some of my angle brackets have disappeared, which makes things a
bit harder.
I'll try to recap:
a. .odc files are used by a number of Office programs, including Word,
Excel, possibly Publisher and possibly Infopath.
b. .odc files let you define
- a type of .odc ("you want to connect to a database and be
presented with a list of available tables", or "you want to connect to a
specific table"),
- a connection string,
- a type of query string ("the query is a table name" or "the query
is a SQL statement")
c. as far as I know, Excel interprets what you put in a .odc file
"correctly" - if you specify a SQL query, Excel will execute that query,
etc. But Word does not. (FWIW it was always a bit difficult to tell what
"correct" was supposed to mean because the .odc file format was never
officially documented. AFAIK .odc has now been documented, but only the
2007 version)
d. For one thing, I don't think Word will honour anything other than a
"table name" in the .odc file. In other words, you can't put some
complex piece of SQL in there and expect Word to execute it when it
opens the .odc
e. However, Word does not even get the table name from the place it is
supposed to get it from, i.e. that odc:CommandText element. AFAICR
Word actually gets it from a meta tag (which is supposed to be a piece
of documentation, not an "instruction" that looks like:
meta name=Table content=mytable
f. If you only need to specify a table name (i.e. you want all the
rows and all the columns in that table), then you can specify the name
in the .odc, name the .odc in the Name parameter of Word's
OpenDataSource call, and that's it.
g. If however, you need to specify any kind of filtering or any SQL
that is anything more than "SELECT * FROM [tablename]", you have to
specify that SQL in the SQLStatement (and if it's a long piece of SQL,
in SQLStatement1) parameter(s) in the OpenDataSource method call.
So just for fun, let's recap:
a. Microsoft has a number of different connection methods to let you
get data from Access, none of which is a completely reliable method
b. In some cases, Microsoft forces you to use a .odc (or perhaps a
..udl, but let's not go there) even though all the necessary connection
information could be in the Connection parameter of the OpenDataSource call
c. Office 2003 .odc is undocumented (unless that has changed recently)
d. Word gets the table name it needs to connect to from the wrong
place in .odc
e. You can't use .odc facilities for specifying a query. You have to
do it in OpenDataSource.
f. Whatever you do, Word ends up embedding your security information
in every Word document that is saved after connecting to a secured
Access data source
g. If you get the slightest thing wrong, Word wil fail.
Easy to explain, eh?
OK, I could probably do better. But it's just a pity that Word's
super-duper modern way of doing "mailmerge" is something you can only
do if you can program in .NET, understand how content controls work,
etc. etc. In other words, something that ordinary users expected to be
able to do "out of the box" has been turned into something only
so-called I.T. professionals can do. I find that set of design and
technological approaches very difficult to respect.
Peter Jamieson
http://tips.pjmsn.me.uk
Murray Muspratt-Rouse wrote:
Hi Peter!
So you are giving me another learning opportunity - Javascript.
I'm
afraid I do not follow what you say about naming the table on the
odc:CommandTextmytable/odc:CommandText line. I was wondering where the
SQL statement would go in. Is this the place?
As I have told you in another reply post I have solved the problem of
linking Word documents to the data source by exporting the table that
is loaded with the mail merge data as a spreadsheet and using that to
insert the merge fields. When the VBA code executes Word inserts data
from the Access table - it obviously has the same name!
Thank you again for all your help
Murray
Peter Jamieson;394960 Wrote:
If you try to connect /manually/ with an empty .odc file, Word cannot
connect because it expects all the necessary information to be in the
..odc and it (probably) will not ask to find out the rest of the
information.
In other words, the empty .odc can only be used if you are connecting
from VBA and providing the necessary info. in there.
When I'm testing this kind of stuff I generally have a test VBA
subroutine that just contains
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
followed by the OpenDataSource call. This is simply because for some
connection types, the connection cannot succeed unless you have closed
any existing connection.
If you want to try using a "real" .odc, here's a sample taken from a
Word 2003 system (Word 2007 uses a slightly different .odc XML format
that has, finally, been documented, but will probably work with the
older ones. I am assuming you are doing a connection to
a database called c:\a\mydatabase.mdb,
with a workgroup security file called c:\a\mysecurity.mdw
using login mylogin,
password mypassword
and connecting to a table called mytable
The content after /head is not really needed - it's used by Internet
Explorer when it tries to open a .odc. As usual, various things can be
removed from the connection string. To use this .odc from Word VBA, all
you should need is
Activedocument.Mailmerge.OpenDataSource _
Name:="the pathname of the .odc"
NB, AFAICR Word/ODS wrongly takes the name of the table to open from
the
meta name=Table content=mytable line, which is supposed to be HTML
documentation, instead of the
odc:CommandTextmytable/odc:CommandText line.