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 |
#21
|
|||
|
|||
fields queries and utter disaster
Hi
My version of MS Query did not allow me to save my query. It seems I can only execute a query via excel. I can actually start MS Query as a standalone but it doesnt let me do anything. It can neither open a file or create a query. I find it a bit odd that neither Office.X nor Office 2004 has a simple method of accessing a mysql database. It would appear that OpenOffice is ahead of Microsoft in this respect. I find this to be a sad state of affairs. However, I shall not give up. I will see if I can get your suggestion to work. Once again many thanks for your effort. It is appreciated very much! |
#22
|
|||
|
|||
fields queries and utter disaster
I find it a bit odd that neither Office.X nor Office 2004 has a simple
method of accessing a mysql database. I think it's fair to say that "Office" does, in the sense that Excel does. I've never had the impression that Microsoft has regarded data connectivity as a particularly important feature in Word, although they have occasionally tried to make it easier to use, and it may be that demand for such features on the Mac is lower than it is on the Windows version of Office (i.e. Mac users probably do that sort of stuff some other way - I'm a realtive newcomer to Mac so really don't know). Peter Jamieson "consiglieri" wrote in message oups.com... Hi My version of MS Query did not allow me to save my query. It seems I can only execute a query via excel. I can actually start MS Query as a standalone but it doesnt let me do anything. It can neither open a file or create a query. I find it a bit odd that neither Office.X nor Office 2004 has a simple method of accessing a mysql database. It would appear that OpenOffice is ahead of Microsoft in this respect. I find this to be a sad state of affairs. However, I shall not give up. I will see if I can get your suggestion to work. Once again many thanks for your effort. It is appreciated very much! |
#23
|
|||
|
|||
fields queries and utter disaster
Hi Peter et al,
The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message oups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#24
|
|||
|
|||
fields queries and utter disaster
Hi Jim,
A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message oups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#25
|
|||
|
|||
fields queries and utter disaster
Hi all
I've been looking into using perl and the win32:le module. From my tests so far this seems to be a way to achieve mysql connectivity. Let the perl script connect to the mysql server, gather the info you want in the word documents, then open the word document with win32:le with the necessary info inputed. I haven't had 100% success, but so far I can initiate a word or excel document, input info. Ofcourse using perl and applescript should be a doable. Since I know more perl then applescript I havent tried applescript yet. I will certainly let the mac business unit know what I think word should be able to do with resepct to mysql connectivity. Thomas PS/ Should i continue to post efforts to try an connect even if they include som perl or applescript specifics? |
#26
|
|||
|
|||
fields queries and utter disaster
In article .com,
"consiglieri" wrote: Ofcourse using perl and applescript should be a doable. Since I know more perl then applescript I havent tried applescript yet. One could do this from within VBA: Selection.TypeText MacScript("do shell script ""perl ~/hello.cgi""") |
#27
|
|||
|
|||
fields queries and utter disaster
Aha..didnt know VBA could execute perl scripts.Thats good news I think.
Can you recommend a good tutorial for VBA on the net ...where they have some kind of example that helps you get going? |
#28
|
|||
|
|||
fields queries and utter disaster
|
#29
|
|||
|
|||
fields queries and utter disaster
Hi Peter,
After experimenting a bit more I discovered that the DATABASE field code does actually work on Mac Office. I tried it in Office 2001 and Office 2004. The following field code when refreshed will return the requested data: { DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" } This means that two of our major questions have been answered. The DATABASE field code works and SQL in Word works! It seems the only remaining piece of the puzzle to solve is what the syntax is for the \c field switch. I tried several likely things, but was unable to find a syntax that works. Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: Hi Jim, A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message egroups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
#30
|
|||
|
|||
fields queries and utter disaster
Hi Jim,
Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. Good. Even in Word 2003 where ODBC connections are definitely feasible, the basic problem is that you have to supply a data file (\d) parameter in the { DATABASE } field, specifying a file in the Windows file system, otherwise you get the Select Data Source dialog. Since server-type databases such as MySQL generally want you to connect to a "server" rather than a "file", it's not obvious what you specify in the \d parameter to make it all work. On the Windows version, in the similar OpenDataSource method you can get around this by specifying the data file as "", but to make that work in recent versions of Word you have to add a special Subtype parameter that makes it work like Word 2000. In the DATABASE field, \d "" just doesn't work. Again on the Windows version, it's possible to work around this by using the name of an ODBC file DSN in the \d parameter, and specifying \c "FILEDSN=the_file_DSN_pathname" I do not know whether the equivalent would work on Mac because a. the ODBC administrator I'm using (iODBC) doesn't have a facility to create a file DSN. Perhaps there is other ODBC administrator software that can do it. b. in Windows you can create one using the ODBC administrator or by hand in Notepad. It's pretty clear what needs to go in the .dsn. On Mac, I tried this but could not tell whether it did not work because... - file DSNs are not supported (and perhaps even unheard of) - I put the wrong information in - of some additional Mac constraint, e.g. the file has to have a certain type. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter, After experimenting a bit more I discovered that the DATABASE field code does actually work on Mac Office. I tried it in Office 2001 and Office 2004. The following field code when refreshed will return the requested data: { DATABASE \d "MyDrive:MySource.xls \s "SELECT MyColumn from Sheet1" } This means that two of our major questions have been answered. The DATABASE field code works and SQL in Word works! It seems the only remaining piece of the puzzle to solve is what the syntax is for the \c field switch. I tried several likely things, but was unable to find a syntax that works. Someone at Microsoft is going to see if they can get us a sample or a determination for sure about the \c field switch. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: Hi Jim, A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. I would be interested to know what these were if you happened to keep a note. The only time I was able to get any such thing was when Word determined that the source was a text file and started asking for delimiters, and that was only when I tried to replicate the Windows approach of using a .dsn file. However, since the questioner was originally asking about Word.X perhaps you have found stuff that will work on that version. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. Indeed, it's the kind of thing where direct feedback from the developers is really needed. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. Yes, I posted some code for this that seems to work, but if you happen to have code that lets you modify the connection string/query string of an existing Excel QueryTable using automation from Word, please let me know. Every time I tried this, Word crashed. However, I can't say I've exhausted all the possibilities in this area. The other thing I noticed was that the documentation in Office 2004 suggests that it should be possible to use query files (cf. .qry/.dqy files on Windows), even though MS Query cannot currently do so, using FINDER; as the file type in the necessary connection string. So I tried one I'd created on Windows but no luck. Peter Jamieson "Jim Gordon MVP" wrote in message ... Hi Peter et al, The situation with database connectivity in Office 2004 is not good, but it has been improving. I tried a large number of different syntax combinations with Word fields, but I was not able to hit the jackpot getting a connection between a Word 2004 database field and an ODBC data source. There are some examples for Windows Word that I tried to adapt to the Mac, but my attempts were unsuccessful. A few combinations seemed to activate a connection dialog, but result set was unusable gibberish. That's not to say I have the final answer to this. There might be a syntax that works, but so far no one I know has stumbled across it. My conclusion is that database Word fields in 2004 are broken. There's a somewhat logical reason for this particular feature to be broken. Unlike Windows, Mac OS does not ship with a complete set of ODBC drivers. Since there were no ODBC drivers of any kind when Word 2004 was built, there was no way to test to see if the database features worked or not. However, now there are ODBC drivers for the Mac. Microsoft has started to support them in Office. So far that effort has resulted in a partial implementation of MS Query. The 2004 implementation of MS Query is the basic GUI from Office 2001, but it is far from complete. The current MS Query is "read only." You can issue SELECT and other SQL statements producing a result set of records, but you can't issue TABLE commands and the like. Concerning programmability of MS Query - there is none. It is not scriptable with AppleScript. It will not respond to Visual Basic. The ODBC Visual Basic add-in has not been updated to work with MS Query. You can not save the queries as a file using the MS Query FILE menu. However, you can use SQL view, copy the query and save any query as a text file manually. You could write an AppleScript that controls the menus to do this, but there is no script ability with MS Query itself. On the positive side there are at least two software vendors who are actively making ODBC drivers for Mac OS. Actual Technologies has a reasonably priced one for MySQL http://www.actualtechnologies.com/products.php I offer the suggestion that you use MS Query with the Actual Technologies driver and create queries in Excel worksheets that contain the data you want to import into Word. You can programatically update the queries as needed using VBA in Excel and also control Excel via VBA in Word. So once you have created the queries you can refresh the results as needed. The data merge manager in Word will allow you to merge the records from the fields as you described. The work-around I am proposing is a two-stage process. The SQL queries will be embedded into Excel worksheets. Word will use the Excel worksheets as data sources. I will ask the Microsoft Word team to take a look at this thread. And I urge anyone who is interested in directly connecting Word to data sources using Word Fields and the Data Merge Manager: please take a moment to let Microsoft know your requirements by sending feedback to the Mac Business Unit at this URL http://www.microsoft.com/mac/default...ng=en&app=Word It is especially important to send this feedback *now* so that the developers know they should pay attention to this feature as they build the next version of Word. Mac Office 12 is now under construction, but it is a huge undertaking. Don't expect it any time soon. As far as future programmability is concerned, please be sure to let the MacBU know in your database connectivity needs and that you would like to have programmability included in the feature set of tools available for Mac Office. Three seperate but important public announcements from Microsoft should be considered in your future programmability plans for Mac Office. 1. AppleScript will continued to be supported in the future. 2. Visual Basic for Applications will not be supported at some unspecified time in the future on both PC and Mac versions of Office. 3. C# will be brought to the Mac Because of those three facts I am not hopeful the ODBC add-in for Visual Basic is ever going to be updated for the Mac - especially since VBA itself will go away sooner or later. It is much more likely that we will see a C# or AppleScript solution. Just something to think about for the future. But no programmability solution for external databases will be made unless the Mac BU hears from customers who want it, so please be sure to send feedback. Thanks. -Jim -- Jim Gordon Mac MVP MVP FAQ http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs Peter Jamieson wrote: In that case, you are probably using a DATABASE field, in which case you can reveal the field code showing e.g. { DATABASE \c "connection info." \s "SELECT * FROM mytable" } and substitute the query you need. To insert the "variable" part of it, you can nest a FILLIN filed that pops up a dialog when you execute the fields, e.g. { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = { FILLIN "Enter ID2" \o } } which is suitable for a numeric ID. If the ID is non-numeric you'll need to use { DATABASE \c "connection info" \s "SELECT Table1.ID1, Table2.ID1 FROM TABLE1, TABLE2 WHERE Table1.ID1 = TABLE2.ID1 AND TABLE2.ID2 = '{ FILLIN "Enter ID2" \o }' } Peter Jamieson "consiglieri" wrote in message legroups.com... Hi Thanks for the answers. To beging with - what i want to achieve is simply an autmatic query whereby my word document receives a particular field from the database. I input for instance a project number and from the database a klient number for that project is selected and automagically inserted into the word document upon save or print. This is a pretty straightforward deal with openoffice and I would imagine that one should be able to do it with MS Office. How i connect to mysql Well i simply used the database tools in word. Made added a source. But since I have a swedish version I will need to figure out the appropriate english terms. Its a holiday here so I will get back on that one. Thanks |
Thread Tools | |
Display Modes | |
|
|