If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can't Open Data Source (Query) where criteria is defined by functi
This question relates to the Automation of Word Mail Merge from Access. I
wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#2
|
|||
|
|||
I tried this using a simple example here and managed to get it all to work,
which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#3
|
|||
|
|||
Peter - many thanks for your help thus far.
I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#4
|
|||
|
|||
Apologies, the following code
oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" should say oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]" (and there obviously should not be a digit "4" jut in front of the WHERE in the SQL clause used to set up q1). Testing q1 implies testing or myrow() Peter Jamieson "l_stocky" wrote in message ... Peter - many thanks for your help thus far. I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#5
|
|||
|
|||
I've made the necessary alteration. However, the resulting word document
scrolls through ALL the records? The myrow() function looks as follows: Function myrow() myrow = 12 End Function There are two records which should be selected based on that assignment. Instead, it is possible to scroll through all the records. Interestingly enough, the myrow() function isn't even being consulted as proved by putting a breakpoint on it. The breakpoint is never met so I guess the function is never called. There still must be something wrong with the code isn't there Peter? Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" "Peter Jamieson" wrote: Apologies, the following code oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" should say oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]" (and there obviously should not be a digit "4" jut in front of the WHERE in the SQL clause used to set up q1). Testing q1 implies testing or myrow() Peter Jamieson "l_stocky" wrote in message ... Peter - many thanks for your help thus far. I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#6
|
|||
|
|||
There still must be something wrong with the code isn't there Peter?
There could be, but if so, at present I can't see it. Unfortunately at the moment I have to retype code when replying. Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" The idea behind that bit of code is simply to set up a connection with a table, i.e. something that should always work. If you want, you can attempt the connection to q1 straight away instead, i.e. in the OpenDataSource statement. However, I doubt if that is the problem here. What code do you currently have for q1, and what happens when you run the query directly in Access? Peter Jamieson "l_stocky" wrote in message ... I've made the necessary alteration. However, the resulting word document scrolls through ALL the records? The myrow() function looks as follows: Function myrow() myrow = 12 End Function There are two records which should be selected based on that assignment. Instead, it is possible to scroll through all the records. Interestingly enough, the myrow() function isn't even being consulted as proved by putting a breakpoint on it. The breakpoint is never met so I guess the function is never called. There still must be something wrong with the code isn't there Peter? Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" "Peter Jamieson" wrote: Apologies, the following code oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" should say oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]" (and there obviously should not be a digit "4" jut in front of the WHERE in the SQL clause used to set up q1). Testing q1 implies testing or myrow() Peter Jamieson "l_stocky" wrote in message ... Peter - many thanks for your help thus far. I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#7
|
|||
|
|||
aha
....I've tried changing the SQL statement to: SQLStatement:="SELECT * FROM [q1]" and it works. The resulting Word file only has two records. These same two records are the result if you run the query directly from Access. A few strange things: 1. TWO additional instances of the database are being opened during the mailmerge process. 2. Only when I run the query directly from Access does the breakpoint show that the myrow() function is being called. My theory is that it queries the second or third instance of Access(see point 1), which since it is opened during the mail merge process doesn't have a breakpoint on it. I have more-or-less proved this by: closing the additional instances of the db, changing the value of myrow, saving the module and then running the mail merge process again. Need help to stop the extra instances of Access loading up. I've got to implement this in an office system - I can't have that happening. "Peter Jamieson" wrote: There still must be something wrong with the code isn't there Peter? There could be, but if so, at present I can't see it. Unfortunately at the moment I have to retype code when replying. Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" The idea behind that bit of code is simply to set up a connection with a table, i.e. something that should always work. If you want, you can attempt the connection to q1 straight away instead, i.e. in the OpenDataSource statement. However, I doubt if that is the problem here. What code do you currently have for q1, and what happens when you run the query directly in Access? Peter Jamieson "l_stocky" wrote in message ... I've made the necessary alteration. However, the resulting word document scrolls through ALL the records? The myrow() function looks as follows: Function myrow() myrow = 12 End Function There are two records which should be selected based on that assignment. Instead, it is possible to scroll through all the records. Interestingly enough, the myrow() function isn't even being consulted as proved by putting a breakpoint on it. The breakpoint is never met so I guess the function is never called. There still must be something wrong with the code isn't there Peter? Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" "Peter Jamieson" wrote: Apologies, the following code oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" should say oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]" (and there obviously should not be a digit "4" jut in front of the WHERE in the SQL clause used to set up q1). Testing q1 implies testing or myrow() Peter Jamieson "l_stocky" wrote in message ... Peter - many thanks for your help thus far. I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
#8
|
|||
|
|||
1. TWO additional instances of the database are being opened during the
mailmerge process. As a first shot, some guesses.... If the document you are opening from Access has been saved as a mail merge main document with an Access data source, Word will probably try to open that data source when it opens the document, and in those circumstances it may create another instance of Access - almost certainly so if the database is different from the one you have open already. If the document you are opening is actually a template and Word actually tries to create a new document based on the template, both the template and the new document have a data source attached, so Word will try to open the data source twice. However, even if either of those things is true, it does not really explain why Word would open another instance when you already have the database open. The following articles might provide a clue: http://support.microsoft.com/default...b;en-us;224697 http://support.microsoft.com/default...b;en-us;199963 Although the first article says it applies to Word 97 and that the problem was corrected in Word 2000, I have a suspicion that a similar problem with DDE may be occuring, but for slightly different reasons than are mentioned in the article. One possibility is that if the full path name of your .mdb is long or contains space characters etc., DDE may not match the database name correctly and may try to open another instance. Another possibility is that if you have a window such as a table design window open in Access (quite likely when you're developing/debugging etc.) DDE may try to open another instance. I don't think it does - I think it simply fails - but it's worth checking. Ignore the ODBC workaround in these articles - it does not work with Access queries that use user-defined functions such as myrow(). Need help to stop the extra instances of Access loading up. I've got to implement this in an office system - I can't have that happening. Yes, unfortunately the way Word and DDE work together make it very difficult to control this. Using ODBC to connect avoids opening any instances of Access, but then you have to consider the following: a. does ODBC work when when the database is already open? b. if the database is protected (either with a database password or with user-level/workgroup security) you may not be able to access it with ODBC despite the fact that it is already open (in fact I think you are more likely to be able to do this with workgroup security than a password-protected database) c. you won't be able to use user-defined functions to specify the rows you want, so you have to use a different approach. That obviously depends on what you are attempting, but some possibilities a - use Access to flag the records you need (or the ones you don't) and construct a query that relies on selecting flagged/unflagged records. Not usually a good approach in my experience. - use Access to create a table with one record for each row you want to include, and construct a query that uses that info to extract the records you need. May be OK if only one user accesses the database at a time. - use code to generate the precise query you need and either - use that to create a query in Access, then use that as the data source (never tried it) or - issue that query in your Word OpenDataSource. In both cases you would probably face limitations in terms of query length and probably complexity (e.g. there could be a limit on the number of ORs in a WHERE clause). A spcific limitation is that OpenDataSource cannot issue a query longer than either around 256 chars. or 512 chars. Best I can do for now, Peter Jamieson "l_stocky" wrote in message ... aha ...I've tried changing the SQL statement to: SQLStatement:="SELECT * FROM [q1]" and it works. The resulting Word file only has two records. These same two records are the result if you run the query directly from Access. A few strange things: 1. TWO additional instances of the database are being opened during the mailmerge process. 2. Only when I run the query directly from Access does the breakpoint show that the myrow() function is being called. My theory is that it queries the second or third instance of Access(see point 1), which since it is opened during the mail merge process doesn't have a breakpoint on it. I have more-or-less proved this by: closing the additional instances of the db, changing the value of myrow, saving the module and then running the merge process again. Need help to stop the extra instances of Access loading up. I've got to implement this in an office system - I can't have that happening. "Peter Jamieson" wrote: There still must be something wrong with the code isn't there Peter? There could be, but if so, at present I can't see it. Unfortunately at the moment I have to retype code when replying. Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" The idea behind that bit of code is simply to set up a connection with a table, i.e. something that should always work. If you want, you can attempt the connection to q1 straight away instead, i.e. in the OpenDataSource statement. However, I doubt if that is the problem here. What code do you currently have for q1, and what happens when you run the query directly in Access? Peter Jamieson "l_stocky" wrote in message ... I've made the necessary alteration. However, the resulting word document scrolls through ALL the records? The myrow() function looks as follows: Function myrow() myrow = 12 End Function There are two records which should be selected based on that assignment. Instead, it is possible to scroll through all the records. Interestingly enough, the myrow() function isn't even being consulted as proved by putting a breakpoint on it. The breakpoint is never met so I guess the function is never called. There still must be something wrong with the code isn't there Peter? Does it have anything to do with: SQLStatement:= "SELECT * FROM [t1]" "Peter Jamieson" wrote: Apologies, the following code oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" should say oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]" (and there obviously should not be a digit "4" jut in front of the WHERE in the SQL clause used to set up q1). Testing q1 implies testing or myrow() Peter Jamieson "l_stocky" wrote in message ... Peter - many thanks for your help thus far. I have managed to successfully replicate your example. However, I don't see that it tests the myrow() function, which is where the potential problems may lie. Can you extend/adjust the code to test the myrow() function, and q1 which is redundant from what I can see in the example as it stands. I appreciate you may have avoided this to test the simple functionality of the mailmerge process and to illiminate the fact that something in my configuration is causing the problem. Short of the fact that a second instance of the database is loaded the whole process is performed properly. I would really appreciate your further help on this, Lloyd "Peter Jamieson" wrote: I tried this using a simple example here and managed to get it all to work, which suggests that something specific either in your function (or perhaps the query) or your configuration is causing a problem. The main visible configuration difference was that I was using Win2K not WinXP, but I doubt if that is a factor. Does your example work when you do not try to automate Word from the database, i.e. if you just use Word VBA to set or change the data source to be the query you want? In other words, is the fact that you are automating Word from your daatabase a factor (here, it does not appear to make any difference). In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a two-column table t1 with columns k (an integer) and t (text), and a small number of rows. I have a query q1 which does SELECT t1.* FROM t1 4WHERE ([t1].[k] = myrow()) (There may be some more [] in there than I have written above). I have a Word document c:\a\atest.doc that just contains mergefields k and t. I have a module ("Module1" in the database containing a. a function myrow() which simpy sets myrow equal to one of the values in k b. a function mymerge which does dim oApp As Word.Application dim oDoc As Word.Document Set oApp = CreateObject("Word.Application") oApp.Visible = True Set oDoc = oApp.Documents.Open("c:\a\atest.doc") oApp.Visible = True oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument oDoc.MailMerge.MainDocumentType = wdFormLetters oDoc.MailMerge.OpenDataSource _ Name:= "C:\a\a.mdb", _ Connection:="TABLE t1", _ SQLStatement:= "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]" Debug.Print oDoc.MailMerge.DataSource.Connectstring Debug.Print oDoc.MailMerge.DataSource.Querystring ' followed by any Set oDoc = Nothing type cleanup required, but I leave the document open to check the results. I wonder if a similar simple example works on your system? If so, it would suggest that there is something different about your database, table or query. I do not think it can be the multi-user settings or it would still fail when you remove the function from the query. BTW, this may work in Word 2000 but automating anything that relies on the DDE connection method may fail in Word 2002 or later, and using user-defined functions does require you to use DDE. Peter Jamieson "l_stocky" wrote in message ... This question relates to the Automation of Word Mail Merge from Access. I wasn't sure whether to post it here or on the Access Newsgroup. versions: Word 2000, Access 2000, Windows XP Pro. I have a Word Document. It's Datasource is set to a predefined query in an Access DB. The query has criteria which is defined by functions set at realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria. This will return an integer which will have the query select a single row of data. This query works correctly within Access and correctly when attempting to 'Edit' the source from Word. What I am attempting to do is automate the mail merge process from Access. This is where the problem shows itself. I can open the Word document but if I try to set the datasource to the Access query I get an error message: "Word cannot open the datasource" and sometimes "Automation Error". If I replace the functions in the query's criteria with "[]" (so the criteria is manually set at runtime), Word opens the datasource successfully and I can execute the mail merge. If I set the criteria with appropriate data, again the mail merge process is successful using automation. So it seems that Word can't open the datasource when directed to do so by automation where the datasource involves function based criteria. I have put a breakpoint on the function and it is clear they are being consulted and are being assigned correctly. Is this a known problem? I see lots of aritcles about parametrised queries but none take into account setting parameters by function. (see kb209976). Is there a workaround? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
Merging MS Word document with MS Access 2002 criteria query | Doug Robbins | Mailmerge | 2 | November 4th, 2004 05:57 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
Struggling with MS Query... | Alex | General Discussion | 5 | July 6th, 2004 11:46 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |