I have a code module that loops through a recordset to retrieve the names of queries to be run and the output files to be created.
I then use docmd.outputto passing the retrieved query and file name.
The problem is that the at some point the query contect is disappearing. I then receive "A query must have at least on destination field" error 3066.
The queries do not have ~ in their name and I have not compacted the database.
Code is as follows:
Dim RSLocation As Recordset
Dim location As String
Dim sqlstring As String
Dim RSReports As ADODB.Recordset
Dim query As String
Dim file As String
Set RSLocation = CurrentDb.OpenRecordset("ReportLocation")
If RSLocation.RecordCount = 0 Then
MsgBox ("No location is present in the Report Location table")
location = RSLocation.Fields(1).Value
Set RSLocation = Nothing
If Right(location, 1) <> "\" Then
location = location + "\"
'2 Move through the OutputReport table and select records with print flag set
sqlstring = "select QueryName, ReportName from OutputReport where Print= -1"
Set RSReports = New ADODB.Recordset
RSReports.CursorLocation = adUseClient
RSReports.Open sqlstring, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not (RSReports.EOF) And Not (RSReports.BOF) Then
While RSReports.EOF = False And RSReports.BOF = False
query = RSReports.Fields(0).Value
file = location + RSReports.Fields(1).Value
On Error GoTo PrintError
'3 Check to see if the file exists - if so remove (kill it)
If Dir(file) <> "" Then
'4 Run the Report
DoCmd.OutputTo acOutputQuery, query, acFormatXLS, file, False
'5 Tidy up
Set RSReports = Nothing
MsgBox "Report out Completed", vbInformation
If Err.Number = 53 Then Resume Next
Else: MsgBox (Err.Description)