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??

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??

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??

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??

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??

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??

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

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

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??

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??