Output Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was using Macros to automate the output of queries to Excel spreadsheets.
I then decided to using VBA in order to automatically name the files based on
the julian date and type of file. The code worked fine when the D/B was on
my local PC. But when I moved the D/B to the server for others on my team to
use the code would run fine until it got to the Output line. It would not
produced the report. I would get the msgbox "no Data, no report" (an
If...then code I added if the query resulted in no data). But there is data
but a report is not produced. Is there something I'm missing?
 
I can only guess that tables you have are maybe linked vs. static. Are there
little marks to the left of the tables names? Another test, if you design on
the table does it contest your request with a dialog?

Find "Linked Table Manager" in the tools menu. Link the tables via
\\uncname\filename.mdb or fix the bad links using other simliar techniques.
 
I have no idea what you are referring to. I do not have linked tables. I am
having a problem with my VBA code executing when the DB is on the server it
does not output my query to create an excel file on the server. When
executing the same Output code from the DB that is on my local pc it output
the query to an excel file on the server. See Code below:

' Select records from the Tbl-Master Table where "LO7JJJR0" is the ACR
Naming Convention, Transfer Date is current date and Discovery Vendor Source
is "Internal DM"
DoCmd.OpenQuery "Qry - ACR4 IDM load R-LO7JJJR0", acViewNormal, acEdit

' Outputs ACR4 IDM load R-LO7JJJR0 Select Query to an Excel file (File Name
will include the Julian Date)
If DCount("*", "Qry - ACR4 IDM load R-LO7JJJR0") = 0 Then
MsgBox "No Data to Output for LO - Transfer Complete.", , "No Records to
Transfer", , "No Records to Transfer"
Else
DoCmd.OutputTo acOutputQuery, "Qry - ACR4 IDM load R-LO7JJJR0", , "U:\ BCBS
of GA\LO7" & ((DateDiff("d", Now, DateSerial(Year(Date), 1, 1)) - 1) * -1) &
"R0.xls", False
End If
' Closes query "ACR4 IDM load R-LO7JJJR0"
DoCmd.Close acQuery, "Qry - ACR4 IDM load R-LO7JJJR0"
MsgBox "IDM RETROS Transfer of Files Complete", vbOKOnly, "Transfer Complete"

All of the code executes appropriately when using the server's DB except the
"OutputTo" code. No file is created eventhough there's data to output.
 
Is that code from within the "open report" method or elsewhere?
How large is the MDB?
What is the total record count of the resulting Query?

Maybe it is a timing issue - pulling more bytes (records of data to do a
count) across the connection to your local computer - may cause delays or
timeouts that maybe have otherwise not been part of the equasion while the
solution was local.

My initial response was guessing that you were having "others" try the query
but "they" were failing to get a successful response.
 
the code is executed by clicking on a button with in a form. The resulting
record count is usually about !30 to 150 records, which isn't large at all.
Is there a solution to this problem?
 
I think you can ommit the "OpenQuery" command and ".Close" near the end; seem
only to confuse the issue; maybe my familiarity w/requirements of the
outputTo command is just 'old' though so maybe they are required.

You stated the the problem was with the ".Output" method and yet you also
stated that you get the custom MsgBox response; this seems contradictory as
they are in separate parts of the IF statement.

What is the return value of the DCount function if executed by itself, 0 or
30? If 30 - then the ELSE part should do the ".OutputTo" command - but you
claimed that is failing with which error? There seems to be a space following
the "U:\" - or no? I would suggest you store the filename into a variable and
use the variable to MsgBox the filename to the consol for inspection - maybe
something is wrong in the expression. I would also beware of spaces within
filenames - sometimes the old XLS/TXT file drivers do not understand spaces
well. Revert testing cases to a mere 'u:\myxls.xls' (under 8.3 and no spaces)

In theory having the .MDB local vs. on the file share (U:\) shouldn't affect
much.

Cheers!
 
Back
Top