Mailmerge and MS Access DB Splitting

[Victor Lazlo]

I use Access 2002 (and Word 2002). There are approximately 20 users on a
network (in my unit) that access a db on the network. If i split the db then
the tables are on the network and the applications, queries etc are deployed
to each of the users' workstations. These users all avail themselves Word
documents that merge currently with a query on the network. If I split the
db then all the Word merge documents will have to be relinked to the query on
each workstation?? Would i have to relocate the word documents from the
network to each workstation? And repeat this process each time there is a
change or a new word document???
Or can I keep one specific query (for mail merge applications) on the
network where the tables are? What would be the most efficient method?

 

[Peter Jamieson]

I don't do this myself, but...
a. how is Word connecting to the data at present? Is it still using DDE?
The thing is, that if it is and if your merges are being launched from
Access, then the user already has the relevant database open and Word does
not have to deal with any security concerns at all. However, if you split
the database, then each user is actually only "opening" their front-end
database. Word would be able to connect to the queries in the front-end
without problems (I think) using DDE, but to access the backend I think it
would have to try to open the backend directly in the user's local copy of
Access, which would be undesirable.
b. if you are connecting using ODBC or OLE DB then again, the main question
is security - once you have addressed that, I don't think it matters where
either the mail merge main document or the query is stored.
c. if as a consequence of (a) you find that you have to consider modifying
your connections from DDE to ODBC/OLE DB, then there are a number of things
you need to know (let's just consider OLE DB), e.g.
- if the database is secured in any way, you will have to use a .udl or
..odc file to make the connection (I think). That .odc file also has to be
located somewhere, but as far as I know, it could be on the server. You will
also either have to embed the necessary login/password info. into the
..udl/.odc, or the user will /probably/ face a Data Link Properties dialog
box each time they open the merge document. Or you need to leave the
tables/query being used as the data source unsecured (e.g. with Admin/blank
as the login/password).
- OLE DB does not see some query types, e.g. Parameter queries (although
in fact, you can get OLE DB to use Parameter queries using VBA)
- OLE DB does not understand the old Jet SQL wildcard characters * and ?
used in LIKE clauses. It only understands the new ANSI SQL wildcard
characters % and _. So you may need to create modified copies of any queries
you are using as data sources.

Peter Jamieson