I have an Access application that connects to a Sybase database via
ODBC. Within that application, I have created a complicated query that
asks the user to specify a "project name" (i.e. this is a parameterized
query). I need to use that query as the Source for a mail merge Word
document (really it's a report on the "project" but I'm using Word's
Mail Merge capabilities to populate the different pieces of
information).

The parameter looks like this: Like "*" & [Enter the Project Name or
part of it] & "*"

Although I'm using Access 2002 and Word 2002, I read in LOTS of places
that since this is a parameter query, I need to set up the data source
with a DDE connection. I've done that successfully so that when I open
the mail merge document, I'm prompted for the application's password
and then prompted for the "project name" that the query calls for.
After a while, I can click "Merge to new document" or "show merged
data" and there's the information about the "project" or "projects",
depending on how I entered the parameter. (I set up the DDE connection
by choosing the actual application .MDB file as the Data Source and
then choosing MS Access via DDE as the Data Source Type.)

HERE's THE PROBLEM: I get DIFFERENT RESULTS when I run the query
within the application and when I run the query from the mail merge.
This happens with different parameters and doesn't have any rhyme or
reason to it that I can see.

For example, I specified "zumwalt" as the parameter when running the
mail merge. I got projects that had zumwalt in the middle and zumwalt
at the beginning. But I didn't get the zumwalt project that I was
looking for (which began with zumwalt) which did appear when running
the query within the application. When I type in "san diego county" as
the parameter for the mail merge, I get 2 results that start with "san
diego county". But I don't get the other 4 that also start with "san
diego county" and that appear when I run the query directly in the
application.


I tried setting the mail merge data source as OLEDB and ODBC but
neither of those worked. I tried using the "%" wildcard but that
didn't work. I've been trying to figure this out ALL DAY so I really
hope that someone can help me.

Thanks in advance. Carol.

Not sure if this is a general problem or specific to the Access+Sybase
combination you have.

Can you do a few experiments, e.g.
a. what happens if your query is not a parameter query, but has the data
hard-coded, e.g.
- Like "*zumwalt*"
b. What if you do (a) and connect to your Access database using ODBC or
OLEDB?
c. If possible, can you try connecting using VBA and OpenDataSource and
issuing the query directly in the OpenDataSource (either with the parameter,
using DDE, or as in (a) and (b)?

Peter Jamieson
"Carol" wrote in message
oups.com...
I have an Access application that connects to a Sybase database via
ODBC. Within that application, I have created a complicated query that
asks the user to specify a "project name" (i.e. this is a parameterized
query). I need to use that query as the Source for a mail merge Word
document (really it's a report on the "project" but I'm using Word's
Mail Merge capabilities to populate the different pieces of
information).

The parameter looks like this: Like "*" & [Enter the Project Name or
part of it] & "*"

Although I'm using Access 2002 and Word 2002, I read in LOTS of places
that since this is a parameter query, I need to set up the data source
with a DDE connection. I've done that successfully so that when I open
the mail merge document, I'm prompted for the application's password
and then prompted for the "project name" that the query calls for.
After a while, I can click "Merge to new document" or "show merged
data" and there's the information about the "project" or "projects",
depending on how I entered the parameter. (I set up the DDE connection
by choosing the actual application .MDB file as the Data Source and
then choosing MS Access via DDE as the Data Source Type.)

HERE's THE PROBLEM: I get DIFFERENT RESULTS when I run the query
within the application and when I run the query from the mail merge.
This happens with different parameters and doesn't have any rhyme or
reason to it that I can see.

For example, I specified "zumwalt" as the parameter when running the
mail merge. I got projects that had zumwalt in the middle and zumwalt
at the beginning. But I didn't get the zumwalt project that I was
looking for (which began with zumwalt) which did appear when running
the query within the application. When I type in "san diego county" as
the parameter for the mail merge, I get 2 results that start with "san
diego county". But I don't get the other 4 that also start with "san
diego county" and that appear when I run the query directly in the
application.


I tried setting the mail merge data source as OLEDB and ODBC but
neither of those worked. I tried using the "%" wildcard but that
didn't work. I've been trying to figure this out ALL DAY so I really
hope that someone can help me.

Thanks in advance. Carol.

Hi, Peter --

"a." was easy so I thought I'd let you know the results of taking out
the parameter and hard coding in the criteria -- Same problem as
before. ;-(

Get 4 results when running the query directly from the application but
only 3 when running it through Word.

I'll try the others tomorrow. I'll let you know my results as soon as
I get them. I can do "b" pretty easily but will have to work a bit on
"c" although I'm pretty sure I know where to start.

Thanks for your help. Carol.

Hi again, Peter. Tried connecting to the database using ODBC and OLEDB
and here's what happened:

ODBC -
Data source = my actual CLSToolkit.mdb file, MS Access Database via
ODBC
Was prompted for login. NOTHING WORKED. (The Access app itself
doesn't have an pwd except for a read only user that is hidden from the
real users. I tried that read only user. I tried the admin pwd for
the Sybase database application that our application is drawing the
data from. I tried dba, sql. I tried my own username for the Sybase
database application. I tried a blank userid and password.)

OLE -
Data source = my actual CLS Toolkit.mdb file. OLEDB Database File
Got a screen asking for the database name, the password and the
provider. The Provider defaults to MS Jet 4.0 OLE DB provider. I
supplied the Read Only password for the Access app. Clicked on the
"Test Connection" button and got this message: "Test connection failed
because of an error in initializing. The provider cannot start the
application. The workgroup info file is missing or opened exclusively
by another user."

Instead of trying another username and password (given my lack of
success with the ODBC connection and passwords), I changed the Provider
to Sybase Adaptive Server Anywhere Provider 9.0 (which is the driver
for the ODBC connections to the Sybase database). I put in the
datasource as the name of the application file, CLSToolkit.mdb. The
location was the physical location on my computer's hard drive
(c:\program files\clstoolkit) and I used the application's Read Only
username and password.

The Test Connection was successful (i.e. I didn't get an error
message). But when I clicked okay, nothing appeared to happen in my
word document and the MailMerge toolbar was all greyed out so I
couldn't do anything with the merge.

And that's where I stand now. I don't think I'll have time today to
test option "c" and connect using VBA just because I'm not the best VBA
programmer in the world and it will take me some time to figure out
exactly how to do it.

But please let me know if the results I've posted so far indicate that
doing option "c" will still be useful. If so, then I'll make the time.

Thanks for your help, Peter.

Carol.

OK, it sounds as if your Access database is associated with an Access
workgroup database, which makes everything harder from the debugging point
of view.

However, the purpose of trying the ODBC and OLEDB connections was primarily
to see if you encountered the same problem where only some of the records
were returned. The only other easy way to test that is probably to try to
issue the same query either via Excel or via MS Query if that is installed -
but I wouldn't try too hard as you may well encounter the same login-related
problem. When I have a bit more time I may be able to give some reliable
instructions.

But even if that works, it doesn't actually solve the problem, because as
you noted, you can only use parameter queries with DDE. What I had in mind
was that at least you might be able to use a simple bit of VBA to prompt for
the parameter and issue the correct query. However, if you're going to do
that, it might well be easier to connect to the Sybase data directly using
ODBC or OLEDB and avoid Access altogether. Would that make sense in your
situation, if it could be done?

Peter Jamieson

"Carol" wrote in message
ups.com...
Hi again, Peter. Tried connecting to the database using ODBC and OLEDB
and here's what happened:

ODBC -
Data source = my actual CLSToolkit.mdb file, MS Access Database via
ODBC
Was prompted for login. NOTHING WORKED. (The Access app itself
doesn't have an pwd except for a read only user that is hidden from the
real users. I tried that read only user. I tried the admin pwd for
the Sybase database application that our application is drawing the
data from. I tried dba, sql. I tried my own username for the Sybase
database application. I tried a blank userid and password.)

OLE -
Data source = my actual CLS Toolkit.mdb file. OLEDB Database File
Got a screen asking for the database name, the password and the
provider. The Provider defaults to MS Jet 4.0 OLE DB provider. I
supplied the Read Only password for the Access app. Clicked on the
"Test Connection" button and got this message: "Test connection failed
because of an error in initializing. The provider cannot start the
application. The workgroup info file is missing or opened exclusively
by another user."

Instead of trying another username and password (given my lack of
success with the ODBC connection and passwords), I changed the Provider
to Sybase Adaptive Server Anywhere Provider 9.0 (which is the driver
for the ODBC connections to the Sybase database). I put in the
datasource as the name of the application file, CLSToolkit.mdb. The
location was the physical location on my computer's hard drive
(c:\program files\clstoolkit) and I used the application's Read Only
username and password.

The Test Connection was successful (i.e. I didn't get an error
message). But when I clicked okay, nothing appeared to happen in my
word document and the MailMerge toolbar was all greyed out so I
couldn't do anything with the merge.

And that's where I stand now. I don't think I'll have time today to
test option "c" and connect using VBA just because I'm not the best VBA
programmer in the world and it will take me some time to figure out
exactly how to do it.

But please let me know if the results I've posted so far indicate that
doing option "c" will still be useful. If so, then I'll make the time.

Thanks for your help, Peter.

Carol.

Thanks, Peter. I had a feeling I was going to end up going directly to
the data and using VBA. But I was trying to find a way around having
to patch the application (which is distributed to lots of folks) and
that's why I started from Word itself.

However, I just figured out how to pass a parameter into a query via
VBA so when I do write the patch, All I'll have to do is figure out how
to open Word and run the merge itself through VBA which might be easier
than going the other way.

Thanks for your help. Carol.

Strictly speaking, you should only need to set up the connection once,
before you distribute your code. But there are some gotchas:
a. fairly recent "security-related" changes mean that Word tends to prompt
the user whenever it opens a document connected to a data source, and/or to
fail to re-make the connection, unless the user's registry is patched. (see

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765 at:

http://support.microsoft.com?kbid=825765

). This is particularly problematic if you have no simple way to patch the
registry for every user.

b. For ODBC connections, you will either have to ensure that every user of
your document has the correct DSN ("User" or "System") on their system, or
distribute a "file DSN". User/System DSNs can be created programmatically if
necessary, but if all your users already have a standard DSN for your data
source it will help. For OLEDB connections you will have to distribute
either a Datalink file (.udl) or Office Database Connection File (.odc)
file. The .odc can be a completely empty file as all the conneciton info.
can be passed in the OpenDataSource method.

You will need to use the OpenDataSource method call to connect to the
database. If you search this group in Google groups for, e.g. Jamieson
OpenDataSource ODBC OLEDB you may find further info. on how to do that.

Peter Jamieson
"Carol" wrote in message
oups.com...
Thanks, Peter. I had a feeling I was going to end up going directly to
the data and using VBA. But I was trying to find a way around having
to patch the application (which is distributed to lots of folks) and
that's why I started from Word itself.

However, I just figured out how to pass a parameter into a query via
VBA so when I do write the patch, All I'll have to do is figure out how
to open Word and run the merge itself through VBA which might be easier
than going the other way.

Thanks for your help. Carol.