Insert Field Database only fails when I try to use filters

[Sherwood]

Hi, I have a word doc and an excel doc. The excel doc has several worksheets
with data that I would like to include in my word doc.

When I do the following:
1) Insert - Field - Database OR "Insert Database" from Database menu
2) Get Data - (use explorer to select excel spreadsheet
3) Select Data - (pick worksheet of interest) - choose OK (1st row contains
headers)
4) Insert Data - "All" and select "Insert data as field"

This works great.

However, if in between steps 3) and 4), I choose
a) Query Options - "field" "greater than" "0" (my query is really this simple)

and then try to insert the data,

I get error "Word was unable to open the data source"
then error "Word could not replace the selection with the specified database"

It seems like I should be able to do this, does anyone have any ideas why
filters would cause this kind of problem? Any ideas on how to get this to
work?

Using Office Professional 2003 SP2 (11.8026.8036)

Thanks,

Sherwood

 

[Peter Jamieson]

It doesn't work because when you specify conditions and filters, Word does
not always generate SQL that the OLEDB provider that fetches the data
understands.

With any luck, you should be able to see the SQL it has generated by using
Alt-F9 to look at the code in the DATABASE field. For example, one I have
here looks like this:

{ DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data
Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User
ID=Admin;Data Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My
Data Sources\\etest.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT
`K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` > '0'))" \h }

In this case, because the OLEDB provider that gets the data /probably/
thinks that "K" is a numeric column, the SQL should be

"SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` WHERE ((`K` > 0))"

But unfortunately even that is not quite enough, because the way Word uses
the OLEDB provider seems to require that you specify a "table alias" (which
is really an alternative name you can use for the table):

"SELECT `K`, `d`, `a`, `eh`, `e`, `t`, `i` FROM `Sheet1$` S1 WHERE ((`K` >
0))"

(or in this case,

"SELECT * FROM `Sheet1$` S1 WHERE K > 0"

should be enough.

You can also cut out a lot of the stuff in the "\c" parameter, e.g. ending
up with

{ DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data
Sources\\etest.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data
Sources\\etest.xls;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet
OLEDB:Engine Type=35;" \s "SELECT * FROM `Sheet1$` S1 WHERE K > 0" \h }

or even cut out the \c parameter altogether, e.g.

{ DATABASE \d "C:\\Documents and Settings\\pjj6ht\\My Documents\\My Data
Sources\\etest.xls" \s "SELECT * FROM `Sheet1$` S1 WHERE K > 0" \h }

That's in a fully up-to-date copy of Word 2003. You may find other glitches
if you're using earlier versions, and the OLEDB connection method isn't
available at all before Word 2002.

Peter Jamieson

 

[Sherwood]

Peter,

Thanks! I've finally had a chance to implement your suggestions and I was
able to get it working. Your reply was a huge help. I have some other
problems now with the import formatting of the data in my various columns,
but I'll try to do my due diligence research first before asking. If that's
not fruitful, I'll be back in the newsgroup soon with more data import
questions. If you happen to have a link to a useful information source on
data formatting while importing, I'd appreciate the link. But regardless,
thanks for your help on the import problem.

Sherwood

 

[Peter Jamieson]

Glad to hear you've been able to make progress.

There's a real problem formatting the data in tables returned by DATABASE
fields becuase you just don't get to use the formatting switches you get to
use in ordinary MERGEFIELD fields.

other than the formatting options available in the DATABASE field itself, as
far as I know you can't do alignment using tabs or spaces, you can't do
decimal alignment etc. You may also find that if you are inserting multiple
tables, Word autoformats the column widths to be different when you want
them to be the same.

IMO the only way to deal with that is to probably to post-process the
resulting document using VBA, formatting rows, columns and cells as you
want. I don't have sample code - if anyone has posted such stuff recently in
this group it's probably Doug Robbins, but that sort of code is the sort of
thing you might find in any of the VBA-related newsgroups.

Peter Jamieson