Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other Qs
Hello,
I'm an app programmer looking at a Word and Access Mail Merge issue - lost and confused! I've been asked to look at an Access DB application that has a custom input form with a big table behind it. Related to that are a collection of Word templates for form letters with lots of Merge Fields in. The templates were originally created and used with Word 2000, but the current environment is Office XP/Word 2002. I've been told that there used to be a button in the toolbar called "Merge" that prompted the user to type in a case number to identify the DB row to merge, but that is missing without trace right now. When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, but I can't find that SQL in the VBA editor, or any of the document properties for the templates. Where should I be looking? I'd like to change the SQL, or delete it -- how can I do that? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? When I create a new document based on a template, there does not appear to be any data source defined, and the Merge toolbar is hidden, so I need to do a few clicks to get that on the screen -- how can I keep that on the screen by default? My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. Any clues will be gratefully recieved ... Steven |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other Qs
I suggest you use Google groups to have a good look through this newsgroup -
some of the answers to your questions are repeated many times (but others are not). be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, but I can't find that SQL in the VBA editor, or any of the document properties for the templates. Where should I be looking? I'd like to change the SQL, or delete it -- how can I do that? This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? When you create a new document based on a template that has a data source attached, Word tries to connect tot he data source for both the template and the new document. It is AFAIK trying to check the connection information, but is also trying to retrieve data to be used in the merge preview and to let the user select records using Word's Select Recipients dialog box. As far as I can tell, in Word 2000 and earlier, Word did not try to open one connection for the document and another for the attached template, but in Word 2002/2003 it generally does so, and that can cause problems if the data source is not a multi-user data source, or is opened using a method that insists on exclusive access. Frankly, you don't get much control over the exclusivity, read-only-ness of a Word connection to a data source using OpenDataSource and its connection string - in some cases, Word appears just to use whatever settings it likes in that area. Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? It sounds like you are in efect creating a new mailmerge main document with a new data source at this point. My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. If your application is fairly simple, i.e. you just need to stuff a few values into Word from a selected recod in Access, I would avoid MailMerge altogether as it's far too complicated. it's probably better to follow e.g. http://support.microsoft.com/kb/210271 (although I've just encountered someone with problems implementing that so cannot be sure it will work for you) Personally I do not have a good "pattern" you could follow, so assuming that is what you need, I suggest you post again eithe rhere or in a suitable Access conf. and ask for precisely that. Peter Jamieson "SJMac" wrote in message ... Hello, I'm an app programmer looking at a Word and Access Mail Merge issue - lost and confused! I've been asked to look at an Access DB application that has a custom input form with a big table behind it. Related to that are a collection of Word templates for form letters with lots of Merge Fields in. The templates were originally created and used with Word 2000, but the current environment is Office XP/Word 2002. I've been told that there used to be a button in the toolbar called "Merge" that prompted the user to type in a case number to identify the DB row to merge, but that is missing without trace right now. When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, but I can't find that SQL in the VBA editor, or any of the document properties for the templates. Where should I be looking? I'd like to change the SQL, or delete it -- how can I do that? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? When I create a new document based on a template, there does not appear to be any data source defined, and the Merge toolbar is hidden, so I need to do a few clicks to get that on the screen -- how can I keep that on the screen by default? My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. Any clues will be gratefully recieved ... Steven |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other
"Peter Jamieson" wrote: I suggest you use Google groups to have a good look through this newsgroup - I had been doing that, but remained confused until I started recognising and understanding the term "main merge document", and discovered that "detach data source" corresponds with the Merge toolbar button "Main Document SetupNormal Word Document". The simple stuff :-( be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? Word - but I see that you suggest it's easier from the Access side of the fence (below). When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 Yup, I'd seen that, but was happy with the prompt temporarilly so that I could tell that when there was still some embeded "automation". Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? When you create a new document based on a template that has a data source attached, Word tries to connect tot he data source for both the template and the new document. It is AFAIK trying to check the connection information, but is also trying to retrieve data to be used in the merge preview and to let the user select records using Word's Select Recipients dialog box. OK, that makes sense. As far as I can tell, in Word 2000 and earlier, Word did not try to open one connection for the document and another for the attached template, but in Word 2002/2003 it generally does so, and that can cause problems if the data source is not a multi-user data source, or is opened using a method that insists on exclusive access. Frankly, you don't get much control over the exclusivity, read-only-ness of a Word connection to a data source using OpenDataSource and its connection string - in some cases, Word appears just to use whatever settings it likes in that area. Interesting. Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? It sounds like you are in efect creating a new mailmerge main document with a new data source at this point. Yes, perhaps I did. I can't repeat this now that I know what I'm doing! My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. If your application is fairly simple, i.e. you just need to stuff a few values into Word from a selected recod in Access, I would avoid MailMerge altogether as it's far too complicated. it's probably better to follow e.g. http://support.microsoft.com/kb/210271 What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Peter Jamieson Peter, your reply has been very helpful, Thank you! Steven |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other
Regarding the warning's security purpose, I regard the effects of data
being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? This warning is a "scattergun" solution. The security problem is essentially that when Word connects to a data source, it executes code that is not part of Word, not necessarily part of Office, not necessarily supplied by MS, and not necessarily bening. The mention of SQL is a bit of a red herring, but for example if Word connects to Access via DDE and executes an Access query containing user-defined functions (i.e. defined in Access VBA) those functions may theoretically have significant side effects. But I could in theory write a text converter (in fact I have), an OLEDB provider or ODBC driver that takes the request "SELECT * FROM mytable" and actually wipes the hard drive or whatever. Now of course in an ideal world a properly configured system would have no such dangerous drivers/providers, and Access and every other database would have proper security controls so that the user+system administrator (if any) can know that they are not going to do anything malicious when they execute SQL code. In which case you could switch the warning off and forget about it. I expect most people probably do anyway as they have to get their work done and they cannot possibly tell whether the "SQL" they are about to execute is malicious or not. The unfortunate thing in this situation is that there are plenty of connection types where it is difficult to see that any harm could arise unless someone has modified Microsoft code. e.g. when Word connects to a Word data source. In other cases I do not really see why the /Word/ user should get a warning. I'm not sure that answers your question but I suppose I would want to have reasonable confidence that my system was safe (in this respect) before doing so. V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. /A/ reason to avoid using merge for many users is that a query that tries to use Access Forms!formname!fieldname syntax (which works in Access) isn't necessarily going to work - actually, I've never really investigated that area. If however you are creating a query that hits the underlying table/query, as you are proposing, you should be OK. What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Not really - I think it's using the "simple" approach. I would saythat the main advantages of using merge come into play if you are letting the users modify the layout and content of the letters/documents they are producing. In that case, there is a pre-existing UI (i.e. the various mailmerge tools) which many users understand, at least to an extent, which allows them to insert fields (and even nest them and so on), preview data, etc. However, having a separate data source in the Word document complicates everything because of a. the SQL dialog we've discussed b. multi-user considerations c. the fact that if you move the data source, you run into problems if the Word document has the old data source set up when you open it, etc. (and probably other stuff I've forgotten). Peter Jamieson "SJMac" wrote in message ... "Peter Jamieson" wrote: I suggest you use Google groups to have a good look through this newsgroup - I had been doing that, but remained confused until I started recognising and understanding the term "main merge document", and discovered that "detach data source" corresponds with the Merge toolbar button "Main Document SetupNormal Word Document". The simple stuff :-( be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? Word - but I see that you suggest it's easier from the Access side of the fence (below). When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 Yup, I'd seen that, but was happy with the prompt temporarilly so that I could tell that when there was still some embeded "automation". Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? When you create a new document based on a template that has a data source attached, Word tries to connect tot he data source for both the template and the new document. It is AFAIK trying to check the connection information, but is also trying to retrieve data to be used in the merge preview and to let the user select records using Word's Select Recipients dialog box. OK, that makes sense. As far as I can tell, in Word 2000 and earlier, Word did not try to open one connection for the document and another for the attached template, but in Word 2002/2003 it generally does so, and that can cause problems if the data source is not a multi-user data source, or is opened using a method that insists on exclusive access. Frankly, you don't get much control over the exclusivity, read-only-ness of a Word connection to a data source using OpenDataSource and its connection string - in some cases, Word appears just to use whatever settings it likes in that area. Interesting. Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? It sounds like you are in efect creating a new mailmerge main document with a new data source at this point. Yes, perhaps I did. I can't repeat this now that I know what I'm doing! My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. If your application is fairly simple, i.e. you just need to stuff a few values into Word from a selected recod in Access, I would avoid MailMerge altogether as it's far too complicated. it's probably better to follow e.g. http://support.microsoft.com/kb/210271 What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Peter Jamieson Peter, your reply has been very helpful, Thank you! Steven |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other
Peter,
Thanks again, your help has been very useful helping me to understand my problem! Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. Which actions in Access would cause a table to be locked? I assume multiple readers are OK? It looks like my user makes all changes to the DB via a series of forms, rather than editing the tables directly. I'm not (yet!) a VB Programmer, but can you give me any comments on the Macro that I recorded and then edited? I think I should at least validate that the user input is numeric, but I'm not going to bother checking that it is valid record! Sub MERGE() ' ' MERGE Macro ' Macro recorded 7/26/2006 by Steven Mackenzie ' On Error GoTo ExitMerge ' ... otherwise error messages are output ' in to a new document CaseRef = InputBox("Please type in the case reference number", "Case Ref") With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord .QueryString = .QueryString & " WHERE Ref = " & CaseRef End With .Execute Pause:=False End With Exit Sub ExitMerge: MsgBox Err.Description End Sub "Peter Jamieson" wrote: Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? This warning is a "scattergun" solution. The security problem is essentially that when Word connects to a data source, it executes code that is not part of Word, not necessarily part of Office, not necessarily supplied by MS, and not necessarily bening. The mention of SQL is a bit of a red herring, but for example if Word connects to Access via DDE and executes an Access query containing user-defined functions (i.e. defined in Access VBA) those functions may theoretically have significant side effects. But I could in theory write a text converter (in fact I have), an OLEDB provider or ODBC driver that takes the request "SELECT * FROM mytable" and actually wipes the hard drive or whatever. Now of course in an ideal world a properly configured system would have no such dangerous drivers/providers, and Access and every other database would have proper security controls so that the user+system administrator (if any) can know that they are not going to do anything malicious when they execute SQL code. In which case you could switch the warning off and forget about it. I expect most people probably do anyway as they have to get their work done and they cannot possibly tell whether the "SQL" they are about to execute is malicious or not. The unfortunate thing in this situation is that there are plenty of connection types where it is difficult to see that any harm could arise unless someone has modified Microsoft code. e.g. when Word connects to a Word data source. In other cases I do not really see why the /Word/ user should get a warning. I'm not sure that answers your question but I suppose I would want to have reasonable confidence that my system was safe (in this respect) before doing so. V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. /A/ reason to avoid using merge for many users is that a query that tries to use Access Forms!formname!fieldname syntax (which works in Access) isn't necessarily going to work - actually, I've never really investigated that area. If however you are creating a query that hits the underlying table/query, as you are proposing, you should be OK. What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Not really - I think it's using the "simple" approach. I would saythat the main advantages of using merge come into play if you are letting the users modify the layout and content of the letters/documents they are producing. In that case, there is a pre-existing UI (i.e. the various mailmerge tools) which many users understand, at least to an extent, which allows them to insert fields (and even nest them and so on), preview data, etc. However, having a separate data source in the Word document complicates everything because of a. the SQL dialog we've discussed b. multi-user considerations c. the fact that if you move the data source, you run into problems if the Word document has the old data source set up when you open it, etc. (and probably other stuff I've forgotten). Peter Jamieson "SJMac" wrote in message ... "Peter Jamieson" wrote: I suggest you use Google groups to have a good look through this newsgroup - I had been doing that, but remained confused until I started recognising and understanding the term "main merge document", and discovered that "detach data source" corresponds with the Merge toolbar button "Main Document SetupNormal Word Document". The simple stuff :-( be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? Word - but I see that you suggest it's easier from the Access side of the fence (below). When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 Yup, I'd seen that, but was happy with the prompt temporarilly so that I could tell that when there was still some embeded "automation". Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? When you create a new document based on a template that has a data source attached, Word tries to connect tot he data source for both the template and the new document. It is AFAIK trying to check the connection information, but is also trying to retrieve data to be used in the merge preview and to let the user select records using Word's Select Recipients dialog box. OK, that makes sense. As far as I can tell, in Word 2000 and earlier, Word did not try to open one connection for the document and another for the attached template, but in Word 2002/2003 it generally does so, and that can cause problems if the data source is not a multi-user data source, or is opened using a method that insists on exclusive access. Frankly, you don't get much control over the exclusivity, read-only-ness of a Word connection to a data source using OpenDataSource and its connection string - in some cases, Word appears just to use whatever settings it likes in that area. Interesting. Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? It sounds like you are in efect creating a new mailmerge main document with a new data source at this point. Yes, perhaps I did. I can't repeat this now that I know what I'm doing! My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. If your application is fairly simple, i.e. you just need to stuff a few values into Word from a selected recod in Access, I would avoid MailMerge altogether as it's far too complicated. it's probably better to follow e.g. http://support.microsoft.com/kb/210271 What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Peter Jamieson Peter, your reply has been very helpful, Thank you! Steven |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other
Which actions in Access would cause a table to be locked? I assume
multiple readers are OK? It looks like my user makes all changes to the DB via a series of forms, rather than editing the tables directly. I don't know Access /that/ well - broadly speaking, if a user can have the form you want open and can still run queries successfully on another app. or machine, you are probably OK. Personally, I would probably want to see what happens when the user has made an update or insertion from the form - can another program then get the data? what data does it see? When experimenting with Access one of the things that generally screws up Word connecting to it is having a table /definition/ dialog box open, but of course that's generally not going to happen in the sort of scenario you're describing. As for your macro, 1. I agree about validating that the value is numeric. When you construct an SQL statement programmatically, from a security perspective you should always validate all the input in case the user has typed in something that makes your SQL statement do something completely unexpected. For example in this case supposing .QueryString has SELECT * FROM mytable and the user enters a value for Ref of "(SELECT xyz FROM someothertable WHERE myvalue = 123)" then your statement ends up as SELECT * FROM mytable WHERE Ref=(SELECT xyz FROM someothertable WHERE myvalue = 123) OK, that's not "dangerous", but someone might well be able to think of something that is. 2. If you execute this macro more than once, you will end up with a statement like SELECT * FROM mytable WHERE Ref=123 WHERE Ref=456 which won't work. You really need to save the .Querystring somewhere before modifying it. 3. I would verify that changing the .Querystring always works the way you expect. Sometimes I've found that it's necessary to issue an OpenDataSource to change it, not necessarily with Access though. Peter Jamieson "SJMac" wrote in message ... Peter, Thanks again, your help has been very useful helping me to understand my problem! Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. Which actions in Access would cause a table to be locked? I assume multiple readers are OK? It looks like my user makes all changes to the DB via a series of forms, rather than editing the tables directly. I'm not (yet!) a VB Programmer, but can you give me any comments on the Macro that I recorded and then edited? I think I should at least validate that the user input is numeric, but I'm not going to bother checking that it is valid record! Sub MERGE() ' ' MERGE Macro ' Macro recorded 7/26/2006 by Steven Mackenzie ' On Error GoTo ExitMerge ' ... otherwise error messages are output ' in to a new document CaseRef = InputBox("Please type in the case reference number", "Case Ref") With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord .QueryString = .QueryString & " WHERE Ref = " & CaseRef End With .Execute Pause:=False End With Exit Sub ExitMerge: MsgBox Err.Description End Sub "Peter Jamieson" wrote: Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? This warning is a "scattergun" solution. The security problem is essentially that when Word connects to a data source, it executes code that is not part of Word, not necessarily part of Office, not necessarily supplied by MS, and not necessarily bening. The mention of SQL is a bit of a red herring, but for example if Word connects to Access via DDE and executes an Access query containing user-defined functions (i.e. defined in Access VBA) those functions may theoretically have significant side effects. But I could in theory write a text converter (in fact I have), an OLEDB provider or ODBC driver that takes the request "SELECT * FROM mytable" and actually wipes the hard drive or whatever. Now of course in an ideal world a properly configured system would have no such dangerous drivers/providers, and Access and every other database would have proper security controls so that the user+system administrator (if any) can know that they are not going to do anything malicious when they execute SQL code. In which case you could switch the warning off and forget about it. I expect most people probably do anyway as they have to get their work done and they cannot possibly tell whether the "SQL" they are about to execute is malicious or not. The unfortunate thing in this situation is that there are plenty of connection types where it is difficult to see that any harm could arise unless someone has modified Microsoft code. e.g. when Word connects to a Word data source. In other cases I do not really see why the /Word/ user should get a warning. I'm not sure that answers your question but I suppose I would want to have reasonable confidence that my system was safe (in this respect) before doing so. V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. /A/ reason to avoid using merge for many users is that a query that tries to use Access Forms!formname!fieldname syntax (which works in Access) isn't necessarily going to work - actually, I've never really investigated that area. If however you are creating a query that hits the underlying table/query, as you are proposing, you should be OK. What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Not really - I think it's using the "simple" approach. I would saythat the main advantages of using merge come into play if you are letting the users modify the layout and content of the letters/documents they are producing. In that case, there is a pre-existing UI (i.e. the various mailmerge tools) which many users understand, at least to an extent, which allows them to insert fields (and even nest them and so on), preview data, etc. However, having a separate data source in the Word document complicates everything because of a. the SQL dialog we've discussed b. multi-user considerations c. the fact that if you move the data source, you run into problems if the Word document has the old data source set up when you open it, etc. (and probably other stuff I've forgotten). Peter Jamieson "SJMac" wrote in message ... "Peter Jamieson" wrote: I suggest you use Google groups to have a good look through this newsgroup - I had been doing that, but remained confused until I started recognising and understanding the term "main merge document", and discovered that "detach data source" corresponds with the Merge toolbar button "Main Document SetupNormal Word Document". The simple stuff :-( be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? Word - but I see that you suggest it's easier from the Access side of the fence (below). When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 Yup, I'd seen that, but was happy with the prompt temporarilly so that I could tell that when there was still some embeded "automation". Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? When you create a new document based on a template that has a data source attached, Word tries to connect tot he data source for both the template and the new document. It is AFAIK trying to check the connection information, but is also trying to retrieve data to be used in the merge preview and to let the user select records using Word's Select Recipients dialog box. OK, that makes sense. As far as I can tell, in Word 2000 and earlier, Word did not try to open one connection for the document and another for the attached template, but in Word 2002/2003 it generally does so, and that can cause problems if the data source is not a multi-user data source, or is opened using a method that insists on exclusive access. Frankly, you don't get much control over the exclusivity, read-only-ness of a Word connection to a data source using OpenDataSource and its connection string - in some cases, Word appears just to use whatever settings it likes in that area. Interesting. Additionally, the SQL described in the warning is a search for a particular row from Access. If I click the "Merge to new document" button while editing the template, then every entry in the table is merged, so what was the point of the SQL? It sounds like you are in efect creating a new mailmerge main document with a new data source at this point. Yes, perhaps I did. I can't repeat this now that I know what I'm doing! My goal is to allow the user to create a form letter based on a single entry in the DB with as few clicks as possible. I think that I need to start by getting some good defaults in the template (like the correct data source), carrying that default over to the docs created from the template, and also having the Merge toolbar visible to the user when they are looking at a Merge document. If your application is fairly simple, i.e. you just need to stuff a few values into Word from a selected recod in Access, I would avoid MailMerge altogether as it's far too complicated. it's probably better to follow e.g. http://support.microsoft.com/kb/210271 What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Peter Jamieson Peter, your reply has been very helpful, Thank you! Steven |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Detach or change SQL data source for Word template, and other
Peter,
Thanks again, especially for pointing out that the multi-WHERE problem on subsequent runs -- I'd only tested it once. Just for completeness, here's my final version Sub MERGE() On Error GoTo ExitMerge ' ... otherwise error messages are output to a new document! CaseRef = Val( _ InputBox("Please type in the case reference number", _ "Case Ref")) ' ... don't trust that the user entered a number, use Val to ' convert string input to a number value. (Ignore fact that ' numbers with decimal points aren't valid for CaseRef.) With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord WhereIdx = InStrRev(.QueryString, " WHERE ", -1, vbTextCompare) If (WhereIdx 0) Then .QueryString = Left(.QueryString, WhereIdx - 1) & _ " WHERE Ref = " & CaseRef Else .QueryString = .QueryString & " WHERE Ref = " & CaseRef End If End With .Execute Pause:=False End With Exit Sub ExitMerge: MsgBox Err.Description End Sub "Peter Jamieson" wrote: Which actions in Access would cause a table to be locked? I assume multiple readers are OK? It looks like my user makes all changes to the DB via a series of forms, rather than editing the tables directly. I don't know Access /that/ well - broadly speaking, if a user can have the form you want open and can still run queries successfully on another app. or machine, you are probably OK. Personally, I would probably want to see what happens when the user has made an update or insertion from the form - can another program then get the data? what data does it see? When experimenting with Access one of the things that generally screws up Word connecting to it is having a table /definition/ dialog box open, but of course that's generally not going to happen in the sort of scenario you're describing. As for your macro, 1. I agree about validating that the value is numeric. When you construct an SQL statement programmatically, from a security perspective you should always validate all the input in case the user has typed in something that makes your SQL statement do something completely unexpected. For example in this case supposing .QueryString has SELECT * FROM mytable and the user enters a value for Ref of "(SELECT xyz FROM someothertable WHERE myvalue = 123)" then your statement ends up as SELECT * FROM mytable WHERE Ref=(SELECT xyz FROM someothertable WHERE myvalue = 123) OK, that's not "dangerous", but someone might well be able to think of something that is. 2. If you execute this macro more than once, you will end up with a statement like SELECT * FROM mytable WHERE Ref=123 WHERE Ref=456 which won't work. You really need to save the .Querystring somewhere before modifying it. 3. I would verify that changing the .Querystring always works the way you expect. Sometimes I've found that it's necessary to issue an OpenDataSource to change it, not necessarily with Access though. Peter Jamieson "SJMac" wrote in message ... Peter, Thanks again, your help has been very useful helping me to understand my problem! Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. Which actions in Access would cause a table to be locked? I assume multiple readers are OK? It looks like my user makes all changes to the DB via a series of forms, rather than editing the tables directly. I'm not (yet!) a VB Programmer, but can you give me any comments on the Macro that I recorded and then edited? I think I should at least validate that the user input is numeric, but I'm not going to bother checking that it is valid record! Sub MERGE() ' ' MERGE Macro ' Macro recorded 7/26/2006 by Steven Mackenzie ' On Error GoTo ExitMerge ' ... otherwise error messages are output ' in to a new document CaseRef = InputBox("Please type in the case reference number", "Case Ref") With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord .QueryString = .QueryString & " WHERE Ref = " & CaseRef End With .Execute Pause:=False End With Exit Sub ExitMerge: MsgBox Err.Description End Sub "Peter Jamieson" wrote: Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? This warning is a "scattergun" solution. The security problem is essentially that when Word connects to a data source, it executes code that is not part of Word, not necessarily part of Office, not necessarily supplied by MS, and not necessarily bening. The mention of SQL is a bit of a red herring, but for example if Word connects to Access via DDE and executes an Access query containing user-defined functions (i.e. defined in Access VBA) those functions may theoretically have significant side effects. But I could in theory write a text converter (in fact I have), an OLEDB provider or ODBC driver that takes the request "SELECT * FROM mytable" and actually wipes the hard drive or whatever. Now of course in an ideal world a properly configured system would have no such dangerous drivers/providers, and Access and every other database would have proper security controls so that the user+system administrator (if any) can know that they are not going to do anything malicious when they execute SQL code. In which case you could switch the warning off and forget about it. I expect most people probably do anyway as they have to get their work done and they cannot possibly tell whether the "SQL" they are about to execute is malicious or not. The unfortunate thing in this situation is that there are plenty of connection types where it is difficult to see that any harm could arise unless someone has modified Microsoft code. e.g. when Word connects to a Word data source. In other cases I do not really see why the /Word/ user should get a warning. I'm not sure that answers your question but I suppose I would want to have reasonable confidence that my system was safe (in this respect) before doing so. V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? Not unless you run into multi-user related problems, e.g. if a table is locked while your form is displaying in Access, or the record you're looking at has been added as part of a transaction that is incomplete and Word can't see it. /A/ reason to avoid using merge for many users is that a query that tries to use Access Forms!formname!fieldname syntax (which works in Access) isn't necessarily going to work - actually, I've never really investigated that area. If however you are creating a query that hits the underlying table/query, as you are proposing, you should be OK. What an interesting kb! That would be exactly what I want, except it's not quite what the user had before. BTW, the sample doesn't use Merge at all - that's odd isn't it? Not really - I think it's using the "simple" approach. I would saythat the main advantages of using merge come into play if you are letting the users modify the layout and content of the letters/documents they are producing. In that case, there is a pre-existing UI (i.e. the various mailmerge tools) which many users understand, at least to an extent, which allows them to insert fields (and even nest them and so on), preview data, etc. However, having a separate data source in the Word document complicates everything because of a. the SQL dialog we've discussed b. multi-user considerations c. the fact that if you move the data source, you run into problems if the Word document has the old data source set up when you open it, etc. (and probably other stuff I've forgotten). Peter Jamieson "SJMac" wrote in message ... "Peter Jamieson" wrote: I suggest you use Google groups to have a good look through this newsgroup - I had been doing that, but remained confused until I started recognising and understanding the term "main merge document", and discovered that "detach data source" corresponds with the Merge toolbar button "Main Document SetupNormal Word Document". The simple stuff :-( be a button in the toolbar called "Merge" that prompted the user to type in a In which application? Access or Word? Word - but I see that you suggest it's easier from the Access side of the fence (below). When I open the Word templates I get warnings that some SQL is about to be executed - that's OK, This prompt is displayed every time you open a Mail Merge Main document connected to a data source (well, there may be circumstances in which it is not displayed, but they are oddities). The prompt can be suppressed by changing the Windows registry - see http://support.microsoft.com/default...b;en-us;825765 Yup, I'd seen that, but was happy with the prompt temporarilly so that I could tell that when there was still some embeded "automation". Regarding the warning's security purpose, I regard the effects of data being inserted into a document as harmless -- Merge can't insert malicious code can it? Is everyone here happy to disable that warning? The "SQL" is created when the user connects the mail merge main document to a data source, and is saved with the document and re-issued when it is opened, along with a suitable connection string etc. You can't really delete it, since there is always some "SQL" - that's simply how Word codifies its queries. The connection may have been made programmatically using Word's OpenDataSource method, and the SQL may have been modified by changing ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly) what Word is trying to issue, after it has successfully opened the data source, by printing the values of ActiveDocument.MailMerge.DataSource.Name ActiveDocument.MailMerge.DataSource.ConnectString ActiveDocument.MailMerge.DataSource.QueryString V helpful, thanks. Right now, I'm imagining that some code that pops up an input to allow the user to enter a row id, alters the QueryString, then does the merge shouldn't be hard to do, and will hide the slightly tedious MailMergeRecipients dialog from the user. I'll try ... is there a problem you'd expect me to see? No data is inserted in to the templates after I open them. Why is SQL being executed for the template? Surely the data normally needs to be retrieved for the actual documents, or when I do the merge? |