Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Different Results when merging
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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to mail merging with MSword 2003? | Mailmerge | |||
Change placement of translation results so they are in view. | Microsoft Word Help | |||
Submit Form Results in an Email | Microsoft Word Help | |||
Merging Access query into Word and Yes/No results come out T/F | Microsoft Word Help | |||
How do keep blank lines when merging a document with the data and. | Mailmerge |