DATABASE SQL staments

[ediaz]

Hello people, I have a problem whit the
functionality "Insert a database"

Ok, I have a database access whit two tables, my people
(name,Id) and my bills (bills_name,id_name)

I have mail merge in word for make letters for the names
and I want to show the bills in a table.

After of use mail merge for the names I use Insert a
database for create one table whit the bills for every
name. But I find the problem.

If I use a { DATABASE \d "D:\\bills\\bills_clients.mdb"
\c "QUERY bills " \s "SELECT * FROM [bills] WHERE
id_name='<id>' " \h }
This run, but show me a table with bills and id_name. I
don't want show id_name and for this reason I try some
types of query:

"SELECT bills_name FROM [bills] WHERE id_name='<id>' " <-
Ignore me, show me the two fields.

"SELECT bills.bills_name FROM [bills] WHERE id_name='<id>'
<-Error "word can't open data source"

Ok, I change the system and use one query(of access)
called bills_only_field.

{ DATABASE \d "D:\\bills\\bills_clients.mdb" \c "QUERY
bills_only_field " \s "SELECT * FROM [bills_only_field]
WHERE id_name='<id>' " \h }

Error again, word can't analyze the options of query SQL..

My English is bad, And the errors are in Spanish, but this
is the approximate translation..

What are I doing bad?, my be use VBA?, whit msquery
happends the same, where can I search some examples of
DATABASE?.

Thanks for all

 

[Guest]

DATABASE \d "D:\\bills\\bills_clients.mdb" \c "TABLE
bills " \s "SELECT * FROM [bills] WHERE id_name='<id>' "
\h }

In this case SELECT bills_name Runs :-D.

Where can I find some info about DATABASE field?

happy :-D

-----Original Message-----
Hello people, I have a problem whit the
functionality "Insert a database"

Ok, I have a database access whit two tables, my people
(name,Id) and my bills (bills_name,id_name)

I have mail merge in word for make letters for the names
and I want to show the bills in a table.

After of use mail merge for the names I use Insert a
database for create one table whit the bills for every
name. But I find the problem.

If I use a { DATABASE \d "D:\\bills\\bills_clients.mdb"
\c "QUERY bills " \s "SELECT * FROM [bills] WHERE
id_name='<id>' " \h }
This run, but show me a table with bills and id_name. I
don't want show id_name and for this reason I try some
types of query:

"SELECT bills_name FROM [bills] WHERE id_name='<id>' " <-
Ignore me, show me the two fields.

"SELECT bills.bills_name FROM [bills] WHERE

<-Error "word can't open data source"

Ok, I change the system and use one query(of access)
called bills_only_field.

{ DATABASE \d "D:\\bills\\bills_clients.mdb" \c "QUERY
bills_only_field " \s "SELECT * FROM [bills_only_field]
WHERE id_name='<id>' " \h }

Error again, word can't analyze the options of query SQL..

My English is bad, And the errors are in Spanish, but this
is the approximate translation..

What are I doing bad?, my be use VBA?, whit msquery
happends the same, where can I search some examples of
DATABASE?.

Thanks for all

.

 

[Peter Jamieson]

You probably already found the main piece of information - the description
of the DATABASE field in Word Help. There aren't many examples online.

As far as I know, you do not actually need the \c parameter at all in a
DATABASE field that gets data from Access.

In Word 2000 and earlier, the \c parameter told Word how to connect to the
database. The default method was DDE. If you do not specify a \c parameter,
or specify \c "TABLE tablename" or \c "QUERY queryname", Word will use DDE.
Whenever the field is executed, Word starts Access, opens the database, gets
the data, and closes Access. If you do not want that to happen, you must use
the \c parameter to specify an ODBC connection string.

In Word 2002/2003 (I am actually looking at 2003 as I write), the default
method for getting data is OLEDB. Even if you use the Insert Database
toolbar icon to insert your DATABASE field, and specify DDE as the
connection type, Word only uses DDE to get the list of tables/queries. It
seems to use OLEDB to get the data. Later, when you re-execute the DATABASE
field, Word definitely does not start Access. Again, if you do not want to
use OLEDB, you have to specify an ODBC connection string (and even in that
case, Word may connect using OLEDB).

You may find that Word takes a very long time to update DATABASE fields when
there is a lot of data to return. You may also find that data in memo fields
is truncated and numeric/date data is not formatted how you expect. Also,
you cannot retrieve images using a DATABASE field.

In Word 2000 and earlier, if you specify \c "QUERY queryname", Word will
always return all the columns in the query, whatever you put in the \s
"SELECT..." option, as you have seen. This does not actualy happen in Word
2002/2003 as far as I know. So it is probably better to leave the \c
parameter out. Then you can specify any query in the \s parameter - as long
as Word is able to execute it using DDE in Word 97/2000 and OLEDB in
2002/2003, you should be OK. There may be a length limit to the query
(probably 255 or 511 characters), and I do not think you will be able to
return more than 63 or 64 columns. When I say "any query", you do not have
to specify a table name. You can just use the SELECT to perform a
calculation or apply a function to some text, e.g. perhaps

SELECT 123
SELECT left('abcdef')
SELECT left('{ MERGEFIELD mytext }',instr{'{ MERGEFIELD mytext }' & ' ','
')-1)

If you leave out the \h parameter and the SELECT only returns a single
column, Word does not put the result in a table. which means that this can
be a useful way to do text manipulation during a mailmerge.

--
Peter Jamieson

DATABASE \d "D:\\bills\\bills_clients.mdb" \c "TABLE
bills " \s "SELECT * FROM [bills] WHERE id_name='<id>' "
\h }

In this case SELECT bills_name Runs :-D.

Where can I find some info about DATABASE field?

happy :-D

-----Original Message-----
Hello people, I have a problem whit the
functionality "Insert a database"

Ok, I have a database access whit two tables, my people
(name,Id) and my bills (bills_name,id_name)

I have mail merge in word for make letters for the names
and I want to show the bills in a table.

After of use mail merge for the names I use Insert a
database for create one table whit the bills for every
name. But I find the problem.

If I use a { DATABASE \d "D:\\bills\\bills_clients.mdb"
\c "QUERY bills " \s "SELECT * FROM [bills] WHERE
id_name='<id>' " \h }
This run, but show me a table with bills and id_name. I
don't want show id_name and for this reason I try some
types of query:

"SELECT bills_name FROM [bills] WHERE id_name='<id>' " <-
Ignore me, show me the two fields.

"SELECT bills.bills_name FROM [bills] WHERE

<-Error "word can't open data source"

Ok, I change the system and use one query(of access)
called bills_only_field.

{ DATABASE \d "D:\\bills\\bills_clients.mdb" \c "QUERY
bills_only_field " \s "SELECT * FROM [bills_only_field]
WHERE id_name='<id>' " \h }

Error again, word can't analyze the options of query SQL..

My English is bad, And the errors are in Spanish, but this
is the approximate translation..

What are I doing bad?, my be use VBA?, whit msquery
happends the same, where can I search some examples of
DATABASE?.

Thanks for all

.