Solved

MS Access VBA refresh printers in Application object

Posted on 2014-11-24
10
287 Views
Last Modified: 2015-01-02
Hi,

I have an MS Access application that I want to provision over remoteapp.

this works fine - but the problem I am having is with the printers.

I preset certain reports to use certain printers - and use the following code to get the printers that are available to the application:

If Printers.Count > 0 Then
        ' Get count of installed printers.
        strMsg = "Printers installed: " & Printers.Count & vbCrLf & vbCrLf
   
        ' Enumerate printer system properties.
        For Each prtLoop In Application.Printers
            With prtLoop
       
                PrinterID = AddPrinter(.DeviceName, .DriverName, .Port)
                strMsg = strMsg _
                    & "Printer ID: " & PrinterID & vbCrLf _
                    & "Device name: " & .DeviceName & vbCrLf _
                    & "Driver name: " & .DriverName & vbCrLf _
                    & "Port: " & .Port & vbCrLf & vbCrLf
            End With
        Next prtLoop
   
    Else
        strMsg = "No printers are installed."
    End If

this works fine

the problem that I have is that when the remoteapp session starts - not all the remote printers are immediately available to the remoteapp and so never appear in the list of printers accessible to the application.

if there a way to refresh this list of printers?

if I go to File.. Print - the remote printers eventually appear.

thanks

jack
0
Comment
Question by:jackbenson
  • 5
  • 4
10 Comments
 
LVL 84
Comment Utility
We had a recent question like this, and the answer was, essentially, no - you can't get the list of printers until they are available, and that is dependent on the OS - or in your case, the RDS server.

You could use the hidden form method, where you have a form that starts when the app starts and is hidden, and you could use a form timer event to run that code until it finds no new ones. Essentially, you store the names of all the printers you find, and then you run through your code until you cannot add any new ones to that list.
0
 
LVL 1

Author Comment

by:jackbenson
Comment Utility
thanks for your reply

I have a button in my application that runs the above code when pressed - but it will not find new printers.

what code do you use to find new printers? nothing seems to get added to Application.Printers

thanks

jack
0
 
LVL 84
Comment Utility
Just so that we understand what you mean:

When you start your Access database using RemoteApp, you do NOT see the full list of printers when initially using File - Print. However, after a few minutes or so, if you use File - Print you WILL see the list of printers, but you cannot see them using Application.Printers?

If so, then I'm not sure how you'd refresh the list of printers in your Access.Application. There is no Refresh method for that property. Perhaps someone else will know ...
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Conceivably it's your code that is the problem
If Printers.Count > 0 Then
         ' Get count of installed printers.

Why the bare Printers here?
Later, in the loop, you do it properly
       For Each prtLoop In Application.Printers
             With prtLoop


I muck with the Appilcation.Printer extensively in Access 2003.
Since that version cannot render objects to PDF, you have to shift the Application.Printer object to a PDF queue, and then render it.

My code for that is as follows
Dim OldDefaultPrinterName As String
Dim NewPrinterName As String
Dim OldDefaultPrinterIndex As Integer
Dim NewPrinterIndex As Integer
Dim x As Integer

'Get the existing printer name to set the app default back to later
OldDefaultPrinterName = Application.Printer.DeviceName
NewPrinterName = "Adobe PDF"

Dim prtLoop As Printer
x = 0
For Each prtLoop In Application.Printers
    With prtLoop
        If .DeviceName = OldDefaultPrinterName Then
            OldDefaultPrinterIndex = x
        ElseIf .DeviceName = NewPrinterName Then
            NewPrinterIndex = x
        End If

    End With
    x = x + 1
Next prtLoop

'now change the printer to adobe pdf and print
Set Application.Printer = Application.Printers(NewPrinterIndex)
Set Application.Printer = Application.Printers("Adobe PDF")

'code to render the object goes here


'then change the printer back
Set Application.Printer = Application.Printers(OldDefaultPrinterIndex)

Open in new window


Now, you may look at that and say hey aren't doing the same thing?
Set Application.Printer = Application.Printers(NewPrinterIndex)
Set Application.Printer = Application.Printers("Adobe PDF")

They are supposed to be, but I don't always have good results using just the one statement

So, to test whether your code isn't firing, try removing the condtional
' Enumerate printer system properties.
         strMsg = "No printers"
         For Each prtLoop In Application.Printers
             With prtLoop
                 strMsg = .DeviceName
             End With
             msgbox strMsg
         Next prtLoop


If that works, then your conditional at the beginning is where the problem lies.

Onward:
This is certainly not available in A2003
AddPrinter(.DeviceName, .DriverName, .Port)
Where is that coming from?
0
 
LVL 1

Author Comment

by:jackbenson
Comment Utility
sorry - AddPrinter(.DeviceName, .DriverName, .Port) - this is a method I created to save the details about the printer to my database -

the solution that I found was to public MS Access as the remote app not the database file - then the user can open the database file after 30 seconds once all the printers are available
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
One other method might be to create a "launcher app", and set that as the published application. Create a single form that runs a timer for as long as is needed for that RDS session to fully populate the printers collection, and then launch your other application. If you're depending on users to wait 30 full seconds (a LIFETIME in the computer world), you can bet you're gonna get support calls on that one :).
0
 
LVL 1

Author Comment

by:jackbenson
Comment Utility
that's - creating a launcher MS Access Database worked perfectly.

if this helps anyone - I created 1 form in the Database - and this was my VBA code

Option Compare Database
Dim ApplicationPrinterCount As Integer
Dim RemoteSessionsPrinterCount1 As Integer
Dim RemoteSessionsPrinterCount2 As Integer
Dim RemoteSessionsPrinterCount3 As Integer
Dim RemoteSessionsPrinterCount4 As Integer
Dim RemoteSessionsPrinterCount5 As Integer
Dim Counter As Integer
Dim OpenedGuideSystem As Boolean




Private Sub btnOpenStockSystem_Click()
    On Error GoTo btnOpenStockSystem_Err
    'Application.Visible = False
    
    Me.TimerInterval = 0
    RunCommand acCmdAppMinimize
    Dim accapp As Access.Application
    Set accapp = New Access.Application
    accapp.OpenCurrentDatabase ("C:\APPLICATIONS\ACCESS\GUIDE\00-SQL\db 1.9.96.mdb")
    accapp.Visible = True
    
    
    'Access.CloseCurrentDatabase
    'Access.OpenCurrentDatabase ("C:\APPLICATIONS\ACCESS\GUIDE\00-SQL\db 1.9.96.mdb")
    'Application.CloseCurrentDatabase
    'Application.NewCurrentDatabase ("C:\APPLICATIONS\ACCESS\GUIDE\00-SQL\db 1.9.96.mdb")
    'DoCmd.RunCommand acCmdOpenDatabase, "C:\APPLICATIONS\ACCESS\GUIDE\00-SQL\db 1.9.96.mdb"
    
btnOpenStockSystem_End:
    Application.Quit
    Exit Sub
    
btnOpenStockSystem_Err:
    Resume btnOpenStockSystem_End
End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Load()
  
    
    ApplicationPrinterCount = Printers.Count
    
    lblApplicationPrinters.Caption = ApplicationPrinterCount
    
    RemoteSessionsPrinterCount1 = -1
    RemoteSessionsPrinterCount2 = 0
    Me.TimerInterval = 3000
    Counter = 0
    OpenedGuideSystem = False
    
    Dim sqlStatement As String
    sqlStatement = "DELETE FROM tblPrinters"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlStatement
    DoCmd.SetWarnings True

End Sub

Private Sub Form_Timer()
    On Error GoTo Form_Timer_Err
    
    Counter = Counter + 1
    

              
    Dim NewApplication As New Access.Application
   
    lblPrinterCount.Caption = NewApplication.Printers.Count
    lblApplicationPrinters.Caption = ApplicationPrinterCount


    Dim index As Integer
    
    Dim prtAvailPrinters As Printer
    For Each prtAvailPrinters In NewApplication.Printers
        With prtAvailPrinters
            Debug.Print "Printer name: " & .DeviceName & "Printer driver: " & .DriverName
            
            Dim rstPrinters As DAO.Recordset
            Set rstPrinters = CurrentDb.OpenRecordset("SELECT PrinterID FROM tblPrinters Where tblPrinters.PrinterName = '" & .DeviceName & "'", dbOpenDynaset, dbSeeChanges)
            If rstPrinters.RecordCount = 0 Then
            
                Dim sqlStatement As String
                sqlStatement = "INSERT INTO tblPrinters(PrinterName) VALUES ('" & .DeviceName & "')"
                DoCmd.SetWarnings False
                DoCmd.RunSQL sqlStatement
                DoCmd.SetWarnings True
            End If
            rstPrinters.Close
            Set rstPrinters = Nothing
        End With
    Next prtAvailPrinters

    Me.Form!PrintersList.Requery

        
'        Loop
    
        lblCounter.Caption = Counter

        If Counter = 1 Then RemoteSessionsPrinterCount1 = NewApplication.Printers.Count

        If Counter = 2 Then RemoteSessionsPrinterCount2 = NewApplication.Printers.Count

        If Counter = 3 Then RemoteSessionsPrinterCount3 = NewApplication.Printers.Count

        If Counter = 4 Then RemoteSessionsPrinterCount4 = NewApplication.Printers.Count

        If Counter = 5 Then RemoteSessionsPrinterCount5 = NewApplication.Printers.Count

        If Counter > 5 Then
            RemoteSessionsPrinterCount1 = RemoteSessionsPrinterCount2
            RemoteSessionsPrinterCount2 = RemoteSessionsPrinterCount3
            RemoteSessionsPrinterCount3 = RemoteSessionsPrinterCount4
            RemoteSessionsPrinterCount4 = RemoteSessionsPrinterCount5
            RemoteSessionsPrinterCount5 = NewApplication.Printers.Count
        End If
'
        NewApplication.Quit
        
        
'
'        If (Counter >= 15) And (RemoteSessionsPrinterCount1 = RemoteSessionsPrinterCount2) And (RemoteSessionsPrinterCount2 = RemoteSessionsPrinterCount3) And (RemoteSessionsPrinterCount3 = RemoteSessionsPrinterCount4) And (RemoteSessionsPrinterCount4 = RemoteSessionsPrinterCount5) Then
'

'
'        End If
'
Form_Timer_End:
   
    Exit Sub
    
Form_Timer_Err:
    Resume Form_Timer_End
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:jackbenson
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for jackbenson's comment #a40526900

for the following reason:

I did as you suggested and all worked
0
 
LVL 84
Comment Utility
If you took my suggestion, you should accept my comment as your solution.
0
 
LVL 1

Author Closing Comment

by:jackbenson
Comment Utility
sorry for clicking the wrong comment!

thanks for your help

solution was perfect
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

7 Experts available now in Live!

Get 1:1 Help Now