Solved

Query content disappears when docmd.outputto code is run

Posted on 2008-10-08
11
595 Views
Last Modified: 2012-05-05
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




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 Function

Open in new window

0
Comment
Question by:-Juddy-
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 22693936
Change the variable "file" to "myFile". I believe that "file" is a reserved word in Access.

Next, putthe word STOP in your recordset, just above the " query = RSReports.Fields(0).Value" lne.
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.
0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22700797
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701128
Is this line getting a value from the recordset:

RSReports.Fields(0).Value

Do you have permission to write to teh file location?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Author Comment

by:-Juddy-
ID: 22701174
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.
0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22701177
The RSReports.Fields(0).value line is populated correctly
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701194
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701206
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

Open in new window

0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22701221
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701235
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("ReportName")

See if that works
0
 
LVL 3

Accepted Solution

by:
-Juddy- earned 0 total points
ID: 22719165
Hi Dannywareham;

thanks for your continued input

I have now tried changing how the recordset is access as you suggested.
unfortunately there is no change in the behaviour.

any other suggestions are gratefully received.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22719299
COuld you post your database?
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now