Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SQL command in DATABASE field
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=\"\";Us er 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 |
#2
|
|||
|
|||
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" wrote in message ... 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=\"\";Us er 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 |
#3
|
|||
|
|||
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 "Peter Jamieson" wrote: 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" wrote in message ... 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=\"\";Us er 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert source texts into multiple files and update automatically | Microsoft Word Help | |||
How do I put a picture in a Mail Merge database field. | Mailmerge | |||
Home Phone field not getting picked up in Merge | Mailmerge | |||
User-defined table format using Database field code | Mailmerge | |||
Text Form Field Ref in Footer Won't Update on Screen | Microsoft Word Help |