Problem creating multiple pdfs from asp page via Word vba

[barleywine]

Hi,

I am trying to create multiple pdfs from an asp page where users have entered data into our database. I am using the following VBA to create prn files, which I can then run through Acrobat distiller. I keep getting the message "Word has insufficient memory. You will not be able to undo this action once it is complete. Do you want to continue?"

I have followed advice and turned off revision tracking, as apparently Word doesn't clear its undo buffer when you are running many actions from VBA, but the code below still stops, and my temp file on my hard disk is filling up with .mso files and temp files which don't disappear when the VBA crashes.

Any ideas please?

Sub testLPs()
Dim w As DAO.Workspace
Dim db As Database
Dim q As QueryDef
Dim r As Recordset
Dim d As Word.Document
Dim CurrentReport As String
Dim ReportName As String
Set w = DBEngine.Workspaces(0)
'Open the database and get a list of UKListIDs
Set db = w.OpenDatabase("v:\2002reporting.mdb&quot
'Set q = db.CreateQueryDef("", "SELECT UKList.UKListID FROM UKList ORDER BY UKList.UKListID&quot

'Test with a few
Set q = db.CreateQueryDef(&quot;&quot;, &quot;SELECT UKList.UKListID, UKList.Name FROM UKList WHERE (((UKList.UKListID)<=30)) ORDER BY UKList.UKListID&quot

'Open the recordset
Set r = q.OpenRecordset
Documents.Add
While r.EOF = False
CurrentReport = r.Fields(0)
ReportName = r.Fields(1)

If ActiveDocument Is Nothing Then Documents.Add
ActiveDocument.TrackRevisions = False
Set d = Documents.Open(&quot;

http://ukbiodiversity/asp/2002_LP_Report_Direct.asp?UKListID=&quot;

& CurrentReport)

'Set the page to landscape
ActiveDocument.PageSetup.Orientation = wdOrientLandscape


'Turn background printing off
With Options
.PrintBackground = False
End With

d.PrintOut outputfilename:=&quot;D:\LPPDFs\IN\UKListID&quot; & CurrentReport & &quot;.prn&quot;, printtofile:=True
d.Close SaveChanges:=wdDoNotSaveChanges
Set d = Nothing


r.MoveNext
Wend

'Close everything and clean up
r.Close
q.Close
Set r = Nothing
Set q = Nothing
db.Close
Set db = Nothing
Set w = Nothing
End Sub


Cheers,

Barleywine