Problem with blank entries being filled with other values

[Denise]

Hello


Using Office 2002 SP-2, Windows 2000 V5.0 SP-4.

One of my users set up a mailmerge document for printing labels and has
been using this successfully with an Access table created using a Make
Table query. Originally the table used to create the mailmerge table was
an Access table but, due to corruption problems, we had to move it out to
SQL Server 2000.

The Make Table query still runs successfully, producing a correct table.
The problem is that, when the user merges this table to Word, empty values
in the Counter field are replaced with values from this field in other
records. e.g. the last value in the Counter field is 7625, so all records
that do not contain a value in the Counter field are given the value 7625
as well.

I have checked the data source from within Word and the result set looks
normal, i.e. the blank values are blank. however, when you actually run
the merge, Word is refusing to allow these fields to stay blank on the
document.

This problem only began appearing after we changed to a SQL Server table.
The resulting table from the query is still an Access table but now the
merge misbehaves. The Counter field has a numeric type in SQL and a number
type in the resulting Access table.

If anyone has any suggestions I'd be very grateful.

Many thanks

Denise Crawley

 

[Peter Jamieson]

I don't know why this happens but I couldn't quite understand exactly what
is happening either. Is it...

You have a table in SQL Server 2000.
Then you use a make table query in Access to create a table in Access.
Then you use that table as the data source for the merge?

Or are you managing to use the Make table query as the data source for the
merge (something I do not seem to be able to do here)?

Unless you have a very good reason to use the route you're using, I suppose
I would just try various other approaches to getting the data, e.g.
a. try a different connection method (check Word
Tools|Options|General|"Confirm conversion at open", go through the
connection process again, and see if using another method makes it work)
b. instead of using a make table query, simply link the Access table to the
SQL Server data, and write an Access query to query that data if you need to
filter the data in some way
c. get the data directly from SQL Server, either directly from the table,
or if necessary, using a view.

Peter Jamieson

 

[Denise]

Thanks for your reply, Peter. I tried a couple of difference connections
and one of them worked perfectly.

Just to clarify:-

We have a table in SQL Server
We have a linked table in Access
We run a Make Table query within Access, which produces a table in Access
This table is used for the mailmerge.

We needed to be able to filter the data as there are 10,500 records in the
main table, but often we only need to pull a dozen of these.

I used the connection 'MS Access databases via DDE (*.mdb, *.mde)', as
opposed to 'MS Access database via ODBC (*.mdb)', which the user had been
using. The connection via DDE worked.

Thanks again for your help.

Denise