Slow Access Report Generation

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
shrimpforkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shrimpforkAuthor Commented:
@ 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
shrimpforkAuthor Commented:
*****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
Jeffrey CoachmanMIS LiasonCommented:
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
shrimpforkAuthor Commented:
@ 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
Jeffrey CoachmanMIS LiasonCommented:
<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
shrimpforkAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Have you trued setting Keep Together to No for all sections?
0
shrimpforkAuthor Commented:
@ TheHiTechCoach:  I started to but got distracted.  Re-checking this now...sorry.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
No problem. Let us know how it goes.
0
shrimpforkAuthor Commented:
I checked and there was only one sort set as keep together.  I changed it and there was no change in time.
0
Jeffrey CoachmanMIS LiasonCommented:
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
shrimpforkAuthor Commented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@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
Nick67Commented:
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
shrimpforkAuthor Commented:
@ 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
shrimpforkAuthor Commented:
@ Nick67:  I just removed Total Released and Total Shipped from the report and there was no change in the performance.  Good suggestion though.
0
Nick67Commented:
@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
shrimpforkAuthor Commented:
@ 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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
shrimpforkAuthor Commented:
@ 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
Jeffrey CoachmanMIS LiasonCommented:
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
Nick67Commented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shrimpforkAuthor Commented:
@ 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
Jeffrey CoachmanMIS LiasonCommented:
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
shrimpforkAuthor Commented:
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
shrimpforkAuthor Commented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<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
shrimpforkAuthor Commented:
I have no idea what field it is trying to autonumber.  How can I tell what field might it might be?
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<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
shrimpforkAuthor Commented:
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
shrimpforkAuthor Commented:
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
Nick67Commented:
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
shrimpforkAuthor Commented:
@ 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
shrimpforkAuthor Commented:
@ 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
Nick67Commented:
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
shrimpforkAuthor Commented:
@ 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
Nick67Commented:
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
shrimpforkAuthor Commented:
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
Nick67Commented:
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
shrimpforkAuthor Commented:
I have it in the Report Open Event....

Private Sub Report_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
0
Nick67Commented:
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
shrimpforkAuthor Commented:
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
Nick67Commented:
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
shrimpforkAuthor Commented:
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
Nick67Commented:
<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
shrimpforkAuthor Commented:
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
Nick67Commented:
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
shrimpforkAuthor Commented:
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
Nick67Commented:
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
shrimpforkAuthor Commented:
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
shrimpforkAuthor Commented:
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
shrimpforkAuthor Commented:
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
shrimpforkAuthor Commented:
Well done experts!  I hope I have divided the points fairly.  (Sorry if I cut someone short.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.