SQL command in DATABASE field

[spedicini]

Okay, I've been trying to use the "Insert Database" command in Microsoft Word
2003. Both my Word 2003 and Windows XP are completely updated and patched to
the latest builds.

I want to insert the database as a 'field'. All this works successfully, if
I hit Alt-F9, I can see the table being inserted correctly. However, if I use
the Query Options..., and attempt to do *any* filtering, then I get the error
message,

"The operation cannot be completed because of dialog or database engine
failures. Please try again later."

However, if I then edit the FIELD manually, and remove the 'WHERE' part of
the sql command, then hit F9 to update the field, it works again. In
addition, I *can* use the Query Options Filtering, if I Insert the data
straight into my document, ie, not as a field. So I know that my sql command
is not the problem. I have tried this same procedure on two different
machines with the same results. What is going on?

I've tried to get this to work with a MDB(Access Database) as well as an
Excel table, all it really contains is column headers and the fields below
them. I've also tried different database protocols by turning on the "Confirm
conversion after opening", including OLE DB Database Files, DDE and ODBC. All
of them have the same effect, I can insert the Database as a field, and it
will work fine, as long as I don't use a 'WHERE' sql command.

Here is the field code that is causing the problem:

{ DATABASE \d "mydatabase.mdb" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=mydatabase.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System
database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase
Password=\"\";Jet OLEDB:Engine Type=5;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
'CustomerID', 'Item', 'Price' FROM 'PurchaseTable' WHERE (('CustomerID' =
'1'))" \h }

If I delete
WHERE (('CustomerID' = '1'))
then it will work. I've tried all sorts of WHERE sql comparisons, and all of
them cause the same problems. I've also tried removing the extra paranthesis,
moving them around, etc., all to no avail.

Any help or suggestions at all are most appreciated.

Thanks,
Shaun

 

[Peter Jamieson]

I think you need to do three things:
a. ensure that your database path name is provided in full, e.g. instea of

"mydatabase.mdb"

use

"c:\\mydbs\\mydatabase.mdb"

b. ensure that you are using the right kind of quotes for each construct in
your query. The ones surrounding the column and table names need to be
single back quotes, i.e. ` . The ones surrounding string constants need to
be straight quotes, i.e. '

c. qualify the column name in the WHERE clause with the table name, i.e.
instead of

`CustomerID` = '1'

use

`PurchaseTable`.`CustomerID` = '1'

In fact, (a) and (b) are probably already correct in the field Word inserted
in your database. In my opinion, the statement should work without the
qualifying table name, and if it doesn't, Word should generate SQL that does
work, but unfortunately, that is not the way it is.

If you try this with an Excel data source, there may be an additional
problem to do with the data type of "CustomerID" - because ODBC and OLEDB
try to determine the data type of the column dynamically, they may decide
that it is a numeric field and require you to use

`PurchaseTable`.`CustomerID` = 1

FInally, if you need to insert a lot of conditions, it is probably easier to
introduce a short table alias and use that to qualify the field names. But
let's leave that for now...

Peter Jamieson

 

[spedicini]

Peter,

that seemed to do the trick. I didn't notice that Word was using different
types of quotes for different fields.

Thanks very much,
Shaun