Access query merge error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jordan - 02 Jul 2004 18:36 GMT

I'm having problems with a ton of my documents merging
with Access. I ended up having to switch to DDE linking
instead, except that now when trying to search for a
record it takes forever. Is there any way to speed up
the Find A Record process? I can't use OLE because it
seems to not take in my calculated fields very well.
Thanks.

Reply to this Message

Cindy M -WordMVP- - 05 Jul 2004 10:04 GMT

Hi Jordan,

> I'm having problems with a ton of my documents merging
> with Access. I ended up having to switch to DDE linking
> instead, except that now when trying to search for a
> record it takes forever. Is there any way to speed up
> the Find A Record process? I can't use OLE because it
> seems to not take in my calculated fields very well.

How many records does the data source contain?

Why are you searching a record? And in what interface (the
merge document, the Recipients dialog box, other)?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

:-)

Reply to this Message

Jordan - 05 Jul 2004 18:38 GMT

We have something like 2500 records. I have the
documents linked to my main query (that includes
calculations) that includes all of my records. We need
to search because we don't want to merge all of the
records to the printer, just print the current record we
need. We're using the Find Record button on the toolbar,
once it's merged. All of the documents are saved with
the merge information, so we only see the Recipients box
the one time when I set them up with the merge data.

>How many records does the data source contain?
>
[quoted text clipped - 12 lines]
>
>.

Reply to this Message

Cindy M -WordMVP- - 06 Jul 2004 09:21 GMT

Hi Jordan,

Mmmm. I think, in this case, your best hope for speeding
things up is to choose the record in the original query (set
criteria).

Next best would probably be to set the criterium in Word.
- In the Recipients list, click the arrow next to a
field name, choose Advanced and set the criteria

- Faster (due to building the Recipients list) would be
to show the Query Options dialog box via the toolbar. See
the Word 2002 section of my website's mail merge FAQ for
instructions how to get back functionality from old versions

- You could also provide your own button on a toolbar to
display the dialog box directly, using this command

Dialogs(wdDialogMailMergeQueryOptions).Show

> We have something like 2500 records. I have the
> documents linked to my main query (that includes
[quoted text clipped - 11 lines]
> (the
> >merge document, the Recipients dialog box, other)?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

:-)

Reply to this Message

Jordan - 06 Jul 2004 15:42 GMT

Using the Query Options seems to be the best way to do
it. I'll just use my base query (which contains no
criteria) and pass any criteria onto Word. Maybe you
misunderstood why we use the find, though. Every time we
open a document, we're printing a letter for a different
client, and that's why we use Find in Field to bring up
that client's record in the document.

If I have to use DDE, then using the Query Options is the
fastest way to bring up a record. But I'd rather use OLE
with the formatting options I just read about on your
page (which seems to be even faster still). Going back
to my original problem with the OLE (that I never really
tried to solve), for some reason it wasn't bringing in my
calculated fields properly. Instead, for a good number
of the records, it displays zero instead of the correct
value. Using ODBC or DDE doesn't result in this
problem. Do you have any idea of what it might be?

>-----Original Message-----
>Hi Jordan,
[quoted text clipped - 44 lines]
>
>.

Reply to this Message

Cindy M -WordMVP- - 07 Jul 2004 18:22 GMT

Hi Jordan,

> Maybe you
> misunderstood why we use the find, though. Every time we
> open a document, we're printing a letter for a different
> client, and that's why we use Find in Field to bring up
> that client's record in the document.

No, I didn't misunderstand. If you set the Query Options to
just the one client, then that's the only one you'll see
when you try to page through the records with <<ABC>>
active.

Note, also, that you apparently aren't actually EXECUTING
the mail merge, just printing out the record you're
currently viewing...

> If I have to use DDE, then using the Query Options is the
> fastest way to bring up a record. But I'd rather use OLE
> with the formatting options I just read about on your
> page (which seems to be even faster still).

You've lost me, here. To which formatting options are we
referring? OLE DB isn't faster in this regard than ODBC,
certainly. Might be faster than DDE if you have a machine
configuration that's balking at DDE conversations (I had one
of those, once).

> Going back
> to my original problem with the OLE (that I never really
[quoted text clipped - 3 lines]
> value. Using ODBC or DDE doesn't result in this
> problem.

I'd have to know more about the calculations. Are these
query expressions? Do they use any functions? User-defined
functions? NZ function? Where OLE DB is returning a zero,
what do you see with DDE? With ODBC?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

:-)

Reply to this Message