469,953 Members | 2,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,953 developers. It's quick & easy.

Word won't hold ODBC data source


I posted the message below back in February and didn't get any
information. I also wasn't able to find any documentation in MS Help,
web site, or other groups. I'm posting now with results of my
research in case anyone else might need this information.

I consulted with my MIS colleague, and he said it would be ok to have
each merge file pull the whole database when the criteria screen is
opened. His reasoning was that it doesn't take as long as copying or
moving a large file, which people often do without causing network
problems. We also found it doesn't actually pull the whole database,
it only pulls to about the middle of the S's. This is encouraging
because it suggests there's a limit on how much the ODBC will pull at
one time.

We decided to implement the change from DDE to ODBC, and I converted
each Word file and let MS create a new ODBC connection in "My
Connections". Since the ODBC doesn't open the database, the fields
have to be formatted in the Word file. I used switches in the merge
fields to format currency and date fields. There was an article I got
from another thread that shows this. It's called MS Office and VBA
Developer Mail Merge: Part I. Unfortunately I no longer have the
link, but the address at the bottom is

The date and currency switches worked fine, but I ran into trouble
with a date field that's often blank (the value of the text field next
to it is used in that case). When the value of this field was blank,
the ODBC merge showed it as all zeros. I found some instructions on
using an If - Then - Else field in such a case, but when I typed in
the code it didn't work as advertised.

Eventually I found that when the code is typed into an If - Then -
Else field, the brackets {} have to be inserted by keying Ctrl - F9.
When I did this and typed in the code and field names, it worked fine.
Here is the code

{IF {MERGEFIELD LossDate} = 0 "" "{MERGEFIELD LossDate \@
If the LossDate field value = 0, Then print "", Else print LossDate

However, it looks odd in the merge document. The If - Then - Else
field either doesn't show at all, or shows a random date in the field
space. But it works, and that's what counts.


Hi everyone,

We do several Word merges from my database each day, and the default
DDE link is noticeably slow. I'm trying to arrange ODBC links which
are much faster. Office XP, Word 2002, Access 2002.

The main problem I'm having is, when the ODBC merge file is closed
after setting criteria and saving, then re-opened, it can't find the
data source and has to be re-linked. Does anyone know how to get it
to save the criteria and the data source? Without coding, a simple
link? It does hold its data source if I save and close it without
entering merge criteria.

It occurs to me I could train my users not to save after a merge, then
it wouldn't save the criteria, and then presumably (I haven't tried
this as I just thought of it now) it would save it's link since it has
no criteria entered. But when the criteria screen is opened after
connecting the source it pulls the whole database, more than 16,000
records. This is still very fast, but on general principles I'd
rather not have it do that. That's a lot of network traffic, and the
database is growing fast, and it could cause problems as it gets
bigger, or am I wrong about this?

The other thing is the date and currency fields are not correctly
formatted, but I did find an article that tells how to fix this.
However, the whole issue will be moot if I can't arrange it so my
users can open the Word merge file, do the merge, and close it like
they normally do.

Any help is appreciated - I've been searching this group and help with
no results on the link issue

Thank you,
Nov 12 '05 #1
0 2024

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Fernand St-Georges | last post: by
reply views Thread by Julia Baresch | last post: by
10 posts views Thread by John | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.