I have written a networked copy of a database and I want to add VBA code to copy a query recordset to a file on the C: drive so I can use this file as a source for Word MailMerge. I cannot seem to find any code that will work.

There may other ways to solve the basic problem? . . . I need to ensure that the search generated by a user will be available for that user as a mail merge source some hours later (another user may have generated other queries using the same code in the mean time). By saving the query to the c: drive the user can have confidence that his query results are not overwritten.

I am using MS Access 2002

Help! anyone!

Ivan

If you mean you run a query and want to save the results for use later then save the output to an Excel spreadsheet. This can then be the datasource for the mailmerge document. An alternative approach would be to query the database at mailmerge time.

--

Joe

Another way to work around the overwrite problem on a shared drive is to push the results into a file with the date and time in the file name, such as OutFile031031091459 where the numbers are formatted from the datetime using Format(Now(), "yymmddhhnnss"). This will be unique to the second.

Rand

Great! you got the idea . . .but HOW do I push this data to a file using VBA code

Ivan

Why save it to a file. Append the data to a database table and mark it with some form of datestamp and a name for the search, then use this to filter what the user wants to use for the mail merge.

It would be easier to implement via a form that enables you to enter a name for the search and use this for an append query to insert the values into a table.

Simpler solution.



Sal

Thanks Sal . .I see what you are saying. My VB code is probably at beginner status, or little more,(I am mainly a general purpose IT manager by Proffesion) so I would need to spend some time to sort it. But I could get there. However perhaps I should explain the context a little more.

I am writing a DB for a local charity, its prime job is mailshots but its used for everything else as well. Its replacing a nono GUI version I wrote 20 years ago. THere is a small network with maybe 6 users/PC's. The database includes some fairly complicated searches, some pre-defined, but also the ability to create some simpler ones on the fly. All the searches create a recordset called 'QuerySearch'. They want to use mail merge. So I thought that if I could copy this recordset to a file on each PC's C: drive then a single Word 'form Letter' could be created on the network pointing to this and it could be used from any PC and get its data source from the particular file on that users PC. This would avoid any possibility of another user doing a search and overwriting the querysearch recordset with a new set of data.
I've gone too far to radicaly change what I have done. I could append the search to a table and datestamp it but I am going to be gradually adding loads more tables & copies of data. I could possibly not datestamp it but use the 'machine name' in the name of the table. But the downside of that is that separate 'Form Letter' template would be required for each PC instead of just one becasue the 'data source' would have a different name.

I know I am close to an answer but I just am not quite there . .are you sure I cannot copy this data to a file outside of the database. I know I can do it manually if I highlight the recordset and use File>Export. But how do I do it using code. A Macro does not work.

Thinking caps on !!!

Anyone else want to chip in?

Ivan

The template letter can always be saved on the network. You can also secure the file so that users can not change it. Once they open it for the mail merge, they will actualy create a new file.
You can use a form to get the users to limit their query to be used for the merge by their specific search. Also, delimit the query to show only the searches they inserted into the table. You can do this by inserting the username as a default in a database field.

what I would do is create a table called Merges and get all of their required fields plus a datestamp field, a username field and a merge name field to ease the retrieval for the user.
When the user wants to create a new recordset, they could run a query, I would let them filter it and after they filtered it the data would be inserted into the Merges table.


I have been programming for about 4 years now, but I do not code unless I have to. I try to find the easier most efficient way out. Consider this. User will be users. They will make a mistake at some point in their lives. Once that time comes (next Monday), they will come to us to ask how can they find out what they did 2 weeks/months ago.

by having the file exports on their machine you are leaving nothing to the company as far as audit trail.

the datasource name would be the same for all users. It would be a query. One that you set-up. A good data source.

Also, consider creating a report with all of the required information inside of the report. That way they can see their results before printing and the document will be better formatted.

The only code you would need is the code to get the username, and that is easy to find.




Sal

Thanks Sal, at present I am not convinced, but suspect that you know a lot more than I. The users I am dealing with are not experts,they are well meaning volunteers. So I use code to do everything I can, all they do is press buttons. (a slight exageration!)

I will give it some thought, but thanks.

Ivan

Hi Sal and anyone else. I have solved my problem using the solution offered by BASSAMSATER,Belgium, 18/9/03, to the question listed 'Transfer Text/timing problem?' as below.
    DoCmd.OutputTo acQuery, "querysearch", "MicrosoftExcel(*.xls)", "c:\winnt\temp\querysearch.xls", False, ""

I know its not a good audit trail, but no-one is asking me to provide that. It seems to work well so I intend to close this thread now. Thanks for all your interest. I suspect I shall come here again.

Ivan

Try this code for the export. But it would be easier to keep it in the database.

http://p2p.wrox.com/topic.asp?TOPIC_...earchTerms=csv

:D




Sal