Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
I am merging a letter in Word that uses tables from Access and SQL - the
fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
How are you defining which records to merge? (e.g., are you checking
individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Sorry - I could have made that clearer - the data is held in a SQL Server
database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Sorry for the delayed response - I have been away. Nothing springs to mind
but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Thanks Peter - it is a very frustrating glitch - I can't reproduce it in
W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
OK, I haven't been able to reproduce this here, but my data may be too
simple. I don't know if you are in a position to modify the data and structures on your database, but a few questions/suggestions: a. what is the data source exactly (a table, a view, does it involve a Transact-SQL procedure). b. how are you connecting to SQL Server? Are you using the same ODBC connection as you would have used in Word 2000 (perhaps in conjunction with MS Query), or an OLEDB connection? If you have not tried both already, try the other method. c. if possible, drop/recreate the table's indexes. d. If you connect to other tables, do you see the same problem? In particular, can you create a copy of the table you are using with a new name and copy some (and/or all, although that may be asking rather a lot given the table size you mention) of the data to that table, and use that as the data source. e. Can you provide a sample of the SQL Query code that Word is using? (to find that out, you can open the mail merge main document, set up the filter you want, start Word's VB editor, open the immediate window, and type print ActiveDocument.MailMerge.DataSource.QueryString and copy/paste the result into a message here. (If you do that, it might also be useful to do print ActiveDocument.MailMerge.DataSource.Name and print ActiveDocument.MailMerge.DataSource.ConnectString as well) Peter Jamieson "RSMITH" wrote in message news Thanks Peter - it is a very frustrating glitch - I can't reproduce it in W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word labels
When I open word (Microsoft XP) for labels it saves it to an access data base
and my print document is word. The details are saved in "My data source" and it doesn't give me any other option to save the information except in an Access table. I've never had this trouble with MS Office and this is the first time I have used the labels on XP. It is important for work, and urgent for me! Thanks! -- Jennml |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access to Word labels
I am not really sure what the issue is, but if you want to get the data into
a different form, use the datasource for a Directory type mailmerge in which you insert the merge fields, one to a cell, of a single row table. When you execute that merge to a new document, that document will contain a row of data for each record in the data source. If you want to use that as your mailmerge data source, insert a row at the top of the table and enter the field names into the cells of that row. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Jennifer" wrote in message ... When I open word (Microsoft XP) for labels it saves it to an access data base and my print document is word. The details are saved in "My data source" and it doesn't give me any other option to save the information except in an Access table. I've never had this trouble with MS Office and this is the first time I have used the labels on XP. It is important for work, and urgent for me! Thanks! -- Jennml |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Peter - I'm not familiar with Word VB - I believe I did what you suggested -
open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! I have a desktop link to the SQL Server data on the server - I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Peter I am anxious to resolve this problem but I shall be away for two weeks after today - if you can advise how to use the VB editor today I can get that back to you... "Peter Jamieson" wrote: OK, I haven't been able to reproduce this here, but my data may be too simple. I don't know if you are in a position to modify the data and structures on your database, but a few questions/suggestions: a. what is the data source exactly (a table, a view, does it involve a Transact-SQL procedure). b. how are you connecting to SQL Server? Are you using the same ODBC connection as you would have used in Word 2000 (perhaps in conjunction with MS Query), or an OLEDB connection? If you have not tried both already, try the other method. c. if possible, drop/recreate the table's indexes. d. If you connect to other tables, do you see the same problem? In particular, can you create a copy of the table you are using with a new name and copy some (and/or all, although that may be asking rather a lot given the table size you mention) of the data to that table, and use that as the data source. e. Can you provide a sample of the SQL Query code that Word is using? (to find that out, you can open the mail merge main document, set up the filter you want, start Word's VB editor, open the immediate window, and type print ActiveDocument.MailMerge.DataSource.QueryString and copy/paste the result into a message here. (If you do that, it might also be useful to do print ActiveDocument.MailMerge.DataSource.Name and print ActiveDocument.MailMerge.DataSource.ConnectString as well) Peter Jamieson "RSMITH" wrote in message news Thanks Peter - it is a very frustrating glitch - I can't reproduce it in W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Peter - I'm not familiar with Word VB - I believe I did what you
suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! In the VB Editor you need to type what I suggested into the "Immediate Window". If you didn't see an "Immediate Window", click View|Immediate Window to open it. If you have the time today, can you clarify a couple of things he I have a desktop link to the SQL Server data on the server What sort of link is this? I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Are you saying that a. whatever you retrieve the records into, you still see the problem where "record 1 in the database" replaces the first record you want? Even if Word is not involved at all? or that b. when you retrieve the records into Access, they are the ones you want, and it is only when you try to get them via Word that the replacement occurs? (Seems unlikely that that could be the case but maybe you could confirm/deny) If it's (a) then I would have to suspect an error in your Dynamics database (perhaps index corruption as I suggested, or a problem - perhaps corruption - in an OLEDB provider and/or some other part of Microsoft's general data access software (a.k.a. the MDAC). If the Dynamics database makes extensive use of SQL Transact procedures, than I could just about imagine that there is an error in (one of) the relevant procedure(s), but that seems highly unlikely. If you haven't tried the ODBC route, that's still my best guess at the moment for a quick solution, primarily because that's what Word 2000 would have used. However, it is usually non-trivial to connect using that method so maybe we had better leave it for now... Peter Jamieson "RSMITH" wrote in message ... Peter - I'm not familiar with Word VB - I believe I did what you suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! I have a desktop link to the SQL Server data on the server - I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Peter I am anxious to resolve this problem but I shall be away for two weeks after today - if you can advise how to use the VB editor today I can get that back to you... "Peter Jamieson" wrote: OK, I haven't been able to reproduce this here, but my data may be too simple. I don't know if you are in a position to modify the data and structures on your database, but a few questions/suggestions: a. what is the data source exactly (a table, a view, does it involve a Transact-SQL procedure). b. how are you connecting to SQL Server? Are you using the same ODBC connection as you would have used in Word 2000 (perhaps in conjunction with MS Query), or an OLEDB connection? If you have not tried both already, try the other method. c. if possible, drop/recreate the table's indexes. d. If you connect to other tables, do you see the same problem? In particular, can you create a copy of the table you are using with a new name and copy some (and/or all, although that may be asking rather a lot given the table size you mention) of the data to that table, and use that as the data source. e. Can you provide a sample of the SQL Query code that Word is using? (to find that out, you can open the mail merge main document, set up the filter you want, start Word's VB editor, open the immediate window, and type print ActiveDocument.MailMerge.DataSource.QueryString and copy/paste the result into a message here. (If you do that, it might also be useful to do print ActiveDocument.MailMerge.DataSource.Name and print ActiveDocument.MailMerge.DataSource.ConnectString as well) Peter Jamieson "RSMITH" wrote in message news Thanks Peter - it is a very frustrating glitch - I can't reproduce it in W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Peter - sorry for the delay in getting back to you - having now understood a
little more the results were as follows: print ActiveDocument.MailMerge.DataSource.QueryString SELECT * FROM "Permadoor$PMDR Property" print ActiveDocument.MailMerge.DataSource.Name C:\Documents and Settings\RogerSmith\My Documents\My Data Sources\192.168.1.199 Permadoor Permadoor$PMDR Property.odc print ActiveDocument.MailMerge.DataSource.ConnectString Provider=SQLOLEDB.1;Password=roger;Persist Security Info=True;User ID=rsmith;Initial Catalog=Permadoor;Data Source=192.168.1.199;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=S_F2;Use Encryption for Data=False;Tag with column collation when possible=False The link to the data is across the network - the data being on the server - SQL server being where the data is held and this is accessed through a MS Navision front. When I ask Word through Mail Merge Helper to filter all records for say, Oxfordshire, it replaces record 1 for Oxfordshire with record one for the entire database and that may be say a record for Berkshire. (When I try the same on another machine where Word 2000 is used the problem does not exist - I only get this on Word 2003.) Regards Roger Smith "Peter Jamieson" wrote: Peter - I'm not familiar with Word VB - I believe I did what you suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! In the VB Editor you need to type what I suggested into the "Immediate Window". If you didn't see an "Immediate Window", click View|Immediate Window to open it. If you have the time today, can you clarify a couple of things he I have a desktop link to the SQL Server data on the server What sort of link is this? I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Are you saying that a. whatever you retrieve the records into, you still see the problem where "record 1 in the database" replaces the first record you want? Even if Word is not involved at all? or that b. when you retrieve the records into Access, they are the ones you want, and it is only when you try to get them via Word that the replacement occurs? (Seems unlikely that that could be the case but maybe you could confirm/deny) If it's (a) then I would have to suspect an error in your Dynamics database (perhaps index corruption as I suggested, or a problem - perhaps corruption - in an OLEDB provider and/or some other part of Microsoft's general data access software (a.k.a. the MDAC). If the Dynamics database makes extensive use of SQL Transact procedures, than I could just about imagine that there is an error in (one of) the relevant procedure(s), but that seems highly unlikely. If you haven't tried the ODBC route, that's still my best guess at the moment for a quick solution, primarily because that's what Word 2000 would have used. However, it is usually non-trivial to connect using that method so maybe we had better leave it for now... Peter Jamieson "RSMITH" wrote in message ... Peter - I'm not familiar with Word VB - I believe I did what you suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! I have a desktop link to the SQL Server data on the server - I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Peter I am anxious to resolve this problem but I shall be away for two weeks after today - if you can advise how to use the VB editor today I can get that back to you... "Peter Jamieson" wrote: OK, I haven't been able to reproduce this here, but my data may be too simple. I don't know if you are in a position to modify the data and structures on your database, but a few questions/suggestions: a. what is the data source exactly (a table, a view, does it involve a Transact-SQL procedure). b. how are you connecting to SQL Server? Are you using the same ODBC connection as you would have used in Word 2000 (perhaps in conjunction with MS Query), or an OLEDB connection? If you have not tried both already, try the other method. c. if possible, drop/recreate the table's indexes. d. If you connect to other tables, do you see the same problem? In particular, can you create a copy of the table you are using with a new name and copy some (and/or all, although that may be asking rather a lot given the table size you mention) of the data to that table, and use that as the data source. e. Can you provide a sample of the SQL Query code that Word is using? (to find that out, you can open the mail merge main document, set up the filter you want, start Word's VB editor, open the immediate window, and type print ActiveDocument.MailMerge.DataSource.QueryString and copy/paste the result into a message here. (If you do that, it might also be useful to do print ActiveDocument.MailMerge.DataSource.Name and print ActiveDocument.MailMerge.DataSource.ConnectString as well) Peter Jamieson "RSMITH" wrote in message news Thanks Peter - it is a very frustrating glitch - I can't reproduce it in W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2003/Access2000/SQLSVR
Hello Roger,
I've also been away. BTW, because we've been discussing this question for a while, I should say that it's quite likely that I will not be able to get to the bottom of the problem, and certainly not if it results from a fault in Word 2003 or Microsoft's MDAC code, so you might want to consider opening a support incident directly with Microsoft (we're just volunteers), e.g. via http://support.microsoft.com, or any other support agreement you might have with Microsoft. I couldn't see anything in the values you provided that would cause problems on their own. It may be significant that you are not using "Integrated Security" to log into the SQL Server, but I doubt it. If you can answer my other questions (with the (a)/(b) options), so much the better. I notice that the QueryString value does not have any "WHERE" clause which I would expect if you had applied a filter, so I'm assuming that the value you provided is the one you see when you open the document. Can you try applying the filter you want (e.g. Oxfordshire records) and print out the value of QueryString at that point? Also, if you are still able to do this from Word 2000, please print the same three values from there - that should also make it easier to try the "connecting via ODBC" option I suggested. Peter Jamieson "RSMITH" wrote in message ... Peter - sorry for the delay in getting back to you - having now understood a little more the results were as follows: print ActiveDocument.MailMerge.DataSource.QueryString SELECT * FROM "Permadoor$PMDR Property" print ActiveDocument.MailMerge.DataSource.Name C:\Documents and Settings\RogerSmith\My Documents\My Data Sources\192.168.1.199 Permadoor Permadoor$PMDR Property.odc print ActiveDocument.MailMerge.DataSource.ConnectString Provider=SQLOLEDB.1;Password=roger;Persist Security Info=True;User ID=rsmith;Initial Catalog=Permadoor;Data Source=192.168.1.199;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=S_F2;Use Encryption for Data=False;Tag with column collation when possible=False The link to the data is across the network - the data being on the server - SQL server being where the data is held and this is accessed through a MS Navision front. When I ask Word through Mail Merge Helper to filter all records for say, Oxfordshire, it replaces record 1 for Oxfordshire with record one for the entire database and that may be say a record for Berkshire. (When I try the same on another machine where Word 2000 is used the problem does not exist - I only get this on Word 2003.) Regards Roger Smith "Peter Jamieson" wrote: Peter - I'm not familiar with Word VB - I believe I did what you suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! In the VB Editor you need to type what I suggested into the "Immediate Window". If you didn't see an "Immediate Window", click View|Immediate Window to open it. If you have the time today, can you clarify a couple of things he I have a desktop link to the SQL Server data on the server What sort of link is this? I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Are you saying that a. whatever you retrieve the records into, you still see the problem where "record 1 in the database" replaces the first record you want? Even if Word is not involved at all? or that b. when you retrieve the records into Access, they are the ones you want, and it is only when you try to get them via Word that the replacement occurs? (Seems unlikely that that could be the case but maybe you could confirm/deny) If it's (a) then I would have to suspect an error in your Dynamics database (perhaps index corruption as I suggested, or a problem - perhaps corruption - in an OLEDB provider and/or some other part of Microsoft's general data access software (a.k.a. the MDAC). If the Dynamics database makes extensive use of SQL Transact procedures, than I could just about imagine that there is an error in (one of) the relevant procedure(s), but that seems highly unlikely. If you haven't tried the ODBC route, that's still my best guess at the moment for a quick solution, primarily because that's what Word 2000 would have used. However, it is usually non-trivial to connect using that method so maybe we had better leave it for now... Peter Jamieson "RSMITH" wrote in message ... Peter - I'm not familiar with Word VB - I believe I did what you suggested - open VB - clicked on the open document that opened a new sheet, typed in as you asked - hit enter - nil - tried run - nil - I guess it needs something else! I have a desktop link to the SQL Server data on the server - I also have a link to the tables through Navision - I've tried cutting and pasting records from that data into an Access database on my local PC - I consistently get the same results: the first record of the requested records is always replaced with the first record on the table in use. Even when I've cut the data to a new Access table. (e.g I cut 100 records into a new table, properties from Berkshire, Oxfordshire, Wiltshire - I try to select all properties in Oxfordshire but the first record is record 1 - a property in Berkshire) Peter I am anxious to resolve this problem but I shall be away for two weeks after today - if you can advise how to use the VB editor today I can get that back to you... "Peter Jamieson" wrote: OK, I haven't been able to reproduce this here, but my data may be too simple. I don't know if you are in a position to modify the data and structures on your database, but a few questions/suggestions: a. what is the data source exactly (a table, a view, does it involve a Transact-SQL procedure). b. how are you connecting to SQL Server? Are you using the same ODBC connection as you would have used in Word 2000 (perhaps in conjunction with MS Query), or an OLEDB connection? If you have not tried both already, try the other method. c. if possible, drop/recreate the table's indexes. d. If you connect to other tables, do you see the same problem? In particular, can you create a copy of the table you are using with a new name and copy some (and/or all, although that may be asking rather a lot given the table size you mention) of the data to that table, and use that as the data source. e. Can you provide a sample of the SQL Query code that Word is using? (to find that out, you can open the mail merge main document, set up the filter you want, start Word's VB editor, open the immediate window, and type print ActiveDocument.MailMerge.DataSource.QueryString and copy/paste the result into a message here. (If you do that, it might also be useful to do print ActiveDocument.MailMerge.DataSource.Name and print ActiveDocument.MailMerge.DataSource.ConnectString as well) Peter Jamieson "RSMITH" wrote in message news Thanks Peter - it is a very frustrating glitch - I can't reproduce it in W2000 and previous versions. Sometimes I can work round it by selecting the record before the one I need - it is more difficult when I am selecting by postcode or county, etc. "Peter Jamieson" wrote: Sorry for the delayed response - I have been away. Nothing springs to mind but I will have a look. Peter Jamieson "RSMITH" wrote in message ... Sorry - I could have made that clearer - the data is held in a SQL Server database and linked to Access 2000, I have tried taking the data directly from SQL Server with identical results, there are around 150k records. From that I merge into a Word 2003 document, I don't use the wizard in word because that is too cumbersome so I use the query facility that was standard in Word 2000, the records are usually simply filtered to an account number and postcode. "Peter Jamieson" wrote: How are you defining which records to merge? (e.g., are you checking individual records in the Mail merge Recipients box, or specifying a start and end record number, or what?) Also, what exactly is the data source? Are you using an Access table? An Access query? An Access table linked to a SQL Server database? Directly connecting to a SQL Server database? All of them? Peter Jamieson "RSMITH" wrote in message ... I am merging a letter in Word that uses tables from Access and SQL - the fields are simple address and names. On each occasion Word imports the data BUT - ignores the first requested record and inserts instead record #1 of the whole database - therefore on each merge I need to insert the data for the first required record separately. Otherwise the merge is perfect... Any ideas?? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reveal codes in a word document | Microsoft Word Help | |||
Why dont MS just f**king re-write Word from scratch? Its dogsh*t | Microsoft Word Help | |||
Converting Word Perfect forms to Word forms | Microsoft Word Help | |||
WP merge file to Word | Tables | |||
How can Word display full path of a file in the title bar? | Microsoft Word Help |