Query content disappears when docmd.outputto code is run
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.
Using Access2007
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.
Using Access2007
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
DoCmd.Hourglass True
DoCmd.SetWarnings False
Set RSLocation = CurrentDb.OpenRecordset("ReportLocation")
If RSLocation.RecordCount = 0 Then
MsgBox ("No location is present in the Report Location table")
Exit Function
End If
RSLocation.MoveFirst
location = RSLocation.Fields(1).Value
RSLocation.Close
Set RSLocation = Nothing
If Right(location, 1) <> "\" Then
location = location + "\"
End If
'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
RSReports.MoveFirst
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
Kill file
End If
'4 Run the Report
DoCmd.OutputTo acOutputQuery, query, acFormatXLS, file, False
RSReports.MoveNext
Wend
'5 Tidy up
RSReports.Close
Set RSReports = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
MsgBox "Report out Completed", vbInformation
Exit Function
PrintError:
If Err.Number = 53 Then Resume Next
Else: MsgBox (Err.Description)
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True
End FunctionASKER
Hi dannywareham;
Thanks for the reponse. I have traced the code extensively and the expected values are getting to the variables.
When tracing I track down the problem to the docmd.outputto statement.
before running this the queries are intact. after running this the queries disappear.
Any other advice would be gratefully recieved.
Thanks for the reponse. I have traced the code extensively and the expected values are getting to the variables.
When tracing I track down the problem to the docmd.outputto statement.
before running this the queries are intact. after running this the queries disappear.
Any other advice would be gratefully recieved.
Is this line getting a value from the recordset:
RSReports.Fields(0).Value
Do you have permission to write to teh file location?
RSReports.Fields(0).Value
Do you have permission to write to teh file location?
ASKER
hi dannywareham.
The file location is writeable. The first time the code runs all files are correctly written to this location. the 2nd time through it fails as some of the queries design has disappeared (i.e. the queries are there but when you look at them the sql consists "select ;" and in design mode the query grid is empty.
The file location is writeable. The first time the code runs all files are correctly written to this location. the 2nd time through it fails as some of the queries design has disappeared (i.e. the queries are there but when you look at them the sql consists "select ;" and in design mode the query grid is empty.
ASKER
The RSReports.Fields(0).value line is populated correctly
But you're not writing to or removing a query object.
How would you see the design grid view with just SELECT;?
If you mean the recordset gets the query correctly first time, and then doesn't, it's a problem with the recordset.
How would you see the design grid view with just SELECT;?
If you mean the recordset gets the query correctly first time, and then doesn't, it's a problem with the recordset.
Try this:
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
DoCmd.Hourglass True
DoCmd.SetWarnings False
Set RSLocation = CurrentDb.OpenRecordset("ReportLocation")
If RSLocation.RecordCount = 0 Then
MsgBox ("No location is present in the Report Location table")
Exit Function
End If
RSLocation.MoveFirst
location = RSLocation.Fields(1).Value
RSLocation.Close
Set RSLocation = Nothing
If Right(location, 1) <> "\" Then
location = location + "\"
End If
'2 Move through the OutputReport table and select records with print flag set
sqlstring = "select QueryName, ReportName from OutputReport where Print= -1"
RSReports.Open sqlstring, cnn 'uses last known connection
Do Until RSReports.EOF
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
Kill file
End If
'4 Run the Report
DoCmd.OutputTo acOutputQuery, query, acFormatXLS, file, False
RSReports.MoveNext
Loop
'5 Tidy up
RSReports.Close
Set RSReports = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
MsgBox "Report out Completed", vbInformation
Exit Function
PrintError:
If Err.Number = 53 Then Resume Next
Else: MsgBox (Err.Description)
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Function
ASKER
Hi Dannywareham;
This is what I cannot understand - why is the query design disappearing?
As you say - I am not writing or removing the query object. before running the code the query is there after running the code - i go to sql view and I see "Select ;". if I go to the query grid I see no fields in the grid for the queries that have disappeared.
The queries are still there in name alone - but the sql and /or design has gone!
When tracing the code I have narrowed it down to the docmd.outputto statement - but cannot see why this is happening or what I need to do to fix it.
Hope this helps to clarify.
This is what I cannot understand - why is the query design disappearing?
As you say - I am not writing or removing the query object. before running the code the query is there after running the code - i go to sql view and I see "Select ;". if I go to the query grid I see no fields in the grid for the queries that have disappeared.
The queries are still there in name alone - but the sql and /or design has gone!
When tracing the code I have narrowed it down to the docmd.outputto statement - but cannot see why this is happening or what I need to do to fix it.
Hope this helps to clarify.
It may be this line:
file = location + RSReports.Fields(1).Value
as "file" gets killed later on.
Change the number to the name of the query field:
file = location + RSReports.Fields("ReportNa
See if that works
file = location + RSReports.Fields(1).Value
as "file" gets killed later on.
Change the number to the name of the query field:
file = location + RSReports.Fields("ReportNa
See if that works
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
COuld you post your database?
Next, putthe word STOP in your recordset, just above the " query = RSReports.Fields(0).Value"
Run your code and it will stop.
Press F8 to step through teh code.
Hover your mouse over your variables to ensure that the expected values are being passed to them.