Re: fields queries and utter disaster

Hi Peter:

I suspect that regrettably, in this case the Excel team got to it, the Word
one did not :-)

ODBC is hard to do on the Mac :-)

Cheers

On 23/5/06 5:57 AM, in article uCySGodfGHA.2208@xxxxxxxxxxxxxxxxxxxx, "Peter
Jamieson" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I guess if you had posted your answer to all the groups in this thread I'd
have seen it.

Here are a few questions for you:

Unfortunately the \c ODBC DATABASE field switch is not supported in Word
2004. It is kept in the application for backwards and cross platform
compatibility purposes, so Word:mac can open already existing documents
that have this switch.

Can you confirm that this means "documents that have this switch and where
Wordmac can do the right thing even if we ignore the \c switch" ?

Excel 2004, of course supports ODBC connections

Why "of course" for Excel and, err.. nothing for Word? What's the big deal?
The technological problem is surely identica?

Peter Jamieson

"Jeffrey Weston [MSFT]" <Jeffrey.Weston@xxxxxxxxxxxxx> wrote in message
news:uHQtJHHdGHA.4276@xxxxxxxxxxxxxxxxxxxxxxx
Hey, hey

Unfortunately the \c ODBC DATABASE field switch is not supported in Word
2004. It is kept in the application for backwards and cross platform
compatibility purposes, so Word:mac can open already existing documents
that have this switch. Excel 2004, of course supports ODBC connections but
Word 2004 does not.

However, I have forwarded this thread on to the others in MacBU for future
consideration.

--
Jeffrey Weston
Mac Word Test
Macintosh Business Unit
Microsoft

This posting is provided "AS IS" with no warranties, and confers no
rights.


"Jim Gordon MVP" <goldkey74@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23Bn72FwcGHA.2404@xxxxxxxxxxxxxxxxxxxxxxx
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" <goldkey74@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:edH8X7ybGHA.4672@xxxxxxxxxxxxxxxxxxxxxxx

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.aspx?pid=feedback&lang=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" <apelsinen@xxxxxxxxx> wrote in message
news:1146494350.950860.192660@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


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









--

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

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

.