Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 31st, 2003, 11:06 AM
Registered User
 
Join Date: Oct 2003
Location: ipswich, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default code to copy a query recordset to a file

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
Reply With Quote
  #2 (permalink)  
Old October 31st, 2003, 11:18 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old October 31st, 2003, 11:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old October 31st, 2003, 11:53 AM
Registered User
 
Join Date: Oct 2003
Location: ipswich, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Ivan
Reply With Quote
  #5 (permalink)  
Old October 31st, 2003, 12:37 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old October 31st, 2003, 04:17 PM
Registered User
 
Join Date: Oct 2003
Location: ipswich, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old October 31st, 2003, 05:17 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old October 31st, 2003, 05:49 PM
Registered User
 
Join Date: Oct 2003
Location: ipswich, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old October 31st, 2003, 06:15 PM
Registered User
 
Join Date: Oct 2003
Location: ipswich, , United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old October 31st, 2003, 06:50 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query from Recordset into another Recordset kamrans74 Pro VB Databases 5 March 5th, 2007 04:17 PM
Copy Query rsm42 ASP.NET 1.0 and 1.1 Basics 2 January 24th, 2007 02:11 PM
Macro to copy data from one file to a 2nd file chadpodsednik Excel VBA 1 October 29th, 2004 10:40 AM
How can I create a query to database file by code. marksu Pro VB Databases 0 April 17th, 2004 05:55 AM
MS Access, VBA code to copy recordset Ivan Classic ASP Databases 1 November 1st, 2003 05:52 PM



All times are GMT -4. The time now is 04:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.