Fastest way to print large data (1 Viewer)

[Hello1]

Hello guys,

Im looking for the fastest way to print text data. Instead generating 20000+ pages in a report (with logos and stuff), what would take quite a while, is there a way to print text data directly from VBA? I would include only text data, logos and other images, if any, would already be on the paper. What I would like is something like maybe first 2000 pages exporting to a txt file and then to send a command outside access (maybe CMD) to print the data from that txt file to the desired port/printer, so while its printing to go on and be able to use access to send next 2000 pages to be printed on another printer. I would let the user select which printer (Port) to use, with a textbox or similar.

Im open to suggestions and if more details are necessary please let me know, thanks!

 

[theDBguy]

Have you tried to see how long it would take to save the report as a PDF?

 

[Hello1]

Hi, no, not yet. Doing so I would have to make a report? I am actually trying to avoid making a report at all

 

[Gasman]

What about a mailmerge?

 

[theDBguy]

Hi, no, not yet. Doing so I would have to make a report? I am actually trying to avoid making a report at all

Oh, I thought you already had a report, and that's how you knew it takes so long to print.

 

[arnelgp]

print any text/docs:

#If VBA7 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
Private Declare PtrSafe Function GetDesktopWindow Lib "USER32" () As LongPtr
Private Declare PtrSafe Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)

#End If
Const SW_SHOWNORMAL = 1

Public Sub PrintAnyDocument(strPathAndFileNamePlusExtension As String)

Dim dfltPrinter As String
Dim newPrinter As Object

    ' Create a new WshNetwork object to access network properties.
    'Set newPrinter = CreateObject("WScript.Network")
    'Get the name of the default printer
    'dfltPrinter = Printer.DeviceName

    'Set the default to the new printer
    'USE YOUR ACTUAL PRINTER NAME BELOW
    'newPrinter.SetDefaultPrinter ("New Printer Name")

    'Pause
    Sleep 100

    'Print the pdf document
    'USE YOUR ACTUAL DOCUMENT NAME & PATH BELOW
    Call ShellExecute(GetDesktopWindow(), "print", strPathAndFileNamePlusExtension, "", 0, 0) 'SW_SHOWNORMAL)

    'Pause
    Sleep 2000 'wait for 2 seconds - increase this if the code doesn't appear to work

End Sub

 

[Hello1]

Thanks for all the suggestions guys.
Im sorry that I wasnt specific enough, I will try to be more specific, now that I finished analyzing.
So it seems like I will have to work with a "template". On the paper they will already have the template printed out like the picture in attachment. What I have to do is to fill the template with data, so all I will need is text.
Im searching for the best way to do this because Im not yet sure in what format to export the data, how to determine the position where to place the text and each...

Maybe first to write the data in a memo filed in the database with all the formatting and then to print the memo field on to the template?
So instead "Company Name", "Client Name" (which is empty on the paper) I want to export/print actual data from the database.
In short, I am talking about a pre-printed document.
Thanks!

 

[Isaac]

I would agree with Gasman, you might look into mail merge. The old stand by ... Works better than reinventing the wheel.

 

[CJ_London]

easiest way would be to create a report - position controls so they are in the right location and remove all labels. If you can scan your image 'full size' as a jpg or png you can add it as a report 'background' - see the report picture properties - to locate the various controls. Then remove it before printing. Do test prints for one or two invoices to check alignment.

However you have a number of things you need to address.

What if the invoice has more lines than allowed for in the template?
addresses often have more than 4 lines - you only have 3 if you exclude email address
Don't know where you are but in UK/Europe - To be a legal document, invoices should include the senders company number/registered office.
There are also rules around displaying VAT information such as VAT number and rates/summary
What type of printer are you using? laser/inkjet/dot matrix? Does it have the capacity to print 20k invoices without having to restock paper?

Another way using vba would be to create a .csv file where you have algorithms to keep track of line and page count. But not sure how you would handle different font sizes.

 

[Hello1]

@CJ_London thanks for the reply. I'm not quite sure which printer is it but I do know that the capacity is 500.
The user would usually print 2k pages, not 20k at once. Yes they will have to restock (they are doing it now also), however, they will havea pop-up form which I'm planning to put before printing where they would specify the number of pages to print and from which to which page.

The template I have put is just some random one I found on the Internet.

What if the invoice has more lines than allowed for in the template?

This shouldn't be a problem as far as I'm aware for now.

The report method might be the easiest to create but I'm afraid it will take a lot more time than for example .csv which you mentioned.
I guess (can't recall right now) when printing from a report I have to wait for the printing to finish, while with a csv sending a print command with shell(notepad /pt....) should send the task to the printer to print the txt/csv file and I would be able to immediately continue using MS access to send another printing task to another printer? Only where I would lose time is the export to txt (but I guess its faster to export 2000 pages to a txt file than printing 2000)..
However, I can't confirm any of this above because I didn't test it yet so I hope there might be someone here who tested the above already?

Oh, and regarding the font, I think it will always be the same, so it should make things easier.

 

[Hello1]

I would agree with Gasman, you might look into mail merge. The old stand by ... Works better than reinventing the wheel.

@Gasman
I will check it out more, I did a little but it didn't seem like the option I'm seeking for. Thanks for your reply

 

[Gasman]

I suggested that before you posted the pic of the report.?
I'm not sure how it would deal with multiple detail lines for a parent record.? I always used mailmerge with data from just one record.
In your situation I would use a report, but then I would not be printing 2000 pages, perhaps 100 at most depending on the data.?

 

[Pat Hartman]

Do you know how long it takes to print this as a report with the graphics included? You really need to know what your benchmark is.

Some fancy printers have the ability to store "forms". You can define the form for the printer. Then format the report so that it fits the form and print only the data. Or you can use pre-printed forms. Preprinted forms were all we ever used in the days of the dot-matrix printer and on the mainframe.

Mail merge would be slower since that requires automating Word and you are still printing all the graphics on each page

If the reports don't actually have to be printed, then .pdf is the best solution. That will be significantly faster.

I'm not sure why you think it would be faster enough to justify this complexity to have some other application do the actual printing. The printing is the slow part, not the sending the data to be printed to the printer.

 

[The_Doc_Man]

Mechanically, if you have something to print on any page, even if you have pre-defined forms for most of it, your time will be spent shuttling paper via the pick-up wheels and printing the lines that have something on them. The invoice sample showed that you had enough on each line to make the printer move paper, move the shuttle, and cycle like that for a bunch of lines. The mechanical aspects of paper movement and the start/stop nature of lots of invoice-type lines means your savings will be limited anyway.

Your best investment is a fast laser printer where the stop/start & shuttling issues are almost nil. Then you don't care about whether you print the whole form or just fill in a blank, partly pre-printed form. The speed of your printer is going to make far more difference to this process than any kind of optimization we can describe to you regarding mechanical printing tips.

 

[Isaac]

The speed of your printer is going to make far more difference to this process than any kind of optimization we can describe to you regarding mechanical printing tips

Well said. Year before last I worked for a governmental agency related to taxes. Extremely large print jobs were the norm, since the audit department still worked on paper and an interface system hadn't yet been designed for them. I fussed around with Access reports-to-PDF as well as direct printing, both of which frequently locked up after a while due to the enormity of records being paged. Ultimately our solution was mostly just to get approval to use the 'high speed printer', which was a mechanism in a secure Server room about the size of a Volkswagen. As Pat mentioned, this machine also leveraged Xerox Forms, which I think in our case we only leveraged to direct the job to a particular Tray, but could have been actually designed much more.

 

[Hello1]

Thank you all for the answers, it helps to know more
They will do actual printing to paper, which this is most about. Some invoices will be exported to PDF which then will be sent with email to another companies and they will print and deliver the invoices to customers. However, my main concern right now is printing to paper, because they will use it a lot.

Unfortunately I cant get a new printer/s. A company is already making the pre-printed forms for them and all I have to do is add the data. All I thought is maybe that I might be able to print invoices on 2 printers at the same time but different invoices. Printing on 2 printers should be faster than only one.
My idea was to let the user choose which printer to use and which pages should be printed with it.
Lets say the user would first chose printer 1 and to print first 2000 (from page 1 to page 2000) pages with it. Now the VBA code which I would write is, to export those 2000 pages to a txt/csv file (lets name it text1.txt) and then use shell to send a command to printer 1 to print the text1.txt.
Immediately after that the user should be able to chose another 2000 pages with different data (from 2001 to 4000) to be printed with printer 2. So now I would export those another 2000 pages to text2.txt file and after that send the printing command with shell to printer 2.

If I start a printing job from MS Access report I have to wait for the printer to finish to be able to use Access again or thats not the case? (I dont have access to a printer right now to test this)
If thats not the case then as you said, it probably wont matter if I do the printing from a report or a txt file, the speed should be similar?
I just thought exporting to a .txt file would be faster and maybe printing too because the txt data should be smaller in size than a MS access report, because access still has to format all the reports before printing.

Im not quit into the printing methods yet, so I know most of my questions might not make any sense. Sorry for that
Thanks

 

[Isaac]

If I start a printing job from MS Access report I have to wait for the printer to finish to be able to use Access again or thats not the case?

Only until the job is finished being sent to the printer.
Given the very high volumes you are talking about, if it were me it would be well worth it to spend the time whipping up some tests using different methods. Personally I wouldn't totally write off mail merge until you test it--Access report objects can be grossly slow to render. Who knows how your speed will turn out.
Regarding all the comments about text and csv files, I'm unsure how you would leverage that specifically--I mean you don't want to just print something formatted like notepad or csv, do you? Your posted example screenshot looked very professionally laid out. It would seem you would need something to properly render the layout....either an Access report, a word mail merge, a printer Form (sounds like that's not an option for you), etc.

Perhaps you could come up with an interface that divides up the total records needing to be printed. Select a given group of records (maybe 1-500), and then incorporate that into the report's recordsource (along with resulting them as 'Printed' in a table somewhere). I wouldn't focus on pages--other than an initial bench-marking as to how many records equals about how many pages--I would focus on keeping track of records printed.

 

[Pat Hartman]

You've got the graphic aspects resolved by using a preprinted form.

Access is single threaded so there is no way you can start two print jobs at the same time. However as someone already said, Access is tied up only until it finishes spooling the report and for a large print job, that can be quite a while before the job is printed. So as soon as Access frees up, you can start the second job. Rather than having a person sit at attention and try to control this, you can write code that loops through the invoices to be printed and does the first 2000, when control returns to the app, the code loops and does the next 2000, etc.

Start your code by counting how many invoices will be printed so you can keep updating the form during the loop to show the progress. So if you have 10,000, then you can say printing 1-2,000 and update to printing 2001-4000, etc.

I don't know how much preprinted forms cost these days but in the olden days, they were expensive enough that they drove companies to buy faster printers with better features which is sort of like what happens when you raise the minimum wage too high. Your client might want to look into that. Spend 5k up front or spend 500 per month. Adds up quickly.

One other thing I learned about long print runs is to sequence all the printed forms. So print a number on each form that indicates its sequence. That way, if there is a mishap you can reprint 513-584 without doing the whole batch. I used a temp table to handle this because the whole selection process can be fluid (or it could be in my case if we ran the job during the day when people were updating). So the first step was to select the items to be printed and insert a row for each into the print table with a sequence number and the ID of the item being printed. Then you join to that table in your print runs to pick up the records you want so you don't have to keep counting.

Select ...
From ..
Where seqnum between forms!myform!startseq and forms!myform!endseq.

Your looping code just keeps updating the start/end values so you can use essentally the same code when running the reprint batches.

 

[arnelgp]

or simple use Access Report.
create an Image (bmp or png) out of the Template (without those numbers of course).
use the Image as background picture of the Report.

your "texts" goes to a Table. the table is the source of the report.
drag the fields you want to show on the report in design view.

 

[theDBguy]

Thank you all for the answers, it helps to know more
They will do actual printing to paper, which this is most about. Some invoices will be exported to PDF which then will be sent with email to another companies and they will print and deliver the invoices to customers. However, my main concern right now is printing to paper, because they will use it a lot.

Unfortunately I cant get a new printer/s. A company is already making the pre-printed forms for them and all I have to do is add the data. All I thought is maybe that I might be able to print invoices on 2 printers at the same time but different invoices. Printing on 2 printers should be faster than only one.
My idea was to let the user choose which printer to use and which pages should be printed with it.
Lets say the user would first chose printer 1 and to print first 2000 (from page 1 to page 2000) pages with it. Now the VBA code which I would write is, to export those 2000 pages to a txt/csv file (lets name it text1.txt) and then use shell to send a command to printer 1 to print the text1.txt.
Immediately after that the user should be able to chose another 2000 pages with different data (from 2001 to 4000) to be printed with printer 2. So now I would export those another 2000 pages to text2.txt file and after that send the printing command with shell to printer 2.

If I start a printing job from MS Access report I have to wait for the printer to finish to be able to use Access again or thats not the case? (I dont have access to a printer right now to test this)
If thats not the case then as you said, it probably wont matter if I do the printing from a report or a txt file, the speed should be similar?
I just thought exporting to a .txt file would be faster and maybe printing too because the txt data should be smaller in size than a MS access report, because access still has to format all the reports before printing.

Im not quit into the printing methods yet, so I know most of my questions might not make any sense. Sorry for that
Thanks

Hi. My only concern about pre-printed forms is making sure the text line up properly. It used to be worse with dot-matrix printers because misalignment could creep up and get way out of alignment in the long run. If you're using a laser printer, maybe it will be better than the old days - but it may still be a concern though. Good luck!