I need to have a way to move data from Access to Word. I'm trying to do a mail merge of sorts, but the difference is that each record needs to have its own document. These documents will be uploaded to a document imaging system/database. (It has to go this route, I can't write to the other database directly). I have code that works, but we will be creating about 5,000 documents at a time and it takes about 45 minutes to run, mostly because of the 'printing... .doc' message that pops up for every document. A second reason for its slowness is the fact I'm creating a new query 5,000, once for every record.
Can I get rid of that popup? Or is there a better way? Here is my code:
strSQL = "SELECT ID FROM tblCaseload WHERE CreationDate >= #" & datCreationDate & "#"
datTimerStart = Now()
Set rst1 = CurrentDb.OpenRecordset(strSQL)
lngCount = rst1.RecordCount
lngLoop = 0
vbPrint = MsgBox("Do you want to create the " & lngCount & " items?", vbYesNo, "CONFIRM TO CONTINUE")
If vbPrint = vbYes Then
MsgBox "Job has been cancelled.", , "Job cancelled"
''delete exsting query
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "qryCaseload" Then
I don't know of a way to get rid of the pop up, but my guess would be that it's only up while Access is busy outputting the report anyway, so I doubt it slows you down much. Rather than the whole QueryDef route, which would slow things down a bit, I use this type of thing:
Interesting idea that I will try today. As long as I can use two criteria - ID & CreationDate, it should work. You don't know how hard I looked to find something usable. I kept looking for things like "export report to Word" and got all kinds of mail merge hits, but nothing like this. I almost feel silly for not thinking of trying a form in the first place because it is so simple.