Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
John McGhie [MVP - Word and Word Macintosh]
 
Posts: n/a
Default fields queries and utter disaster

I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However, I
think your answer will be something along the lines of "You need to use the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
, "
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

  #2   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
 
Posts: n/a
Default fields queries and utter disaster

I don't really understand what you mean by:

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.


It just doesn't make sense. You are not saying what it is that you want
done if the condition is met.

Aside from that, the only way to do what you want will almost certainly be
to use a macro that is triggered by the Save or Print commands by having
macros with the same name as those commands.

You could use the InputBox() command to have the user enter a value, but it
might be more appropriate to throw up a userform that contains a combobox or
listbox that gets populated with the values of ID2 so that the user only
selects form the values that exist.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However, I
think your answer will be something along the lines of "You need to use
the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410



  #3   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.


I had the impression that it was using Office X, but unfortunately I am much
more ignorant on Mac issues and can only experiment with Office 2004, i.e.
(a) do not know whether it is possible to connect directly using ODBC with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However, I
think your answer will be something along the lines of "You need to use
the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410



  #4   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
John McGhie [MVP - Word and Word Macintosh]
 
Posts: n/a
Default fields queries and utter disaster

My Bad... The OP did specify Office X...

In which case the following link may help:
http://www.microsoft.com/mac/otherpr...pid=usingoffic
ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml


On 29/4/06 7:38 PM, in article , "Peter
Jamieson" wrote:

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.


I had the impression that it was using Office X, but unfortunately I am much
more ignorant on Mac issues and can only experiment with Office 2004, i.e.
(a) do not know whether it is possible to connect directly using ODBC with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However, I
think your answer will be something along the lines of "You need to use
the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410




--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

  #5   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

Still no obvious way to do it here - it's possible to get the data via Excel
and the Excel VBA Help gives an example of how to do it programmatically,
but I haven't been able to get it to work so far (again, on Office 2004).
Next opportunity to look is in a couple of days' time, but I'd have thought
someone on the Mac side had had a thorough go at this and knows what's
feasible.

Peter jamieson


"John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
My Bad... The OP did specify Office X...

In which case the following link may help:
http://www.microsoft.com/mac/otherpr...pid=usingoffic
ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml


On 29/4/06 7:38 PM, in article ,
"Peter
Jamieson" wrote:

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.


I had the impression that it was using Office X, but unfortunately I am
much
more ignorant on Mac issues and can only experiment with Office 2004,
i.e.
(a) do not know whether it is possible to connect directly using ODBC
with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC
connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However,
I
think your answer will be something along the lines of "You need to use
the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410




--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410





  #6   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Paul Berkowitz
 
Posts: n/a
Default fields queries and utter disaster

I suggest pinging JE McGimpsey. If anyone knows, he will.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html
AppleScripts for Entourage: http://macscripter.net/scriptbuilders/

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.


From: Peter Jamieson
Newsgroups:
microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields
Date: Sat, 29 Apr 2006 17:54:19 +0100
Subject: fields queries and utter disaster

Still no obvious way to do it here - it's possible to get the data via Excel
and the Excel VBA Help gives an example of how to do it programmatically,
but I haven't been able to get it to work so far (again, on Office 2004).
Next opportunity to look is in a couple of days' time, but I'd have thought
someone on the Mac side had had a thorough go at this and knows what's
feasible.

Peter jamieson


"John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
My Bad... The OP did specify Office X...

In which case the following link may help:
http://www.microsoft.com/mac/otherpr...pid=usingoffic
ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml


On 29/4/06 7:38 PM, in article ,
"Peter
Jamieson" wrote:

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

I had the impression that it was using Office X, but unfortunately I am
much
more ignorant on Mac issues and can only experiment with Office 2004,
i.e.
(a) do not know whether it is possible to connect directly using ODBC
with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC
connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
I'm going to spit this off to the group that specialises in this kind of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist. However,
I
think your answer will be something along the lines of "You need to use
the
Database toolbar to insert the database into your data source as a Word
table." Once you have done that, you can then query the fields in that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined with
ASK but I've spent the better part of my day not getting any closer att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but only
once for each document .. so if its opened again it will not ask .. in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410




--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410




  #7   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
 
Posts: n/a
Default fields queries and utter disaster

Aside from the issues that have been raised by Peter, does anyone understand
what the OP means by:

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Paul Berkowitz" wrote in message
...
I suggest pinging JE McGimpsey. If anyone knows, he will.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html
AppleScripts for Entourage: http://macscripter.net/scriptbuilders/

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.


From: Peter Jamieson
Newsgroups:
microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields
Date: Sat, 29 Apr 2006 17:54:19 +0100
Subject: fields queries and utter disaster

Still no obvious way to do it here - it's possible to get the data via
Excel
and the Excel VBA Help gives an example of how to do it programmatically,
but I haven't been able to get it to work so far (again, on Office 2004).
Next opportunity to look is in a couple of days' time, but I'd have
thought
someone on the Mac side had had a thorough go at this and knows what's
feasible.

Peter jamieson


"John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
My Bad... The OP did specify Office X...

In which case the following link may help:
http://www.microsoft.com/mac/otherpr...pid=usingoffic
ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml


On 29/4/06 7:38 PM, in article ,
"Peter
Jamieson" wrote:

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind
of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are
doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

I had the impression that it was using Office X, but unfortunately I am
much
more ignorant on Mac issues and can only experiment with Office 2004,
i.e.
(a) do not know whether it is possible to connect directly using ODBC
with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is
a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC
connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote
in
message ...
I'm going to spit this off to the group that specialises in this kind
of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are
doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist.
However,
I
think your answer will be something along the lines of "You need to
use
the
Database toolbar to insert the database into your data source as a
Word
table." Once you have done that, you can then query the fields in
that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select
id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined
with
ASK but I've spent the better part of my day not getting any closer
att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but
only
once for each document .. so if its opened again it will not ask ..
in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical
Writer
Sydney, Australia +61 (0) 4 1209 1410




--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410






  #8   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
CyberTaz
 
Posts: n/a
Default fields queries and utter disaster

Hi Doug, et al -

It sounds to me like the 2 tables are related using ID1 as the common field
(Primary Key of Table1, Foreign Key of Table2). I'm not familiar with MySQL,
but in Access & others, it would seem to me that a simple query in the
database would provide the record set the user needs, matching records on
the basis of the common field. An inner join would return just the matching
records & the query should be constructed to return only the 2 required
fields of data (ID1 & ID2) for the matching records.

It seems far simpler to me to do this in the database & use the *query* as
the data source rather than trying to get Word to query the two tables.

What isn't clear (to me) is whether the OP wants to do a *merge*, with the
user choosing which of the found records to include, or whether this doc is
to be generated for one recipient at a time with the user being prompted for
an 'ID2' criteria.

Does this make sense?

HTH |:)
Bob Jones
[MVP] Office:Mac



On 4/30/06 2:23 AM, in article , "Doug
Robbins - Word MVP" wrote:

Aside from the issues that have been raised by Peter, does anyone understand
what the OP means by:

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.


  #9   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
 
Posts: n/a
Default fields queries and utter disaster

Yes Bob, that would be my understanding and I always maintain that data
manipulation is best done in the data source.

Looking now in the original newsgroup to which this was posted
(microsoft.public.mac.office.word), I see that the OP posted a couple of
follow ups to his original post, the most telling of which states:

Quote

I will settle for an even easier version ... The user is prompted for
an input in the form of a number (id2) and then the corresponding id1
will be printed as well as id2

like this

id1 - id2

Unquote

If it was Access, I would say the form into which the user eterns the id2
should be in the database and the merge should be initiated from there,
possibly making use of the information on Albert Kallal's site at:

http://www.members.shaw.ca/AlbertKal...rge/index.html



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"CyberTaz" wrote in message
.. .
Hi Doug, et al -

It sounds to me like the 2 tables are related using ID1 as the common
field
(Primary Key of Table1, Foreign Key of Table2). I'm not familiar with
MySQL,
but in Access & others, it would seem to me that a simple query in the
database would provide the record set the user needs, matching records on
the basis of the common field. An inner join would return just the
matching
records & the query should be constructed to return only the 2 required
fields of data (ID1 & ID2) for the matching records.

It seems far simpler to me to do this in the database & use the *query* as
the data source rather than trying to get Word to query the two tables.

What isn't clear (to me) is whether the OP wants to do a *merge*, with the
user choosing which of the found records to include, or whether this doc
is
to be generated for one recipient at a time with the user being prompted
for
an 'ID2' criteria.

Does this make sense?

HTH |:)
Bob Jones
[MVP] Office:Mac



On 4/30/06 2:23 AM, in article , "Doug
Robbins - Word MVP" wrote:

Aside from the issues that have been raised by Peter, does anyone
understand
what the OP means by:

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.




  #10   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

What's the best way to do that?

Peter Jamieson

"Paul Berkowitz" wrote in message
...
I suggest pinging JE McGimpsey. If anyone knows, he will.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: http://www.entourage.mvps.org/faq/index.html
AppleScripts for Entourage: http://macscripter.net/scriptbuilders/

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.


From: Peter Jamieson
Newsgroups:
microsoft.public.mac.office.word,microsoft.public. word.mailmerge.fields
Date: Sat, 29 Apr 2006 17:54:19 +0100
Subject: fields queries and utter disaster

Still no obvious way to do it here - it's possible to get the data via
Excel
and the Excel VBA Help gives an example of how to do it programmatically,
but I haven't been able to get it to work so far (again, on Office 2004).
Next opportunity to look is in a couple of days' time, but I'd have
thought
someone on the Mac side had had a thorough go at this and knows what's
feasible.

Peter jamieson


"John McGhie [MVP - Word and Word Macintosh]" wrote in
message ...
My Bad... The OP did specify Office X...

In which case the following link may help:
http://www.microsoft.com/mac/otherpr...pid=usingoffic
ex&type=howto&article=/mac/LIBRARY/feature_articles/officex/xl_ODBC.xml


On 29/4/06 7:38 PM, in article ,
"Peter
Jamieson" wrote:

Hi John etc.,

I'm going to spit this off to the group that specialises in this kind
of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are
doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

I had the impression that it was using Office X, but unfortunately I am
much
more ignorant on Mac issues and can only experiment with Office 2004,
i.e.
(a) do not know whether it is possible to connect directly using ODBC
with
Office X and
(b) what the appropriate syntax for OpenDataSource (or a { DATABASE }
field, if that is what the questioner wants) would be on Mac.

On the Windows version this is doable partly because it's possible to
specify "" as the Name in the OpenDataSource call when the ODBC DSN is
a
machine DSN (i.e. in the registry), or use a file DSN.

If someone on the Mac side knows the answer to the general ODBC
connectivity
questions maybe we could get a little bit further.

Peter Jamieson

John McGhie [MVP - Word and Word Macintosh]" wrote
in
message ...
I'm going to spit this off to the group that specialises in this kind
of
thing, however, my first question is "How did you get a connection to
mysql
from Microsoft Office in the first place?"

The people in the mailmergefields group need to know that you are
doing
this
in Macintosh OS 10.4.5 (Unix) using Microsoft Office 2004.

This is a specialist area, and I am by no means a specialist.
However,
I
think your answer will be something along the lines of "You need to
use
the
Database toolbar to insert the database into your data source as a
Word
table." Once you have done that, you can then query the fields in
that
table.

Hope this helps

On 28/4/06 8:37 PM, in article
,
"
wrote:

Hi

I'm trying to solve the following.

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select
id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.

or to put it in mysql language

SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 =
TABLE2.ID1 AND TABLE2.ID2 = ?

I can access the table, my problem is that I cannot figure out the
proper syntax for MSWords fields

As far as I can tell I should be able to do this using IF combined
with
ASK but I've spent the better part of my day not getting any closer
att
all.

I would really appreciate any help.

I might add i would like this to be don on saving or printing but
only
once for each document .. so if its opened again it will not ask ..
in
other words if the fields are filled in dont ask. But I'll settle for
just getting the first part!


--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical
Writer
Sydney, Australia +61 (0) 4 1209 1410




--

Please reply to the newsgroup to maintain the thread. Please do not
email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410








  #11   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

Does this make sense?

Partly - I agree with the analysis about what the user is trying to
retrieve, but there are at least a couple of key issues:
a. how does the user get any data at all from MySQL into Word on the Mac
platform? Constructing a query or view in the DBMS does not solve that
problem, although it might make it easier to do it without resorting to VBA
or editing a DATABASE field. At the moment the only way I can see is "get
the data from MySQL into a format Word can use as a data source, then use
that". As far as I can see at the moment, that can be done using e.g. Excel
& MS Query, but it isn't clear that it can easily be automated, e.g. there
is no equivalent of ADO to use with Word VBA on Mac, I haven't been able to
get the appropriate Mac Excel VBA "get data directly from an ODBC data
source" sample to work so far, and the MS Query documentation in this
version suggests that it isn't even possible to save a .qry/.dqy file
b. in this case the user needs to enter a value which will restrict the set
of records returned, which means that you have to create a
query/view/procedure to which you can pass a parameter, which means you have
to do slightly more than just use the query/view as a data source (again.
some of this stuff can be done with some types of data source in the Windows
version of Word, but you either have to use the old DDE connection type to
Access (and that option's not available on Mac) or successfully issue a
procedure call with a parameter which is typically not straightforward even
on Windows Word.

The bottom line is, either it's feasible to get this data directly from
Word, or it's probably going to involve a separate manual step.

FWIW, I agree with Doug about databses being the place to manipulate data,
but it kinda depends on what you mean by a "database". To me, the whole
point of a standard such as SQL is that anything can use all its features to
retireve data.I don't see the necessity to do queries as a separate step.

Just my 2c-worth

Peter Jamieson

"CyberTaz" wrote in message
.. .
Hi Doug, et al -

It sounds to me like the 2 tables are related using ID1 as the common
field
(Primary Key of Table1, Foreign Key of Table2). I'm not familiar with
MySQL,
but in Access & others, it would seem to me that a simple query in the
database would provide the record set the user needs, matching records on
the basis of the common field. An inner join would return just the
matching
records & the query should be constructed to return only the 2 required
fields of data (ID1 & ID2) for the matching records.

It seems far simpler to me to do this in the database & use the *query* as
the data source rather than trying to get Word to query the two tables.

What isn't clear (to me) is whether the OP wants to do a *merge*, with the
user choosing which of the found records to include, or whether this doc
is
to be generated for one recipient at a time with the user being prompted
for
an 'ID2' criteria.

Does this make sense?

HTH |:)
Bob Jones
[MVP] Office:Mac



On 4/30/06 2:23 AM, in article , "Doug
Robbins - Word MVP" wrote:

Aside from the issues that have been raised by Peter, does anyone
understand
what the OP means by:

I have a data source that contains two tables - table one has an id1
and some other fields and id1 is the primary key, table2 has as its
primary key id2 but also contains id1 from table1 for linking.

I want my word document to ask for a value for id2. And the select id1
and id2 if and only if table1.id1 equals table2.id1 and table2.id2
contains the value that i was asked for intially.




  #12   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks

  #13   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

In that case, you are probably using a DATABASE field, in which case you can
reveal the field code showing e.g.

{ DATABASE \c "connection info." \s "SELECT * FROM mytable" }

and substitute the query you need. To insert the "variable" part of it, you
can nest a FILLIN filed that pops up a dialog when you execute the fields,
e.g.

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

which is suitable for a numeric ID. If the ID is non-numeric you'll need to
use

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN
"Enter ID2" \o }' }

Peter Jamieson

"consiglieri" wrote in message
oups.com...
Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks



  #14   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Thanks,

That looks like it might work, or at least from a mysql viewpoint it
seems reasonable. However I have just ordered office 2004 for mac so I
shall wait untill it arrives in a couple of days and then give it a
whirl.

I'll post again as soon as I have had a chance to try it out.

Once again many thanks for the offered suggestions. The simple fact
that one actually gets a response has made me decide to abandon
OpenOffice for now and go with "Bill's stuff".

Thanks again

  #15   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

I would appreciate it if
a. you could try it on Word.X first.
b. you could let us know what your existing database field actually says.

Peter Jamieson

"consiglieri" wrote in message
oups.com...
Thanks,

That looks like it might work, or at least from a mysql viewpoint it
seems reasonable. However I have just ordered office 2004 for mac so I
shall wait untill it arrives in a couple of days and then give it a
whirl.

I'll post again as soon as I have had a chance to try it out.

Once again many thanks for the offered suggestions. The simple fact
that one actually gets a response has made me decide to abandon
OpenOffice for now and go with "Bill's stuff".

Thanks again





  #16   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Sure. not a problem. I'll give it a whirl on Word.X

  #17   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Trying it on Word.X

To clear a few things up. Initially i connected to the mysql database
using sql-query in excel after which I pulled int the information to an
excel worksheet. I then tried to create fields along the lines I
perviously explained.

As for the last attempt.

My filed contains the following

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

The only problem is I do not know what "connection info" to use.
Normally to connect to mysql i would issue mysql -u usernamen -p
password Database
I tried that but it didnt work.

Could you help me out with an example of "connection info".

  #18   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

OK, from your previous messages I had the impression that you might have
managed to get the results of a simple MySQL query directly into Word, in
which case it would almost certainly have been possible to modify the query
to get what you needed. But that appears not to be the case.

Unfortunately, the gist of the replies so far is that no-one here -
including me - actually knows whether it is possible to get data directly
into Word from an ODBC data source in either Word X or Word 2004, or what
other approaches might work if a direct connection is not possible.

The one thing I am sure of is that it is possible to get data manually from
an ODBC data source into Excel 2004 using MS Query (as you have done in
Excel X). Whether that can be automated in a useful way is another question.

Peter Jamieson



"consiglieri" wrote in message
oups.com...
Trying it on Word.X

To clear a few things up. Initially i connected to the mysql database
using sql-query in excel after which I pulled int the information to an
excel worksheet. I then tried to create fields along the lines I
perviously explained.

As for the last attempt.

My filed contains the following

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

The only problem is I do not know what "connection info" to use.
Normally to connect to mysql i would issue mysql -u usernamen -p
password Database
I tried that but it didnt work.

Could you help me out with an example of "connection info".



  #19   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

OK,
Yes looking back on my posts I realize I my answers may have been
somewhat befuddled. But I have not managed to get a direct mysql query
into word, only via excel.

Guess I will keep trying to find a reasonable solution.

  #20   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

As a workaround in Office 2004, the following general approach can be used
to get data via Excel. However, I am very unfamiliar with VBA on the Mac
platform and the Excel objects this macro needs, so if you want to pursue
this, you may need more expert help than I can give. In particular, I
originally tried to modify the QueryTable connection and sql from this
macro, but however I tried to do it, Word crashed, so I opted for the
simpler approach below. Also, on Mac I don't know of a way to prevent Word
from prompting for the Worksheet/range details whenever it tries to update
the DATABASE field.

1. Create a new workbook (let's call it "Macintosh
HD:Users:meocuments:query.xls")
2. Use the data menu to insert the results of a query in Sheet1 at cell A1.
You can delete Sheets2 and 3 if you like.
3. Save and close the workbook.

4. In Word, use the database toolbar insertion feature to insert the .xls as
a DATABASE field

5. In Word's VBA editor, use Tools|References to make a reference to the
Excel object.

Create a sub as follows:

Sub GetODBCDataViaExcel()
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim strWorkbookName as String

strWorkbookName = "Macintosh HD:Users:meocuments:query.xls"

' open the spreadsheet, refresh the table, and save the sheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strWorkbookName)
objWorkbook.Worksheets(1).QueryTables(1).Refresh
objWorkbook.Close savechanges:=True
set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

' Update all the fields in the body of the active Word document

ActiveDocument.COntent.Fields.Update

End Sub

It may also be worth looking at the following in Office.X:
Assuming you went through MS Query to set up the query, did your version of
MS Query allow you to save the query definition before returning the data to
Excel, or does it allow you to open a query definition file? On the Windows
version, (a) this is feasible and MS Query lets you save a .dqy or .qry file
which can be used by Word and (b), MS Query can be run as a standalone
program, but on the Office 2004 version of MS Query, it only appears to be
possible to start Query from Excel, and there is no facility to save/open
separate query definition files. I have tried creating one by hand just in
case the facility has been retained elsewhere but that doesn't work either.

Peter Jamieson

"consiglieri" wrote in message
oups.com...
OK,
Yes looking back on my posts I realize I my answers may have been
somewhat befuddled. But I have not managed to get a direct mysql query
into word, only via excel.

Guess I will keep trying to find a reasonable solution.





  #21   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Hi

My version of MS Query did not allow me to save my query. It seems I
can only execute a query via excel. I can actually start MS Query as a
standalone but it doesnt let me do anything. It can neither open a file
or create a query.

I find it a bit odd that neither Office.X nor Office 2004 has a simple
method of accessing a mysql database. It would appear that OpenOffice
is ahead of Microsoft in this respect. I find this to be a sad state of
affairs. However, I shall not give up. I will see if I can get your
suggestion to work.

Once again many thanks for your effort. It is appreciated very much!

  #22   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

I find it a bit odd that neither Office.X nor Office 2004 has a simple
method of accessing a mysql database.


I think it's fair to say that "Office" does, in the sense that Excel does.
I've never had the impression that Microsoft has regarded data connectivity
as a particularly important feature in Word, although they have occasionally
tried to make it easier to use, and it may be that demand for such features
on the Mac is lower than it is on the Windows version of Office (i.e. Mac
users probably do that sort of stuff some other way - I'm a realtive
newcomer to Mac so really don't know).

Peter Jamieson
"consiglieri" wrote in message
oups.com...
Hi

My version of MS Query did not allow me to save my query. It seems I
can only execute a query via excel. I can actually start MS Query as a
standalone but it doesnt let me do anything. It can neither open a file
or create a query.

I find it a bit odd that neither Office.X nor Office 2004 has a simple
method of accessing a mysql database. It would appear that OpenOffice
is ahead of Microsoft in this respect. I find this to be a sad state of
affairs. However, I shall not give up. I will see if I can get your
suggestion to work.

Once again many thanks for your effort. It is appreciated very much!



  #23   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Jim Gordon MVP
 
Posts: n/a
Default fields queries and utter disaster

Hi Peter et al,

The situation with database connectivity in Office 2004 is not good, but
it has been improving.

I tried a large number of different syntax combinations with Word
fields, but I was not able to hit the jackpot getting a connection
between a Word 2004 database field and an ODBC data source. There are
some examples for Windows Word that I tried to adapt to the Mac, but my
attempts were unsuccessful. A few combinations seemed to activate a
connection dialog, but result set was unusable gibberish.

That's not to say I have the final answer to this. There might be a
syntax that works, but so far no one I know has stumbled across it. My
conclusion is that database Word fields in 2004 are broken.

There's a somewhat logical reason for this particular feature to be
broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC
drivers. Since there were no ODBC drivers of any kind when Word 2004 was
built, there was no way to test to see if the database features worked
or not.

However, now there are ODBC drivers for the Mac. Microsoft has started
to support them in Office. So far that effort has resulted in a partial
implementation of MS Query. The 2004 implementation of MS Query is the
basic GUI from Office 2001, but it is far from complete. The current MS
Query is "read only." You can issue SELECT and other SQL statements
producing a result set of records, but you can't issue TABLE commands
and the like.

Concerning programmability of MS Query - there is none. It is not
scriptable with AppleScript. It will not respond to Visual Basic. The
ODBC Visual Basic add-in has not been updated to work with MS Query. You
can not save the queries as a file using the MS Query FILE menu.
However, you can use SQL view, copy the query and save any query as a
text file manually. You could write an AppleScript that controls the
menus to do this, but there is no script ability with MS Query itself.

On the positive side there are at least two software vendors who are
actively making ODBC drivers for Mac OS. Actual Technologies has a
reasonably priced one for MySQL
http://www.actualtechnologies.com/products.php

I offer the suggestion that you use MS Query with the Actual
Technologies driver and create queries in Excel worksheets that contain
the data you want to import into Word. You can programatically update
the queries as needed using VBA in Excel and also control Excel via VBA
in Word. So once you have created the queries you can refresh the
results as needed.

The data merge manager in Word will allow you to merge the records from
the fields as you described.

The work-around I am proposing is a two-stage process. The SQL queries
will be embedded into Excel worksheets. Word will use the Excel
worksheets as data sources.

I will ask the Microsoft Word team to take a look at this thread. And I
urge anyone who is interested in directly connecting Word to data
sources using Word Fields and the Data Merge Manager: please take a
moment to let Microsoft know your requirements by sending feedback to
the Mac Business Unit at this URL
http://www.microsoft.com/mac/default...ng=en&app=Word

It is especially important to send this feedback *now* so that the
developers know they should pay attention to this feature as they build
the next version of Word. Mac Office 12 is now under construction, but
it is a huge undertaking. Don't expect it any time soon.

As far as future programmability is concerned, please be sure to let the
MacBU know in your database connectivity needs and that you would like
to have programmability included in the feature set of tools available
for Mac Office.

Three seperate but important public announcements from Microsoft should
be considered in your future programmability plans for Mac Office.
1. AppleScript will continued to be supported in the future.
2. Visual Basic for Applications will not be supported at some
unspecified time in the future on both PC and Mac versions of Office.
3. C# will be brought to the Mac

Because of those three facts I am not hopeful the ODBC add-in for Visual
Basic is ever going to be updated for the Mac - especially since VBA
itself will go away sooner or later. It is much more likely that we will
see a C# or AppleScript solution. Just something to think about for the
future. But no programmability solution for external databases will be
made unless the Mac BU hears from customers who want it, so please be
sure to send feedback.

Thanks.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:
In that case, you are probably using a DATABASE field, in which case you can
reveal the field code showing e.g.

{ DATABASE \c "connection info." \s "SELECT * FROM mytable" }

and substitute the query you need. To insert the "variable" part of it, you
can nest a FILLIN filed that pops up a dialog when you execute the fields,
e.g.

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

which is suitable for a numeric ID. If the ID is non-numeric you'll need to
use

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN
"Enter ID2" \o }' }

Peter Jamieson

"consiglieri" wrote in message
oups.com...

Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks




  #24   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

Hi Jim,

A few combinations seemed to activate a connection dialog, but result set
was unusable gibberish.


I would be interested to know what these were if you happened to keep a
note. The only time I was able to get any such thing was when Word
determined that the source was a text file and started asking for
delimiters, and that was only when I tried to replicate the Windows approach
of using a .dsn file. However, since the questioner was originally asking
about Word.X perhaps you have found stuff that will work on that version.

That's not to say I have the final answer to this. There might be a
syntax that works, but so far no one I know has stumbled across it.


Indeed, it's the kind of thing where direct feedback from the developers is
really needed.

You can programatically update the queries as needed using VBA in Excel
and also control Excel via VBA in Word. So once you have created the
queries you can refresh the results as needed.


Yes, I posted some code for this that seems to work, but if you happen to
have code that lets you modify the connection string/query string of an
existing Excel QueryTable using automation from Word, please let me know.
Every time I tried this, Word crashed. However, I can't say I've exhausted
all the possibilities in this area.

The other thing I noticed was that the documentation in Office 2004 suggests
that it should be possible to use query files (cf. .qry/.dqy files on
Windows), even though MS Query cannot currently do so, using FINDER; as the
file type in the necessary connection string. So I tried one I'd created on
Windows but no luck.

Peter Jamieson

"Jim Gordon MVP" wrote in message
...
Hi Peter et al,

The situation with database connectivity in Office 2004 is not good, but
it has been improving.

I tried a large number of different syntax combinations with Word fields,
but I was not able to hit the jackpot getting a connection between a Word
2004 database field and an ODBC data source. There are some examples for
Windows Word that I tried to adapt to the Mac, but my attempts were
unsuccessful. A few combinations seemed to activate a connection dialog,
but result set was unusable gibberish.

That's not to say I have the final answer to this. There might be a syntax
that works, but so far no one I know has stumbled across it. My conclusion
is that database Word fields in 2004 are broken.

There's a somewhat logical reason for this particular feature to be
broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC
drivers. Since there were no ODBC drivers of any kind when Word 2004 was
built, there was no way to test to see if the database features worked or
not.

However, now there are ODBC drivers for the Mac. Microsoft has started to
support them in Office. So far that effort has resulted in a partial
implementation of MS Query. The 2004 implementation of MS Query is the
basic GUI from Office 2001, but it is far from complete. The current MS
Query is "read only." You can issue SELECT and other SQL statements
producing a result set of records, but you can't issue TABLE commands and
the like.

Concerning programmability of MS Query - there is none. It is not
scriptable with AppleScript. It will not respond to Visual Basic. The ODBC
Visual Basic add-in has not been updated to work with MS Query. You can
not save the queries as a file using the MS Query FILE menu. However, you
can use SQL view, copy the query and save any query as a text file
manually. You could write an AppleScript that controls the menus to do
this, but there is no script ability with MS Query itself.

On the positive side there are at least two software vendors who are
actively making ODBC drivers for Mac OS. Actual Technologies has a
reasonably priced one for MySQL
http://www.actualtechnologies.com/products.php

I offer the suggestion that you use MS Query with the Actual Technologies
driver and create queries in Excel worksheets that contain the data you
want to import into Word. You can programatically update the queries as
needed using VBA in Excel and also control Excel via VBA in Word. So once
you have created the queries you can refresh the results as needed.

The data merge manager in Word will allow you to merge the records from
the fields as you described.

The work-around I am proposing is a two-stage process. The SQL queries
will be embedded into Excel worksheets. Word will use the Excel worksheets
as data sources.

I will ask the Microsoft Word team to take a look at this thread. And I
urge anyone who is interested in directly connecting Word to data sources
using Word Fields and the Data Merge Manager: please take a moment to let
Microsoft know your requirements by sending feedback to the Mac Business
Unit at this URL
http://www.microsoft.com/mac/default...ng=en&app=Word

It is especially important to send this feedback *now* so that the
developers know they should pay attention to this feature as they build
the next version of Word. Mac Office 12 is now under construction, but it
is a huge undertaking. Don't expect it any time soon.

As far as future programmability is concerned, please be sure to let the
MacBU know in your database connectivity needs and that you would like to
have programmability included in the feature set of tools available for
Mac Office.

Three seperate but important public announcements from Microsoft should be
considered in your future programmability plans for Mac Office.
1. AppleScript will continued to be supported in the future.
2. Visual Basic for Applications will not be supported at some unspecified
time in the future on both PC and Mac versions of Office.
3. C# will be brought to the Mac

Because of those three facts I am not hopeful the ODBC add-in for Visual
Basic is ever going to be updated for the Mac - especially since VBA
itself will go away sooner or later. It is much more likely that we will
see a C# or AppleScript solution. Just something to think about for the
future. But no programmability solution for external databases will be
made unless the Mac BU hears from customers who want it, so please be sure
to send feedback.

Thanks.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:
In that case, you are probably using a DATABASE field, in which case you
can reveal the field code showing e.g.

{ DATABASE \c "connection info." \s "SELECT * FROM mytable" }

and substitute the query you need. To insert the "variable" part of it,
you can nest a FILLIN filed that pops up a dialog when you execute the
fields, e.g.

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

which is suitable for a numeric ID. If the ID is non-numeric you'll need
to use

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN
"Enter ID2" \o }' }

Peter Jamieson

"consiglieri" wrote in message
oups.com...

Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks




  #25   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Hi all

I've been looking into using perl and the win32:le module. From my
tests so far this seems to be a way to achieve mysql connectivity. Let
the perl script connect to the mysql server, gather the info you want
in the word documents, then open the word document with win32:le with
the necessary info inputed. I haven't had 100% success, but so far I
can initiate a word or excel document, input info. Ofcourse using perl
and applescript should be a doable. Since I know more perl then
applescript I havent tried applescript yet.

I will certainly let the mac business unit know what I think word
should be able to do with resepct to mysql connectivity.

Thomas

PS/ Should i continue to post efforts to try an connect even if they
include som perl or applescript specifics?



  #26   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
JE McGimpsey
 
Posts: n/a
Default fields queries and utter disaster

In article .com,
"consiglieri" wrote:

Ofcourse using perl
and applescript should be a doable. Since I know more perl then
applescript I havent tried applescript yet.


One could do this from within VBA:

Selection.TypeText MacScript("do shell script ""perl ~/hello.cgi""")
  #27   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Aha..didnt know VBA could execute perl scripts.Thats good news I think.

Can you recommend a good tutorial for VBA on the net ...where they have
some kind of example that helps you get going?

  #29   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Jim Gordon MVP
 
Posts: n/a
Default fields queries and utter disaster

Hi Peter,

After experimenting a bit more I discovered that the DATABASE field code
does actually work on Mac Office. I tried it in Office 2001 and Office
2004.

The following field code when refreshed will return the requested data:
{ DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" }

This means that two of our major questions have been answered. The
DATABASE field code works and SQL in Word works!

It seems the only remaining piece of the puzzle to solve is what the
syntax is for the \c field switch. I tried several likely things, but
was unable to find a syntax that works.

Someone at Microsoft is going to see if they can get us a sample or a
determination for sure about the \c field switch.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:
Hi Jim,


A few combinations seemed to activate a connection dialog, but result set
was unusable gibberish.



I would be interested to know what these were if you happened to keep a
note. The only time I was able to get any such thing was when Word
determined that the source was a text file and started asking for
delimiters, and that was only when I tried to replicate the Windows approach
of using a .dsn file. However, since the questioner was originally asking
about Word.X perhaps you have found stuff that will work on that version.


That's not to say I have the final answer to this. There might be a
syntax that works, but so far no one I know has stumbled across it.



Indeed, it's the kind of thing where direct feedback from the developers is
really needed.


You can programatically update the queries as needed using VBA in Excel
and also control Excel via VBA in Word. So once you have created the
queries you can refresh the results as needed.



Yes, I posted some code for this that seems to work, but if you happen to
have code that lets you modify the connection string/query string of an
existing Excel QueryTable using automation from Word, please let me know.
Every time I tried this, Word crashed. However, I can't say I've exhausted
all the possibilities in this area.

The other thing I noticed was that the documentation in Office 2004 suggests
that it should be possible to use query files (cf. .qry/.dqy files on
Windows), even though MS Query cannot currently do so, using FINDER; as the
file type in the necessary connection string. So I tried one I'd created on
Windows but no luck.

Peter Jamieson

"Jim Gordon MVP" wrote in message
...

Hi Peter et al,

The situation with database connectivity in Office 2004 is not good, but
it has been improving.

I tried a large number of different syntax combinations with Word fields,
but I was not able to hit the jackpot getting a connection between a Word
2004 database field and an ODBC data source. There are some examples for
Windows Word that I tried to adapt to the Mac, but my attempts were
unsuccessful. A few combinations seemed to activate a connection dialog,
but result set was unusable gibberish.

That's not to say I have the final answer to this. There might be a syntax
that works, but so far no one I know has stumbled across it. My conclusion
is that database Word fields in 2004 are broken.

There's a somewhat logical reason for this particular feature to be
broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC
drivers. Since there were no ODBC drivers of any kind when Word 2004 was
built, there was no way to test to see if the database features worked or
not.

However, now there are ODBC drivers for the Mac. Microsoft has started to
support them in Office. So far that effort has resulted in a partial
implementation of MS Query. The 2004 implementation of MS Query is the
basic GUI from Office 2001, but it is far from complete. The current MS
Query is "read only." You can issue SELECT and other SQL statements
producing a result set of records, but you can't issue TABLE commands and
the like.

Concerning programmability of MS Query - there is none. It is not
scriptable with AppleScript. It will not respond to Visual Basic. The ODBC
Visual Basic add-in has not been updated to work with MS Query. You can
not save the queries as a file using the MS Query FILE menu. However, you
can use SQL view, copy the query and save any query as a text file
manually. You could write an AppleScript that controls the menus to do
this, but there is no script ability with MS Query itself.

On the positive side there are at least two software vendors who are
actively making ODBC drivers for Mac OS. Actual Technologies has a
reasonably priced one for MySQL
http://www.actualtechnologies.com/products.php

I offer the suggestion that you use MS Query with the Actual Technologies
driver and create queries in Excel worksheets that contain the data you
want to import into Word. You can programatically update the queries as
needed using VBA in Excel and also control Excel via VBA in Word. So once
you have created the queries you can refresh the results as needed.

The data merge manager in Word will allow you to merge the records from
the fields as you described.

The work-around I am proposing is a two-stage process. The SQL queries
will be embedded into Excel worksheets. Word will use the Excel worksheets
as data sources.

I will ask the Microsoft Word team to take a look at this thread. And I
urge anyone who is interested in directly connecting Word to data sources
using Word Fields and the Data Merge Manager: please take a moment to let
Microsoft know your requirements by sending feedback to the Mac Business
Unit at this URL
http://www.microsoft.com/mac/default...ng=en&app=Word

It is especially important to send this feedback *now* so that the
developers know they should pay attention to this feature as they build
the next version of Word. Mac Office 12 is now under construction, but it
is a huge undertaking. Don't expect it any time soon.

As far as future programmability is concerned, please be sure to let the
MacBU know in your database connectivity needs and that you would like to
have programmability included in the feature set of tools available for
Mac Office.

Three seperate but important public announcements from Microsoft should be
considered in your future programmability plans for Mac Office.
1. AppleScript will continued to be supported in the future.
2. Visual Basic for Applications will not be supported at some unspecified
time in the future on both PC and Mac versions of Office.
3. C# will be brought to the Mac

Because of those three facts I am not hopeful the ODBC add-in for Visual
Basic is ever going to be updated for the Mac - especially since VBA
itself will go away sooner or later. It is much more likely that we will
see a C# or AppleScript solution. Just something to think about for the
future. But no programmability solution for external databases will be
made unless the Mac BU hears from customers who want it, so please be sure
to send feedback.

Thanks.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:

In that case, you are probably using a DATABASE field, in which case you
can reveal the field code showing e.g.

{ DATABASE \c "connection info." \s "SELECT * FROM mytable" }

and substitute the query you need. To insert the "variable" part of it,
you can nest a FILLIN filed that pops up a dialog when you execute the
fields, e.g.

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

which is suitable for a numeric ID. If the ID is non-numeric you'll need
to use

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN
"Enter ID2" \o }' }

Peter Jamieson

"consiglieri" wrote in message
egroups.com...


Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks




  #30   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default fields queries and utter disaster

Hi Jim,

Someone at Microsoft is going to see if they can get us a sample or a
determination for sure about the \c field switch.


Good.

Even in Word 2003 where ODBC connections are definitely feasible, the basic
problem is that you have to supply a data file (\d) parameter in the {
DATABASE } field, specifying a file in the Windows file system, otherwise
you get the Select Data Source dialog.

Since server-type databases such as MySQL generally want you to connect to a
"server" rather than a "file", it's not obvious what you specify in the \d
parameter to make it all work.

On the Windows version, in the similar OpenDataSource method you can get
around this by specifying the data file as "", but to make that work in
recent versions of Word you have to add a special Subtype parameter that
makes it work like Word 2000. In the DATABASE field, \d "" just doesn't
work.

Again on the Windows version, it's possible to work around this by using the
name of an ODBC file DSN in the \d parameter, and specifying \c
"FILEDSN=the_file_DSN_pathname" I do not know whether the equivalent would
work on Mac because
a. the ODBC administrator I'm using (iODBC) doesn't have a facility to
create a file DSN. Perhaps there is other ODBC administrator software that
can do it.
b. in Windows you can create one using the ODBC administrator or by hand in
Notepad. It's pretty clear what needs to go in the .dsn. On Mac, I tried
this but could not tell whether it did not work because...
- file DSNs are not supported (and perhaps even unheard of)
- I put the wrong information in
- of some additional Mac constraint, e.g. the file has to have a certain
type.

Peter Jamieson

"Jim Gordon MVP" wrote in message
...
Hi Peter,

After experimenting a bit more I discovered that the DATABASE field code
does actually work on Mac Office. I tried it in Office 2001 and Office
2004.

The following field code when refreshed will return the requested data:
{ DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" }

This means that two of our major questions have been answered. The
DATABASE field code works and SQL in Word works!

It seems the only remaining piece of the puzzle to solve is what the
syntax is for the \c field switch. I tried several likely things, but was
unable to find a syntax that works.

Someone at Microsoft is going to see if they can get us a sample or a
determination for sure about the \c field switch.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:
Hi Jim,


A few combinations seemed to activate a connection dialog, but result set
was unusable gibberish.



I would be interested to know what these were if you happened to keep a
note. The only time I was able to get any such thing was when Word
determined that the source was a text file and started asking for
delimiters, and that was only when I tried to replicate the Windows
approach of using a .dsn file. However, since the questioner was
originally asking about Word.X perhaps you have found stuff that will
work on that version.


That's not to say I have the final answer to this. There might be a
syntax that works, but so far no one I know has stumbled across it.



Indeed, it's the kind of thing where direct feedback from the developers
is really needed.


You can programatically update the queries as needed using VBA in Excel
and also control Excel via VBA in Word. So once you have created the
queries you can refresh the results as needed.



Yes, I posted some code for this that seems to work, but if you happen to
have code that lets you modify the connection string/query string of an
existing Excel QueryTable using automation from Word, please let me know.
Every time I tried this, Word crashed. However, I can't say I've
exhausted all the possibilities in this area.

The other thing I noticed was that the documentation in Office 2004
suggests that it should be possible to use query files (cf. .qry/.dqy
files on Windows), even though MS Query cannot currently do so, using
FINDER; as the file type in the necessary connection string. So I tried
one I'd created on Windows but no luck.

Peter Jamieson

"Jim Gordon MVP" wrote in message
...

Hi Peter et al,

The situation with database connectivity in Office 2004 is not good, but
it has been improving.

I tried a large number of different syntax combinations with Word fields,
but I was not able to hit the jackpot getting a connection between a Word
2004 database field and an ODBC data source. There are some examples for
Windows Word that I tried to adapt to the Mac, but my attempts were
unsuccessful. A few combinations seemed to activate a connection dialog,
but result set was unusable gibberish.

That's not to say I have the final answer to this. There might be a
syntax that works, but so far no one I know has stumbled across it. My
conclusion is that database Word fields in 2004 are broken.

There's a somewhat logical reason for this particular feature to be
broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC
drivers. Since there were no ODBC drivers of any kind when Word 2004 was
built, there was no way to test to see if the database features worked or
not.

However, now there are ODBC drivers for the Mac. Microsoft has started to
support them in Office. So far that effort has resulted in a partial
implementation of MS Query. The 2004 implementation of MS Query is the
basic GUI from Office 2001, but it is far from complete. The current MS
Query is "read only." You can issue SELECT and other SQL statements
producing a result set of records, but you can't issue TABLE commands and
the like.

Concerning programmability of MS Query - there is none. It is not
scriptable with AppleScript. It will not respond to Visual Basic. The
ODBC Visual Basic add-in has not been updated to work with MS Query. You
can not save the queries as a file using the MS Query FILE menu. However,
you can use SQL view, copy the query and save any query as a text file
manually. You could write an AppleScript that controls the menus to do
this, but there is no script ability with MS Query itself.

On the positive side there are at least two software vendors who are
actively making ODBC drivers for Mac OS. Actual Technologies has a
reasonably priced one for MySQL
http://www.actualtechnologies.com/products.php

I offer the suggestion that you use MS Query with the Actual Technologies
driver and create queries in Excel worksheets that contain the data you
want to import into Word. You can programatically update the queries as
needed using VBA in Excel and also control Excel via VBA in Word. So once
you have created the queries you can refresh the results as needed.

The data merge manager in Word will allow you to merge the records from
the fields as you described.

The work-around I am proposing is a two-stage process. The SQL queries
will be embedded into Excel worksheets. Word will use the Excel
worksheets as data sources.

I will ask the Microsoft Word team to take a look at this thread. And I
urge anyone who is interested in directly connecting Word to data sources
using Word Fields and the Data Merge Manager: please take a moment to let
Microsoft know your requirements by sending feedback to the Mac Business
Unit at this URL
http://www.microsoft.com/mac/default...ng=en&app=Word

It is especially important to send this feedback *now* so that the
developers know they should pay attention to this feature as they build
the next version of Word. Mac Office 12 is now under construction, but it
is a huge undertaking. Don't expect it any time soon.

As far as future programmability is concerned, please be sure to let the
MacBU know in your database connectivity needs and that you would like to
have programmability included in the feature set of tools available for
Mac Office.

Three seperate but important public announcements from Microsoft should
be considered in your future programmability plans for Mac Office.
1. AppleScript will continued to be supported in the future.
2. Visual Basic for Applications will not be supported at some
unspecified time in the future on both PC and Mac versions of Office.
3. C# will be brought to the Mac

Because of those three facts I am not hopeful the ODBC add-in for Visual
Basic is ever going to be updated for the Mac - especially since VBA
itself will go away sooner or later. It is much more likely that we will
see a C# or AppleScript solution. Just something to think about for the
future. But no programmability solution for external databases will be
made unless the Mac BU hears from customers who want it, so please be
sure to send feedback.

Thanks.

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs


Peter Jamieson wrote:

In that case, you are probably using a DATABASE field, in which case you
can reveal the field code showing e.g.

{ DATABASE \c "connection info." \s "SELECT * FROM mytable" }

and substitute the query you need. To insert the "variable" part of it,
you can nest a FILLIN filed that pops up a dialog when you execute the
fields, e.g.

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN
"Enter ID2" \o } }

which is suitable for a numeric ID. If the ID is non-numeric you'll need
to use

{ DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM
TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN
"Enter ID2" \o }' }

Peter Jamieson

"consiglieri" wrote in message
legroups.com...


Hi

Thanks for the answers.

To beging with - what i want to achieve is simply an autmatic query
whereby my word document receives a particular field from the database.
I input for instance a project number and from the database a klient
number for that project is selected and automagically inserted into the
word document upon save or print. This is a pretty straightforward deal
with openoffice and I would imagine that one should be able to do it
with MS Office.

How i connect to mysql

Well i simply used the database tools in word. Made added a source. But
since I have a swedish version I will need to figure out the
appropriate english terms. Its a holiday here so I will get back on
that one.

Thanks








  #31   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Hi

On Windows Word97 I can query and get results frpm mysql by using the
DSN name for my connection \c DSN=connectionname and then issuing a
query \s my query.

The only problem is returning more than one field its inserted into a
tabel and I have not been able to set a format for that table so it
spreads out on the whole page which is not what I want.

On office 2004 I can do the same by using \d and putting in the path
for my xls file .. allthough it took me a while to figure out the
format for the path.

The query on mac must contain the path

i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls"

This also results in a table ... but how to set a format?

  #32   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
John McGhie [MVP - Word and Word Macintosh]
 
Posts: n/a
Default fields queries and utter disaster

You can't set the table format before you created it. You have to go back
afterwards and format the table.

The following VBA code formats a table for you. This is very ³robust² code
that is in service at some of my customer sites, so it is built to be
unbreakable. You can simplify it a lot for what you want. For example, if
you ensure that your Normal template contains Table Heading and Table Body
styles, you can remove half the code :-)

Sub Main()
'
' Insert or Format Table Macro
' Macro recorded 14/03/00 by John McGhie
'
' On Error GoTo Error

Dim astyle As Style
Dim aDoc As String
Dim aTemplate As String
Dim x As Integer
Dim TabBodyText As Style
Dim TabHeading As Style
Dim TabBullet As Style
Dim tabNumber As Style


aTemplate = ActiveDocument.AttachedTemplate.FullName
aDoc = ActiveDocument.FullName
For Each astyle In ActiveDocument.Styles
If UCase(astyle.NameLocal) = UCase("Table Body Text") Then Set
TabBodyText = astyle
If UCase(astyle.NameLocal) = UCase("Table Bullet") Then Set TabBullet =
astyle
If UCase(astyle.NameLocal) = UCase("Table Heading") Then Set TabHeading
= astyle
If UCase(astyle.NameLocal) = UCase("Table Number") Then Set tabNumber =
astyle
Next astyle

If TabBodyText Is Nothing Then
ActiveDocument.Styles.Add Name:="Table Body Text"
Set TabBodyText = ActiveDocument.Styles("Table Body Text")
With TabBodyText
.AutomaticallyUpdate = False
.BaseStyle = "Body Text"
.NextParagraphStyle = "Table Body Text"
End With
With TabBodyText.Font
.Name = Arial
.Size = 10
.Bold = False
.Italic = False
End With
With TabBodyText.ParagraphFormat
.LeftIndent = 0
.RightIndent = 0
.SpaceBefore = 2
.SpaceBeforeAuto = False
.SpaceAfter = 2
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = False
.KeepWithNext = False
.KeepTogether = True
.PageBreakBefore = False
.FirstLineIndent = 0
.OutlineLevel = wdOutlineLevelBodyText
End With
End If

If TabHeading Is Nothing Then
ActiveDocument.Styles.Add Name:="Table Heading"
Set TabHeading = ActiveDocument.Styles("Table Heading")
With TabHeading
.AutomaticallyUpdate = False
.BaseStyle = "Table Body Text"
.NextParagraphStyle = "Table Heading"
End With
With TabHeading.Font
.Name = Arial
.Size = 10
.Bold = True
.Italic = False
End With
With TabHeading.ParagraphFormat
.LeftIndent = 0
.RightIndent = 0
.SpaceBefore = 2
.SpaceBeforeAuto = False
.SpaceAfter = 2
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = False
.KeepWithNext = True
.KeepTogether = True
.PageBreakBefore = False
.FirstLineIndent = 0
.OutlineLevel = wdOutlineLevelBodyText
End With
End If

If TabBullet Is Nothing Then
ActiveDocument.Styles.Add Name:="Table Bullet"
Set TabBullet = ActiveDocument.Styles("Table Bullet")
With TabBullet
.AutomaticallyUpdate = False
.BaseStyle = "Table Body Text"
.NextParagraphStyle = "Table Bullet"
End With
With TabBullet.Font
.Name = Arial
.Size = 10
.Bold = False
.Italic = False
End With
With TabBullet.ParagraphFormat
.LeftIndent = 22
.RightIndent = 0
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 5
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.FirstLineIndent = -17
.OutlineLevel = wdOutlineLevelBodyText
End With
TabBullet.ParagraphFormat.TabStops.ClearAll
TabBullet.ParagraphFormat.TabStops.Add _
Position:=22, Alignment:=wdAlignTabLeft, Leader:= _
wdTabLeaderSpaces
With ListGalleries(wdBulletGallery).ListTemplates(7).Li stLevels(1)
.NumberFormat = ChrW(61623)
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleBullet
.NumberPosition = 6
.Alignment = wdListLevelAlignLeft
.TextPosition = 22
.TabPosition = 22
.ResetOnHigher = 0
.StartAt = 1
.Font.Name = "Symbol"
.LinkedStyle = "Table Bullet"
End With
End If

If tabNumber Is Nothing Then
ActiveDocument.Styles.Add Name:="Table Number"
Set tabNumber = ActiveDocument.Styles("Table Number")

With tabNumber
.AutomaticallyUpdate = False
.BaseStyle = "Table Bullet"
.NextParagraphStyle = "Table Number"
End With
With tabNumber.Font
.Name = Arial
.Size = 10
.Bold = False
.Italic = False
End With
With tabNumber.ParagraphFormat
.LeftIndent = 22
.RightIndent = 0
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 2
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.FirstLineIndent = -17
.OutlineLevel = wdOutlineLevelBodyText
End With
tabNumber.ParagraphFormat.TabStops.ClearAll
tabNumber.ParagraphFormat.TabStops.Add _
Position:=22, Alignment:=wdAlignTabLeft, Leader:= _
wdTabLeaderSpaces
With ListGalleries(wdNumberGallery).ListTemplates(7).Li stLevels(1)
.NumberFormat = "%1."
.TrailingCharacter = wdTrailingTab
.NumberStyle = wdListNumberStyleArabic
.NumberPosition = 6
.Alignment = wdListLevelAlignLeft
.TextPosition = 22
.TabPosition = 22
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = "Table Number"
End With
End If

If Not Selection.Information(wdWithInTable) Then
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5,
NumColumns:= _
3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
End If

Dim aTable As Table
Set aTable = Selection.Tables(1)

aTable.Select
aTable.AutoFormat Format:=wdTableFormatGrid5, ApplyBorders:= _
True, ApplyShading:=True, ApplyFont:=False, ApplyColor:=False, _
ApplyHeadingRows:=True, ApplyLastRow:=False,
ApplyFirstColumn:=False, _
ApplyLastColumn:=False, AutoFit:=True

aTable.Select

With Selection
.Rows.AllowBreakAcrossPages = False
.Paragraphs.Reset
.Font.Reset
.Style = ActiveDocument.Styles("Table Body Text")
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth075pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth025pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderVertical)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth025pt
.Color = wdColorAutomatic
End With
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth025pt
.DefaultBorderColor = wdColorAutomatic
End With

aTable.Rows(1).Select
With Selection
.Style = ActiveDocument.Styles("Table Heading")
.Rows(1).HeadingFormat = True
With .Shading
.Texture = wdTexture10Percent
.ForegroundPatternColor = wdColorAutomatic
.BackgroundPatternColor = wdColorAutomatic
End With
End With

aTable.AutoFitBehavior (wdAutoFitWindow)

End

Error:
MsgBox "The Template has been damaged. See Tech Support.", vbCritical

End Sub



On 9/5/06 6:43 PM, in article
, "consiglieri"
wrote:

Hi

On Windows Word97 I can query and get results frpm mysql by using the
DSN name for my connection \c DSN=connectionname and then issuing a
query \s my query.

The only problem is returning more than one field its inserted into a
tabel and I have not been able to set a format for that table so it
spreads out on the whole page which is not what I want.

On office 2004 I can do the same by using \d and putting in the path
for my xls file .. allthough it took me a while to figure out the
format for the path.

The query on mac must contain the path

i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls"

This also results in a table ... but how to set a format?


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410


  #33   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

WoW!
Thanks.. I will give it a test.

  #34   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

John

Could I possibly trouble you to send a file with that script to
?

I tried copying it but I have compilation errors and I thought perhaps
they where a result of some formatting issues when doing copy and
paste? Or perhaps you allready know thats not the issue..if so please
let me know.

Once again, many thanks

  #35   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
John McGhie [MVP - Word and Word Macintosh]
 
Posts: n/a
Default fields queries and utter disaster

Did you paste directly out of the browser? That will do it... The browser
substitutes non-breaking spaces for normal spaces. These are significant
characters to VBA and they will give you an error.

To fix it, paste into TextEdit, convert it to plain text, then copy to Word
from there.

This is my copy, which will give compile errors because it calls external
constants. Change BodyFont to the string name of the font you want to use
for table body, and HeadingFont to the one for headings.

Cheers

On 9/5/06 8:46 PM, in article
, "consiglieri"
wrote:

John

Could I possibly trouble you to send a file with that script to
?

I tried copying it but I have compilation errors and I thought perhaps
they where a result of some formatting issues when doing copy and
paste? Or perhaps you allready know thats not the issue..if so please
let me know.

Once again, many thanks


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410




  #36   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
consiglieri
 
Posts: n/a
Default fields queries and utter disaster

Hi

I keep getting DefaultTableBehavior:=wdWord9TableBehavior

The named argument does not exist

Any chance you could explain that to me? I am a complete newbie to this
VBA stuff

  #37   Report Post  
Posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
John McGhie [MVP - Word and Word Macintosh]
 
Posts: n/a
Default fields queries and utter disaster

Oh, sorry about that :-)

That's code from the PC version of the macro. Remove the whole
property/value pair "DefaultTableBehavior:=wdWord9TableBehavior"

Just delete it, and Word will revert to its default (auto-size columns)
instead of the previous fixed-size columns. Hopefully it won't make a
difference for your application.

Cheers


On 10/5/06 7:21 PM, in article
, "consiglieri"
wrote:

Hi

I keep getting DefaultTableBehavior:=wdWord9TableBehavior

The named argument does not exist

Any chance you could explain that to me? I am a complete newbie to this
VBA stuff


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 12:17 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"