Hello again, Colleagues All,
I have a sticky little problem to which I require an urgent solution. I am not sure whether this is an Access problem or an MS Word problem, but I hope that one of you bright Access guys out there has used Access/Word for mail merge purposes and can address my problem.
I have a front-end database which is used to communicate with a back-end database, so all of the tables are links. I have created a query in the front-end to select one or more records and generate letters from them. However, when I open Word (all Office components are 2003) and go to attach the database, I get a message saying "No visible tables". Well, I can understand that Word does not understand links, but it should recognise a local query. All of the documentation I can find says "use an Access table OR QUERY as a data source". As a last resort I could use the query to populate a temporary table and delete the data afterwards, but this creates all sorts of problems in a multi-user system, where the Access "users" are actually logged in and out automatically to the server via a batch process and this does not correspond to their real user name.
Can anyone shed any light on this situation and offer a solution, please ?
After I posted my initial question, I went back to Word and opened the Mail Merge Wizard. As I said earlier, it said it could not see any visible tables. However, when one presses the Options key, one can choose Tables, Views, Synonyms, etc. The penny dropped and I thought "DUH, James, you missed something quite trivial and are wasting the time of all those clever guys round the world". You see, what all of the big DB people like Oracle, IBM, et al call Views, Microsoft calls Queries everywhere else. Anyway, I ticked Views and sure enough, i was able to select the query I had engineered for this purpose. However, MS trumped me again, saying that it could not connect to the data source. So I still have my problem, but we have moved up the ladder just a little bit.
I finally sorted out the data source problem. As is so often the case, it comes back to what MS decides to do in different versions of the same application. The default connection type in Word is ODBC. In Access it is DDE. In the mail merge document, if you do things in the right order, using the wizard, a window comes up allowing you the choice of three different modes of connection. Select DDE and it works.
However, following on from this, I have another problem, to do with the behaviour of the underlying query.
A user entering data in a form elects to send a letter to the client currently displayed in the form. A query uses the ID information in the form, to select the required data to go into the various fields in the document. The user clicks on a button which opens the Word mail-merge file and hopefully displays the data in the query. This works fine for a single user. It is also the case that when multiple users launch the same query, they will see data relevant to their open form. However, Word does not know anything about the genesis of the data in the query and I am uncertain whether, if several users try to do the same thing at the same time, the document opened up by the user will pick up only the data in the version of the query set up by the same user. One might argue that this is not really an Access problem per se, but only Access users are likely to have encountered it so I am submitting it anyway.
As I intimated earlier, my problem is really a sort of crossover between Access and other Office products. I finally got a prompt and effective reply through a forum called Woody's Lounge, which focusses exclusively on Windows and Office. i can confidently recommend this forum to other Access users who need to communicate in more sophisticated ways to MS Office. WL is not a competitor to this forum in that its focus on Access is merely an Office tool and not as a platform for whole-of-business representation or specific business processes.