When I change the querystring to use a different table, thefieldnames don't update

I'm using an OLEDB connection to an excel workbook. The workbook has
two spreadsheets with data. When I use the datasource.querystring
property to change the name of the spreadsheet (table) I'm using, the
fieldnames remain what they were for the first table. How can I get
the fieldnames to update to those of the second table?

I don't have any idea how the process of gathering the field names
works. I put the field names I want in the first row of the table and
it always picks up on the for table I specify in the opendatasource
sql statement.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the
querystring to "SELECT * table2 WHERE ..."? Could work. Only problem
is, I was using both SQLStatement, and SQLStatement1 in the
opendatasource statement because my queries can be long and the
querystring property can only be used up to something like 255
characters. Any ideas about that?

Thanks.

If the field names in the two tables are not the same, there is no
alternative to having to replace the merge fields in the main document with
those from the new table. As you may want to switch between the two tables,
it would probably be best to make another copy of the mail merge main
document and attache the other table to it and then modify the field names.

The only other possibility is to try and do something with field mapping,
but I doubt that it is worth the effort.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Frank A Frugone" wrote in message
...
I'm using an OLEDB connection to an excel workbook. The workbook has
two spreadsheets with data. When I use the datasource.querystring
property to change the name of the spreadsheet (table) I'm using, the
fieldnames remain what they were for the first table. How can I get
the fieldnames to update to those of the second table?

I don't have any idea how the process of gathering the field names
works. I put the field names I want in the first row of the table and
it always picks up on the for table I specify in the opendatasource
sql statement.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the
querystring to "SELECT * table2 WHERE ..."? Could work. Only problem
is, I was using both SQLStatement, and SQLStatement1 in the
opendatasource statement because my queries can be long and the
querystring property can only be used up to something like 255
characters. Any ideas about that?

Thanks.

Generally speaking it is better to use OpenDataSource to define a new
query rather than try to use Querystring to modify an existing one.
There are exceptions, but Word does not always seem to do the obvious
thing when you change a query, even one that looks as if it should work
with the existing connection.

But...
a. are you simply trying to get whatever fields (including their
names) exist in the data source and list the names and data in a
tabular/columnar format? If so, it might be simpler to use
InsertDatabase (I don't know!) or if necessary, to insert DATABASE
fields with the correct connection/query info).
b. or do you have specific column names in your merge main document?
In which case, if the column names in each worksheet are different, how
do /you/ map them onto the appropriate columns in you main document? Do
you know the column names in the data source in advance, or could they
be anything?

NB if you just need to get the field names in the data source, you can
get them from the Mailmerge.Datasource object. Also, as Doug says, you
can also use "field mapping" so that you always have the same MERGEfIELD
fields in your main document, but associate them with different fields
in the data source. However, you can map a maximum of 30 fields.

Maybe if you could spell out what you are trying to achieve overall, it
would help? The version of Word/Excel is always handy to know, too.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the

FWIW if you use SELECT * FROM table1, table2 you will get "product"
("Cartesian product") of the data in your two sheets - i.e. if you have
p rows and q columns in table1 and r rows and s columns table2, you
will get a table with q+s columns and p*r rows. I would guess that the
Jet/ACE OLE DB provider would restrict you to about 255 columns and the
Excel par to it may impose a limitation on the row count. But I'm not
sure it will get you anywhere. If you want to rename the columns in your
query, you can use synonyms, e.g.

SELECT abc AS [replacement_name1], def AS [replacement_name2]

etc.

but then you need to know the names that you are selecting in order to
replace them! (I don't think there is syntax in this case that will let
you specify the fields by position)


Peter Jamieson

http://tips.pjmsn.me.uk

Frank A Frugone wrote:
I'm using an OLEDB connection to an excel workbook. The workbook has
two spreadsheets with data. When I use the datasource.querystring
property to change the name of the spreadsheet (table) I'm using, the
fieldnames remain what they were for the first table. How can I get
the fieldnames to update to those of the second table?

I don't have any idea how the process of gathering the field names
works. I put the field names I want in the first row of the table and
it always picks up on the for table I specify in the opendatasource
sql statement.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the
querystring to "SELECT * table2 WHERE ..."? Could work. Only problem
is, I was using both SQLStatement, and SQLStatement1 in the
opendatasource statement because my queries can be long and the
querystring property can only be used up to something like 255
characters. Any ideas about that?

Thanks.

On Jul 10, 1:28*am, Peter Jamieson
wrote:
Generally speaking it is better to use OpenDataSource to define a new
query rather than try to use Querystring to modify an existing one.
There are exceptions, but Word does not always seem to do the obvious
thing when you change a query, even one that looks as if it should work
with the existing connection.

But...
* a. are you simply trying to get whatever fields (including their
names) exist in the data source and list the names and data in a
tabular/columnar format? If so, it might be simpler to use
InsertDatabase (I don't know!) or if necessary, to insert DATABASE
fields with the correct connection/query info).
* b. or do you have specific column names in your merge main document?
In which case, if the column names in each worksheet are different, how
do /you/ map them onto the appropriate columns in you main document? Do
you know the column names in the data source in advance, or could they
be anything?

NB if you just need to get the field names in the data source, you can
get them from the Mailmerge.Datasource object. Also, as Doug says, you
can also use "field mapping" so that you always have the same MERGEfIELD
fields in your main document, but associate them with different fields
in the data source. However, you can map a maximum of 30 fields.

Maybe if you could spell out what you are trying to achieve overall, it
would help? The version of Word/Excel is always handy to know, too.

* I suddenly have an idea for a workaround. *What if I use a sql
* statement in the opendatasource statement like: "SELECT * table1,
* table2", and then change the query string to "SELECT * table1
* WHERE ..." and after I gathered all the info from table1, change the

FWIW if you use SELECT * FROM table1, table2 you will get "product"
("Cartesian product") of the data in your two sheets - i.e. if you have
p rows *and q columns in table1 and r rows and s columns table2, you
will get a table with q+s columns and p*r rows. I would guess that the
Jet/ACE OLE DB provider would restrict you to about 255 columns and the
Excel par to it may impose a limitation on the row count. But I'm not
sure it will get you anywhere. If you want to rename the columns in your
query, you can use synonyms, e.g.

SELECT abc AS [replacement_name1], def AS [replacement_name2]

etc.

but then you need to know the names that you are selecting in order to
replace them! (I don't think there is syntax in this case that will let
you specify the fields by position)

Peter Jamieson

http://tips.pjmsn.me.uk

Frank A Frugone wrote:
I'm using an OLEDB connection to an excel workbook. *The workbook has
two spreadsheets with data. *When I use the datasource.querystring
property to change the name of the spreadsheet (table) I'm using, the
fieldnames remain what they were for the first table. *How can I get
the fieldnames to update to those of the second table?

I don't have any idea how the process of gathering the field names
works. *I put the field names I want in the first row of the table and
it always picks up on the for table I specify in the opendatasource
sql statement.

I suddenly have an idea for a workaround. *What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the
querystring to "SELECT * table2 WHERE ..."? Could work. *Only problem
is, I was using both SQLStatement, and SQLStatement1 in the
opendatasource statement because my queries can be long and the
querystring property can only be used up to something like 255
characters. *Any ideas about that?

Thanks.

Thank you both for your excellent informative replies. I think I
should expound on the underlying goal of the method I'm asking about.

I am using Office 2003.

The first table is a list of customers. Each column name (fieldname)
contains a product or service that we offer. For each individual
record, there is one of three possible entries: a dollar amount that
they paid for it, an "x" to indicate that they paid the standard price
for it, or it's blank to indicate that they didn't purchase it.

The second table contains the standard prices for each of the products
and services we offer. Each individual record has the name of a
customer group, (i.e. "Preferred customers") in the first column
(field) followed by the standard prices paid by those customers for
each product or service.

I have a word document template, that when double clicked, creates a
new document that first performs an opendatasource, then populates a
userform list indexed by customers and date for purchases from the
last month. The user then chooses a record, and that record is
retrieved, populating the mailmerge fields in the document.

I want to store the values from a "standard customer" record in table2
in docvariables, and then use word field logic (IF{}) to use them when
the custom record chosen from table1 has an "x" in the field for any
of the products and services we offer.

The plan I first tried to implement was to open the data source,
populate the userform list, record the record chosen into a variable,
use a querystring to change to table2, save the table2 values into
docvariables, use a querystring to change to an individual record from
table1, and unlock the fields to let them work their magic.

I had been successfully using the strategy of choosing a record using
the userform and then committing that record's values to the document,
but when I tried to use a querystring to change to table2 read values,
and change back to table1, it wouldn't recognize the fieldnames for
table2 when I was working with table2.

I suppose that I could use opendatasource instead of just changing the
querystring, but I thought that just changing the querystring would be
faster, and be less noticeable to the user. If there's a better way
to go about it, I'd be all ears.

Thanks for all help.

If you want to use mail merge for this, believe that you should be doing the
data manipulation in Excel by creating a separate sheet in the workbook that
replaces the X's with the standard price applicable for the group to which
the customer belongs. Then you would have all of the data for each record
in the one place.

Alternatively, mail merge is probably not the thing to be using and it
should all be done using VBA.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Frank A Frugone" wrote in message
...
On Jul 10, 1:28 am, Peter Jamieson
wrote:
Generally speaking it is better to use OpenDataSource to define a new
query rather than try to use Querystring to modify an existing one.
There are exceptions, but Word does not always seem to do the obvious
thing when you change a query, even one that looks as if it should work
with the existing connection.

But...
a. are you simply trying to get whatever fields (including their
names) exist in the data source and list the names and data in a
tabular/columnar format? If so, it might be simpler to use
InsertDatabase (I don't know!) or if necessary, to insert DATABASE
fields with the correct connection/query info).
b. or do you have specific column names in your merge main document?
In which case, if the column names in each worksheet are different, how
do /you/ map them onto the appropriate columns in you main document? Do
you know the column names in the data source in advance, or could they
be anything?

NB if you just need to get the field names in the data source, you can
get them from the Mailmerge.Datasource object. Also, as Doug says, you
can also use "field mapping" so that you always have the same MERGEfIELD
fields in your main document, but associate them with different fields
in the data source. However, you can map a maximum of 30 fields.

Maybe if you could spell out what you are trying to achieve overall, it
would help? The version of Word/Excel is always handy to know, too.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the

FWIW if you use SELECT * FROM table1, table2 you will get "product"
("Cartesian product") of the data in your two sheets - i.e. if you have
p rows and q columns in table1 and r rows and s columns table2, you
will get a table with q+s columns and p*r rows. I would guess that the
Jet/ACE OLE DB provider would restrict you to about 255 columns and the
Excel par to it may impose a limitation on the row count. But I'm not
sure it will get you anywhere. If you want to rename the columns in your
query, you can use synonyms, e.g.

SELECT abc AS [replacement_name1], def AS [replacement_name2]

etc.

but then you need to know the names that you are selecting in order to
replace them! (I don't think there is syntax in this case that will let
you specify the fields by position)

Peter Jamieson

http://tips.pjmsn.me.uk

Frank A Frugone wrote:
I'm using an OLEDB connection to an excel workbook. The workbook has
two spreadsheets with data. When I use the datasource.querystring
property to change the name of the spreadsheet (table) I'm using, the
fieldnames remain what they were for the first table. How can I get
the fieldnames to update to those of the second table?

I don't have any idea how the process of gathering the field names
works. I put the field names I want in the first row of the table and
it always picks up on the for table I specify in the opendatasource
sql statement.

I suddenly have an idea for a workaround. What if I use a sql
statement in the opendatasource statement like: "SELECT * table1,
table2", and then change the query string to "SELECT * table1
WHERE ..." and after I gathered all the info from table1, change the
querystring to "SELECT * table2 WHERE ..."? Could work. Only problem
is, I was using both SQLStatement, and SQLStatement1 in the
opendatasource statement because my queries can be long and the
querystring property can only be used up to something like 255
characters. Any ideas about that?

Thanks.

Thank you both for your excellent informative replies. I think I
should expound on the underlying goal of the method I'm asking about.

I am using Office 2003.

The first table is a list of customers. Each column name (fieldname)
contains a product or service that we offer. For each individual
record, there is one of three possible entries: a dollar amount that
they paid for it, an "x" to indicate that they paid the standard price
for it, or it's blank to indicate that they didn't purchase it.

The second table contains the standard prices for each of the products
and services we offer. Each individual record has the name of a
customer group, (i.e. "Preferred customers") in the first column
(field) followed by the standard prices paid by those customers for
each product or service.

I have a word document template, that when double clicked, creates a
new document that first performs an opendatasource, then populates a
userform list indexed by customers and date for purchases from the
last month. The user then chooses a record, and that record is
retrieved, populating the mailmerge fields in the document.

I want to store the values from a "standard customer" record in table2
in docvariables, and then use word field logic (IF{}) to use them when
the custom record chosen from table1 has an "x" in the field for any
of the products and services we offer.

The plan I first tried to implement was to open the data source,
populate the userform list, record the record chosen into a variable,
use a querystring to change to table2, save the table2 values into
docvariables, use a querystring to change to an individual record from
table1, and unlock the fields to let them work their magic.

I had been successfully using the strategy of choosing a record using
the userform and then committing that record's values to the document,
but when I tried to use a querystring to change to table2 read values,
and change back to table1, it wouldn't recognize the fieldnames for
table2 when I was working with table2.

I suppose that I could use opendatasource instead of just changing the
querystring, but I thought that just changing the querystring would be
faster, and be less noticeable to the user. If there's a better way
to go about it, I'd be all ears.

Thanks for all help.