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.

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

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

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

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

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

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".

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".

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.

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.