MakeTableQuery table location needs to be back end

[DaveN]

I have a small database (Access 2003) located with backend data on a Nass drive (just tables) and a couple of users with front ends (forms and queries) which we update from time to time and redistribute the front end.



I have an issue with mail merge and data locations.

Currenty for producing a monthly servicing list of customers we run a query (on the front end) that collects the data but stores this in a table on the front end.

We then open up a word mail merged letter (in a directory on the Nass drive) which grabs this data. This worked fine when all PCs where called "admin" as the file location was the same.

Now each ot the PCs has different names the datasource is different for each front end so only one will work without keep redoing the datasource each time you open the letter.

Is there any way we can convert the existing make table query to store the newly made table in the back end in stead of the front end so the letter datasource will always be the backend, or will some kind of generic datapath work instead of c:\users\fredsPC\mydatabase etc

Would be interested to hear if anyone does this kind of mail merge any different.

Many thanks for any feedback

[ranman256]

Don't use make table. Use append. Empty the table,then run append query.

Dont use sspecialized paths, Use the SAME path for everyone,
C:\data\

[ssanfu]

As ranman256 pointed out, you shouldn't use Make Table queries - this causes dB bloat.. use a delete query, then an append query.
Use a make table query once to initially create the table, then use the delete & append queries.

The problem as I see it with the mail merge table on the NASS drive is that now there is only one table. Lets say you ran the append query that added names to the table for a mail merge. Just after your append query finished executing, I run MY delete query and MY append query in preparation for a mail merge. I just wiped out your data. And if Janet ran her two queries, MY data has been wiped out, so I can't do my mail merge either.

It might be easier to keep the mail merge table in the FEs, and move the mail merge word document(s) to each of the FEs. (its been a long time since I did mail merges )

You could use Word automation to change the datasource each time, but you still have the problem of the mail merge data being in a single table. How would you determine who has priority to do mail merges? You're on Monday, I'm on Tue, ....

[Micron]

if Janet ran her two queries, MY data has been wiped out,

AFAIK, the only way you can avoid this with a single table in the BE is to log user data against the records, and only delete those records for the specific user. Of course, the user id has to be part of the append. I think ranman256 is also correct; each user should be using the same path, but if that's not possible, those paths should be table values associated with the user, else you're going to need the msoFileDialogFolderPicker (or file picker).

[DaveN]

Thanks for the info, I have followed Steves advice and moved copy mail merge word docs to the front end PCs which has resolved the issue.