It is amazing that so much has
changed from 2000 to 2003, as the workgroup was not problem, nor was the
number of records when we were using 2000.
Yes, I can only guess but suspect that when the developers tried to
incorporate OLE DB, they probably found themselves with a lot more problems
than they bargained for and probably too little time to design a better way
through them. The vast majority of the changes occurred between Word 2000
and Word 2002, BTW.
For an OLE DB data source you really need a .udl or .odc file rather than a
DSN (which are for ODBC data sources). You don't actually have to have
either, as long as your users do not need to set up their data sources from
scratch (and you may find that they have to) - in that case, using a .odc
can make things rather easier for them. .udl s can also be used but cannot
embed a table or query name.
Whether either ODBC or OLE DB can "see" your query depends on the type of
query. For OLE DB, I have verified that a simple TOP 10 query that
references a single local table in a .mdb is visible to OLE DB, but if your
query has other complications it might well be "invisible". You obviously
need to ensure that whatever Access login you use has all the necessary
permissions to run the query successfully.
If you want to try to connect with ODBC, you usually have to ensure that
a. the database name is correctly selected when you see the tables/queries
dialog box. It can be hard to tell because you can't inspect long file names
within the database name text box.
b. you have clicked the "options" button and selected all the options in
there
Both OLE DB and ODBC connections from Word can also suffer from another
problem - if the pathnames of the access database and the workgroup security
database (mdw) are too long, you may have problems when you try to connect,
or more likely, when you try to re-open the mail merge main document. This
is because Word can only save a 255/256-character connection string, and the
way Word works with OLEDB, there is no way to keep that string short by
specifying only the settings you need either in VBA or a .odc- Word always
adds in a bunch of standard settings as well. If you encounter that problem
then I suspect that the only way through it is either to ensure that your
..mdb and .mdw are located at short path names or perhaps to create a further
..mdb which (e) has a short path name, (d) uses the same .mdw and (e) links
to your first .mdb.
Anyway, let's step through the basic approach needed to make the connection
in OLE DB /without/ setting up a .odc:
a. ensure Word Tools|Options|General|Confirm conversions at open is checked
b. start the process of connecting to a data source
c. when the Select Data Source dialog opens, select the .mdb and click
Open. A Confirm Data Source dialog box should display - probably showing
OLE DB, ODBC and DDE options.
d. Select OLE DB Database files and click OK A Data Link Properties dialog
box should display, with the COnnection tab open.
e. (The first time you do this, just so you fully understand what's going
on, click the Provider tab and ensure that "Microsoft Jet 4.0 OLE DB
Provider" is selected. Then click the Connection tab again
f. enter the name of your .mdb or select it using the ... button
g. enter the user name
h. uncheck blank password and enter the Password.
i. (If you click Test connection at this point, the connection will
probably fail. If it does not, it suggests that someone has configured some
Jet registry properties so that the workgroup security database for your
database is the default "system database". However, for the purposes of this
experiment, you should probably enter the database name as shown in the next
few steps)
j. click the All tab and double-click on the property named "Jet
OLEDB:System database". Enter the full path name of your workgroup security
file. Select the pathname and use ctrl-C to copy it to the clipboard.
k. go back to the Connection tab and click the Test connection button. If
you don't see "Test connection succeeded" then please let us know what
message you do see (and I may not be able to get you any further)
l. Click OK. For the benefit of anyone else reading this, this is the point
where it is easy to get into difficulties and which I have never understood
very well before. What happens is that Word, or OLE DB (not sure which)
tries to retrieve the list of tables and queries in the specified database.
However, what is potentially very confusing is that if the database only
contains a single visible table, the process selects that table without
tellng you, and the dialog box redisplays, but with your login info wiped
out. What's more, filling in your login info again doesn't help. But if that
is what happens, skip to step (). Otherwise, if there is more than one
visible table/query, you should see a Select Table dialog box.
m. If the query you need to connect to is not listed, we may not be able to
use this approach. But for now it may be worth completing this process and
selecting one that is listed. Click OK
n. The Data Link Properties dialog box is redisplayed. However, most of the
stuff we entered before has now been tossed away - i.e. it was only there to
allow us to select a specific database and table.
o. fill in the correct user name again, uncheck blank password again and
fill in the password again
p. click the All tab and double-click on the property named "Jet
OLEDB:System database" again. Use ctrl-v to insert the full path name of
your workgroup security file that you copied to the clipboard in step (j).
Or re-enter the pathname the hard way...
q. click Test connection again if you like. Let's hope you see "Success"
r. You now have one final choice to make. You can either check "Allow
saving password" or leave it unchecked. Either way, when you check OK, Word
should connect tot he data source. However, when you save and close and
re-open the mail merge main document, if you /do/ check this box then the
login info. will be stored in the .doc and the user should not see the Data
Link Properties dialog box. If you do not check the box, the information is
not stored and the user wil be faced with the data link properties dialog
when they re-open the mail merge main document. However, at that stage, they
should only have to enter the user name, uncheck blank password, and enter
their password - all the other info (provider name, database name, security
database name) should have been retained. But I think you need to verify
this on your own system before subjecting users to it. Also, since it
appears that the user could easily check thiis box when they open the
document and then save the document and transmit it, there may be further
security implications.
s. Click OK. If all has gone well, you should be connected to the data
source and be able to proceed inthe usual way.
Phew!
If you got that far, well done! If you still couldn't see the query in step
(m), it is still just about possible that the same thing can be done using a
..odc or VBA (i.e. I have sometimes discovered that even though the
query/table is not listed, you can still connect to it). But maybe you could
post the query code here?
If you want or need to create a .odc, let's leave that for another time.
