Solved

Slow Access Report Generation

Posted on 2012-04-10
58
858 Views
Last Modified: 2012-04-24
I have a report that is programmed to create a PDF by using the DoCmd propery (see below).  The report is generates is about a 250+ PDF document and places it on a server drive for future use.  My problem is that this report takes 7-8 minutes to create.  Looking into the report, the query for this report takes less than 2 seconds to create 3406 records.

I'm trying to figure out why it is so slow.  Any ideas to look at?


Code excerpt....................................
DoCmd.OutputTo acOutputReport, "rptPanelElevationShipmentSummary-COLOR", acFormatPDF, "J:\Access DataBase\Temp\Elevation-Shipment-Summary-" & Me.JobNumber & ".pdf", False
0
Comment
Question by:shrimpfork
  • 30
  • 12
  • 8
  • +1
58 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
What event is this code being run on please?
If this is being run for each record, (on the Detail Format event) then I am not surprised by the length of time.
If the Report is somewhat complex, then this will also effect this...

How long the query takes to run is irrelevant because there is no code on the query, and the query does not have to "render" and graphical elements.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Also note that this appears to be sending the PDF's to a Network drive, so the network performance is also a factor here...
0
 
LVL 21
Comment Utility
There are several thing that can slow the report generation.  The biggest issue I find for speed is with pagination.  That is Access trying to figure out what will fit on a page.

In the order I would look for issues:

1) Have keep together set to yes.  Try turning this off for all sections.

3) Sub reports.  There are several things that can make sub reports low things down.   Also check the Keep toher in the sub report sections.

3) the record source is sorts the data and then the report sorts the data in a different order.
.

Boyd Trimmell, Microsoft Access MVP
0
 

Author Comment

by:shrimpfork
Comment Utility
@ boag2000:  There is no code in the report.  (Nothing running in the Detail Format).  I am running that code in a button on a form.  The report is very basic, but has 4 levels of sorting and grouping.  I'll also test to save it to the local machine rather than the server and see if that changes it.

@ TheHiTechCoach:  There is no subreport.  I'll check into the "keep together" settings for the sections.
0
 

Author Comment

by:shrimpfork
Comment Utility
*****MORE INFORMATION*****

I have changed it to save the PDF to the local workstation. No change in time. I noticed that it does not save the file to the location until the entire report is created. I noticed that it take 2m:10s before it starts to list the page progress. It is doing something before it even starts to output page 1.

(I have attached the report that is generated.)

Is it a sorting thing? The query for the report creates 7 Sort Fields that I am using in the report Sorting and Grouping to properly sort the data. (there is a7 field to help with the alpha numeric sorting for elevation numbers and mark numbers).
Elevation-Shipment-Summary-10018.pdf
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
The I am confused,
<The report is generates is about a 250+ PDF document >
250+ what?
250+ Pages or 250+ documents?
I am confused as to how it would create 250 files...
Because in looking at your PDF, it is 267 *Pages*.
So it seems that your 250+ number is actually the number of Pages, (not files)
Please confirm...

Also note that your PDF is almost 1MB in size.
If your system is creating a file of this size, then yes, this would take quite a bit of time.
<has 4 levels of sorting and grouping>
Again, another hit on resources...

There also seems to be some conditional formatting in there as well.

All in all, this may just be "What It Is"...


Lets see if TheHiTechCoach has any more tricks up his sleeve...


;-)

JeffCoachman
0
 

Author Comment

by:shrimpfork
Comment Utility
@ boag2000:  Sorry a little bit of detail was missing, yes, you assumed correctly 250+ pages.  (I typed pages in my head, my fingers did not respond.)  Yes there is some conditional formatting, but not much.  Should I move this formatting into code?  I wonder why it takes 2 minutes doing something before it creates the output for page 1.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<Should I move this formatting into code?>
*No*
Code will be even slower...

I only mentioned it as possibly another contributing factor to the performance...

<I wonder why it takes 2 minutes doing something before it creates the output for page 1.>
Not sure without a sample of this database to examine.
But in most cases Access must run though the entire report *Twice* to get an accurate page count.
So again, with a complex report (4 grouping levels, Conditional formatting, Thousands of records,) ...it may just take this long...

Just To be clear, if you open the report on its own (not by the button) how long does it take to display?
0
 

Author Comment

by:shrimpfork
Comment Utility
I removed the conditional formating, no change in time.

I ran the report on it own, (without exporting to a PDF) and it took about half the time to open to the first page, but it takes a long time to skip to the last page of the report.  So in all it is about the same time to "view all pages".
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Then it looks like there are no real potential gains in performance to be made here...

Again, lets see if TheHiTechCoach chimes back in...

Jeff
0
 
LVL 21
Comment Utility
Have you trued setting Keep Together to No for all sections?
0
 

Author Comment

by:shrimpfork
Comment Utility
@ TheHiTechCoach:  I started to but got distracted.  Re-checking this now...sorry.
0
 
LVL 21
Comment Utility
No problem. Let us know how it goes.
0
 

Author Comment

by:shrimpfork
Comment Utility
I checked and there was only one sort set as keep together.  I changed it and there was no change in time.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
TheHiTechCoach,

Thanks for coming back in on this...

I really hate to Say: "that may just be the way it is"
...every time I say this, another expert post something that speeds thing up significantly...
;-)

Just want to make sure all the bases are covered...

;-)

Jeff
0
 

Author Comment

by:shrimpfork
Comment Utility
The frustrating thing is that we just got new super computers and this report is still a dog.  The new computer did not have any change on it.  (We don't have a lack of workstation resources.)
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 200 total points
Comment Utility
@Jeff,  glad I can assist.

<<I checked and there was only one sort set as keep together.  I changed it and there was no change in time.>>
So all the sections are not set to Keep together = No and not speed increase?

To make sure I understand the setup:
1) The database is split into a front end and back end.
2) The front end i local on the PC's hard drive

If the above is not correct then that needs to be fixed first.

If the speed still does not increase then base the report of a local temp table. Since the query runs really fast the building of the local temp table should be fast. The report base3d on local data should no be fast.

Here is what I would do:

1) create a local temp table that has all the fields fo the report
2) modify the report to pull the data from the local temp table

In the report's On Open event

1) run a delete query to clear the local temp table
2) run an append query to put all the data for the report into the local temp table
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
A query that is quick to return results and a report that is slow to render even in preview says that something in the report is not very happy.
Depending on how and where  you are calculating
Total Released and Total Shipped
may be part of the problem
The domain  aggregate functions (Dcount, Dmax, ect) are NOTORIOUSLY slow
If that is how you are calculating those two items, that could be a large part of the problem.
They seem to be part of the detail section.
Ideally, you'd like them to be in a group footer, calculated by a count over group, or Sum of a control value and not by any Dcount function.
If you really want to see them where they are located now, you'd hide the controls in the group footer, and make your existing controls values equal to the hidden controls.
Or have them calculated within the report's query itself.
Or calculate them with VBA recordset code in a group header Format event.
But NEVER use DCount when you can avoid it--and don't use it repetitively (continuous forms or report detail sections) because it is dog-slow

As a test, remove Total Released and Total Shipped from the report.
Then see how fast it renders.

If it is a lot faster, then let us know how those fields are being calculated
0
 

Author Comment

by:shrimpfork
Comment Utility
@ TheHiTechCoach:  I'm looking at your last comment.  Yes it is a split DB with the front end located on the local workstations and the BE on a NAS server.  Yes, I have all the Group and Sort By set as Keep Together = NO.

(Please clarify that you asked me in the last post that it was NOT set as Keep Together = NO)

I'm looking at theballance of your suggestion now.  Just want to be clear on the Keep Together setting.


@ Nick67:  I am looking into your comment now.  I'll get back.
0
 

Author Comment

by:shrimpfork
Comment Utility
@ Nick67:  I just removed Total Released and Total Shipped from the report and there was no change in the performance.  Good suggestion though.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@shrimpfork

Are you able to post a working mdb sample?
THTC suggested <The biggest issue I find for speed is with pagination>

Somewhere in there, you have a (some) Force New Page setting(s).
For troubleshooting, set them to NONE.

It'll mess up the formatting--but if it renders really quickly, you'll know you are up against something you may not be able change.
If it is still slow without any Force New Page settings, then I am not sure what could be wrong without a sample.
0
 

Author Comment

by:shrimpfork
Comment Utility
@ Nick67:  I am unable to post the Database, it is very complex and is reading certain things to detect and no work if it is copyed from the server.  I'll check the force new page settings and see what that does.
0
 
LVL 21
Comment Utility
I do mean to set Keep Together to No for all sections.

IMHO, it sounds like you have done all the tweaks that can be done to teh report's design.

It this were mine, I would use a local temp table.  I find that generally always gets the best performance with a JET/ACE back end.
0
 

Author Comment

by:shrimpfork
Comment Utility
@ TheHiTechCoach:  A local temp table might be good.  I am trying to figure out the best way to handle this.  Is there a good link with proper usage there of?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
Comment Utility
A temp table will increase the "Perceived" speed of the report.
By doing all the "Heavy Lifting" before the report is needed (Creating the temp table), you then use the temp table to run the report.

The basic procedure is this:
Decide when you want to create the temp table...
This is important because the temp table needs to reflect an accurate picture of the most recent data.
Creating it to soon, you may miss new/updated records.
Creating it  too late (just before the report is run) will make the report appear "Slow"
If the data does not change all that often, you can do this on the Open event of your startup form.
    CurrentDB.Execute "YourMakeTableQuerySQL", dbfailonerror


Save a copy of your query.
Open this copy in design view and click the "make table" button
Select a name for the "Temp table"
Save the query
Open the report in design view and change the recordsource to the Temp Table Name

Close the DB.

Now when you open your db, the startup form will load, running the code that creates the temp table.
Now with the temp table created, the report should open a lot faster.

These are just the broad strokes.

You may need to tweak this by using code to delete the existing temp table as a first step.

I am sure the other experts can flesh this out...


;-)

Jeff
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
When I do stuff like that, I create the 'temp' table ONCE.
Then I empty it of all records, and insert new ones when the report runs.

That way I am not creating/destroying tables--which has proved problematic for me over time
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 200 total points
Comment Utility
I posted the basic steps above.

Here they are again:

Here is what I would do:

1) create a local temp table that has all the fields fo the report

A simple way to create the local temp table is to open the current query for the report and chsnge it to a make table query.  Run it to create the table table.

Next change it to a append query and save it to  used in step 4 below.

2) modify the report to use a query based on the local temp table

In the report's On Open event

3) run a delete query to clear the local temp table
4) run an append query to put all the data for the report into the local temp table
0
 

Author Comment

by:shrimpfork
Comment Utility
@ TheHiTechCoach:  Yes I saw you post that first, sorry.  I'm keeping that in mind for the point assignment, depending on how this goes.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Nick,
<When I do stuff like that, I create the 'temp' table ONCE.>
I thought of that too, but I was unsure if the fields might change...

Jeff
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:shrimpfork
Comment Utility
I'm getting back on this today.  I am going to be looking at teh temp table option.  I'm sure that I'll have some questions.

@ TheHiTechCoach:  You mentioned "I find that generally always gets the best performance with a JET/ACE back end."  Can you expand on this a little?  I would love to increase the overall performance, as I have been struggling with it for a while.
0
 

Author Comment

by:shrimpfork
Comment Utility
Ok, first question:  Making the TempTable using the make table returns "Resultant table not allowed to have more than one AutoNumber field."  I do know what the means, how do I get around it?
0
 
LVL 21
Comment Utility
<<Ok, first question:  Making the TempTable using the make table returns "Resultant table not allowed to have more than one AutoNumber field."  I do know what the means, how do I get around it?
>>
I use use the Val() on the field. This will trick Access into not thinking the datatype should be an autonumber.

<<@ TheHiTechCoach:  You mentioned "I find that generally always gets the best performance with a JET/ACE back end."  Can you expand on this a little?  I would love to increase the overall performance, as I have been struggling with it for a while.>>
The ideas is that anytime you can avoid pulling lots of data over the network you will be faster. First pull down just the data  you will need from the network to local tables. Now as you process the data it will be faster since it is local and just the records you need.
0
 

Author Comment

by:shrimpfork
Comment Utility
I have no idea what field it is trying to autonumber.  How can I tell what field might it might be?
0
 
LVL 21
Comment Utility
<<I have no idea what field it is trying to autonumber.  How can I tell what field might it might be?>>
Access is trying to use the same datatype as the field in the table. It is probably fields that are primary keys.  If you are not sure, you will need to look at the table design.  

FWIW: I use a naming conversion so they are easy to identify.

Converting the query to a make table is only a tool. You can always manually create the temp table with just the fields you need.    

FWIW: I rarely show the user the autonumber fields. Because of this I do not normally include them in the query.   To help with performance, only include the field you must have. Avoid using Select Tablename.* from TableName
0
 

Author Comment

by:shrimpfork
Comment Utility
I am back working on this again. I'm trying to figure out the base approach on this and would like a recommendation on how to..

3) run a delete query to clear the local temp table
4) run an append query to put all the data for the report into the local temp table.

Do I need to create two more queries or is there a way to do this within the code?
0
 

Author Comment

by:shrimpfork
Comment Utility
Uh oh...
I just found my dog in this report.  I completely forgot that I am calling code from within a field in the report to get the "Pallet No." for each record.  This code is running for every line of the detail to create a list of the pallets the mark number is in....
(I'm not sure if I can speed this up.)

Public Function CombinePallets(PanelElevationID As String) As String
    On Error GoTo Err_CombinePallets
    
    Dim sReturn As String
    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    
    sSQL = "SELECT tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "FROM tblPallet INNER JOIN tblPanelPackingList ON tblPallet.PalletID = tblPanelPackingList.PalletID " & _
            "GROUP BY tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "HAVING (((tblPanelPackingList.PanelElevationID) = " & PanelElevationID & ") And ((tblPallet.Packaged) = -1)) " & _
            "ORDER BY tblPallet.PalletNumber;"

    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset(sSQL, dbOpenForwardOnly)
    
    Do While Not rst.EOF
        sReturn = sReturn & ", " & Nz(rst!PalletNumber)
        rst.MoveNext
    Loop
    
    'Trim Leading Comma
    sReturn = Mid$(sReturn, 3)
    
Exit_CombinePallets:
    On Error Resume Next
    
    'Function Return
    CombinePallets = sReturn
    
    'Clean up
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
    
    Exit Function

Err_CombinePallets:
    sReturn = "#Error"
    Resume Exit_CombinePallets
End Function

Open in new window

0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 200 total points
Comment Utility
Many things!

1.  What is this field returning?  It seems to be a string of tblPallet.PalletNumber
Then why are you selecting all these fields?
SELECT tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged
Perhaps they are required by the "group by" clause?

2.Set db = DBEngine(0)(0)
This isn't great.  Set db = CurrentDB is better.
Even better yet is to have in Report_Open
Public db as database
Set db = CurrentDB
Then you only instantiate db ONCE and keep it while the report is open.

3. Save this out as a query
    sSQL = "SELECT tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "FROM tblPallet INNER JOIN tblPanelPackingList ON tblPallet.PalletID = tblPanelPackingList.PalletID " & _
            "GROUP BY tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "HAVING (((tblPanelPackingList.PanelElevationID) = " & ReturnPanelElevationID() & ") And ((tblPallet.Packaged) = -1)) " & _
            "ORDER BY tblPallet.PalletNumber;"
and remove the unneeded fields if possible

Note the change ReturnPanelElevationID()

Add a Module, and a Public Variable
Public ThePanelElevationID as Long

Add a wrapper function in that module
Public Function ReturnPanelElevationID() as long
    ReturnPanelElevationID = ThePanelElevationID
End Function


Now the code gets simpler and probably faster because the query can be optimized by the db engine.  Let's say you called the query qryPalletString

In a new module
Public ThePanelElevationID As Long

'Add a wrapper function in that module
Public Function ReturnPanelElevationID() As Long
    ReturnPanelElevationID = nz(ThePanelElevationID,0)
End Function

Open in new window


and in your report's module
Public db As Database

Private Sub Report_Open(Cancel As Integer)
Set db = CurrentDb
End Sub

Public Function CombinePallets(PanelElevationID As String) As String
    On Error GoTo Err_CombinePallets
    
    Dim sReturn As String
    Dim rst As Recordset
    ThePanelElevationID = PanelElevationID
    Set rst = db.OpenRecordset("qryPalletString", dbOpenForwardOnly)
    
    Do While Not rst.EOF
        sReturn = sReturn & ", " & Nz(rst!PalletNumber)
        rst.MoveNext
    Loop
    
    'Trim Leading Comma
    sReturn = Mid$(sReturn, 3)
    
Exit_CombinePallets:
    On Error Resume Next
    
    'Function Return
    CombinePallets = sReturn
    
    'Clean up
    rst.Close
    Set rst = Nothing
   
    Exit Function

Err_CombinePallets:
    sReturn = "#Error"
    Resume Exit_CombinePallets
End Function

Open in new window


That ought to perform better
0
 

Author Comment

by:shrimpfork
Comment Utility
@ Nick67:  Wow, great input.  Let me digest and give this a try.  Thank you.  I'm sure I might have a question or two...
0
 

Author Comment

by:shrimpfork
Comment Utility
@ Nick67: I am using the CombinePallets() function for a few differant reports.  How would this work if I move the function to the report module?

I would like to test the db = Current DB set in the OnOpen and leave the Public Function CombinePallets() in the general module folder area.  Is this possible.  I'm not sure how the DB would carry from the report module to the other module.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
You could put
Public db As Database
as a global variable in a module along with the wrapper function

Public ThePanelElevationID As Long
Public db As Database

'Add a wrapper function in that module
Public Function ReturnPanelElevationID() As Long
    ReturnPanelElevationID = nz(ThePanelElevationID,0)
End Function



There are things to be aware of when you use global variables.
One is that you will need a Report Close event for
db.close
set db = nothing


because, as a global variable db would hang around after you used it in the report.
In an ideal situation, you split the app into a front-end and back-end, and distribute the front-end to each user on their own computer.  If you DON'T do that, and multiple users use the same front-end, you can encounter issues where multiple users alter global variables, causing each other grief.
0
 

Author Comment

by:shrimpfork
Comment Utility
@ Nick67:  The PanelElevationID is not making it over to the wraper function...
Public Function ReturnPanelElevationID() As Long
    ReturnPanelElevationID = Nz(PanelElevationID, 0)
End Function

(BTW:  I do have this DB split...no worries there.)
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Careful!
You are missing some links in the chain

bad:
ReturnPanelElevationID = Nz(PanelElevationID, 0)
good:
ReturnPanelElevationID = nz(ThePanelElevationID,0)

You need a global variable outside the report
Public ThePanelElevationID As Long
in an independent code module.

You need to set that value in CombinePallets
ThePanelElevationID = PanelElevationID

and THEN the wrapper function will retreive it in your query's criteria
0
 

Author Comment

by:shrimpfork
Comment Utility
Opps.  Ok fixed that.  Now I discovered that the Set db = CurrentDb is not making it to the Public Function CombinePallets(PanelElevationID As String) As String
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
db needs to exist in a module (not the report's module)
Set db = CurrentDb needs to be done in the Report_Open event
0
 

Author Comment

by:shrimpfork
Comment Utility
I have it in the Report Open Event....

Private Sub Report_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
If db is set then
MsgBox db.Name should return something.

so try
Private Sub Report_Open(Cancel As Integer)
    Set db = CurrentDb
    MsgBox db.Name

    ...

and

Public Function CombinePallets(PanelElevationID As String) As String
MsgBox db.Name


While testing, comment out any OnError statements.
You want to SEE it go BANG!
0
 

Author Comment

by:shrimpfork
Comment Utility
I got the valid path and filename on the first msgbox and a run-time error '91' (Object variable or With block variable not set) on the second msgbox.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Ok.

So what module is
Public db As Database
in?

It needs to be in a code module, and not the report's module.
The fact the first one works suggests that the statement is not in the right module
0
 

Author Comment

by:shrimpfork
Comment Utility
It is located in the report's module....

Option Compare Database
Option Explicit

Public db As Database


Private Sub Report_Close()
db.Close
Set db = Nothing

End Sub

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
End Sub

Private Sub Report_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<It is located in the report's module....>

and since, I infer, CombinePallets is not, that's the problem
In the report's module, Public means that it is available to all functions and procedures in that report's module

CombinePallets() is not going to be able to access it there.
Most likely--I am inferring since you use that function in multiple reports
Public db As Database
Could go at the top of the module CombinePallets() is in--providing that CombinePallets() is NOT a function defined in some other report or form and made Public, but is in a code module
0
 

Author Comment

by:shrimpfork
Comment Utility
I moved the Public db As Database to the top of the Module that holds the CombinePallets().  It still has the same result.  I think that as soon as the Report_Open() completes, the variable db is lost.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Remember, you DON'T want a db.close or set db = Nothing statement until the Report_Close() event.  Order of events is Open, ReportHeader, any GroupHeaders, Detail, GroupFooters, ReportFooters and Close.

You can check each event in turn with msgbox db.name to see what is going on.

You DON'T want those two statements (db.close or set db =nothing) in CombinePallets() either!
0
 

Author Comment

by:shrimpfork
Comment Utility
Ok, I got it running properly with the latest suggestions.  Not as big of a performance change that I was hopping for.  The item that changed the performance the most was setting DB Current instead of DBEngine(0)(0), but it still takes way to much time.

I need to look back at geting the data temporatly to the front end to run the query for each record.  So to kind of go back to the previous suggestions (but pulling a differant set of data), I need to create two temp tables that is used in the query in the CombinePallets(), the tables tblPanelPackingList and tblPallet.

Thank you for hanging in there with me experts....I'm getting it figured out.  I'm sure I'll have another question.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
What you may also want to look at doing is creating a 'temp table' to hold values of PanelElevationID and the resulting CombinePallets.  You could then set that routine off in Report_Open, and then just have a join to the 'temp table' to retrieve the CombinePallets value.

If it is slow, you may also have indexing issues
tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged
should maybe all have indexes on them, if they don't already.

I am also not a heavy duty SQL guy and I don't know your data--but your SQL seems a bit tortured--and that may be slowing it up, too

"SELECT tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "FROM tblPallet INNER JOIN tblPanelPackingList ON tblPallet.PalletID = tblPanelPackingList.PalletID " & _
            "GROUP BY tblPanelPackingList.PanelElevationID, tblPallet.PalletNumber, tblPallet.Packaged " & _
            "HAVING (((tblPanelPackingList.PanelElevationID) = " & PanelElevationID & ") And ((tblPallet.Packaged) = -1)) " & _
            "ORDER BY tblPallet.PalletNumber;"

Given that you want the Ordered pallet numbers to concatenate, why isn't your original SQL
"SELECT  tblPallet.PalletNumber " & _
            "FROM tblPallet INNER JOIN tblPanelPackingList ON tblPallet.PalletID = tblPanelPackingList.PalletID " & _
            "Where (((tblPanelPackingList.PanelElevationID) = " & PanelElevationID & ") And ((tblPallet.Packaged) = -1)) " & _
            "ORDER BY tblPallet.PalletNumber;"

Does this not give the right result set?
0
 

Author Comment

by:shrimpfork
Comment Utility
Ok, I have created my delete queries and apend queries to handle the data for the temp tables.

I think I have done this before, but I would like to know what is the best way to run these in VB?

(I am putting them in the Report_Open() module.)


@ Nick67:  I just saw your comment.  I will look at that also.
0
 

Author Comment

by:shrimpfork
Comment Utility
Here is what I put in the Report_Open() module...

    DoCmd.SetWarnings False
   
    'Run Delete Queries
    DoCmd.OpenQuery "qryModDelTempPallet"
    DoCmd.OpenQuery "qryModDelTempPanelPackingList"
   
    'Run Append Queries
    DoCmd.OpenQuery "qryModAppendTempPallet"
    DoCmd.OpenQuery "qryModAppendTempPanelPackingList"
   
    DoCmd.SetWarnings True
0
 

Author Comment

by:shrimpfork
Comment Utility
Well Experts, with your help I have cut this report down to 1m: 07s.  Thank you, I've learned a lot this round.

Now to divy points...(the hard part).
0
 

Author Closing Comment

by:shrimpfork
Comment Utility
Well done experts!  I hope I have divided the points fairly.  (Sorry if I cut someone short.)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

17 Experts available now in Live!

Get 1:1 Help Now