Link to home
Start Free TrialLog in
Avatar of jackbenson
jackbensonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS Access VBA refresh printers in Application object


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
        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.


Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
Avatar of jackbenson


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


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 ...
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.

This is certainly not available in A2003
AddPrinter(.DeviceName, .DriverName, .Port)
Where is that coming from?
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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.OpenCurrentDatabase ("C:\APPLICATIONS\ACCESS\GUIDE\00-SQL\db 1.9.96.mdb")
    '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"
    Exit Sub
    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
            Set rstPrinters = Nothing
        End With
    Next prtAvailPrinters


'        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
'        If (Counter >= 15) And (RemoteSessionsPrinterCount1 = RemoteSessionsPrinterCount2) And (RemoteSessionsPrinterCount2 = RemoteSessionsPrinterCount3) And (RemoteSessionsPrinterCount3 = RemoteSessionsPrinterCount4) And (RemoteSessionsPrinterCount4 = RemoteSessionsPrinterCount5) Then

'        End If
    Exit Sub
    Resume Form_Timer_End
End Sub

Open in new window

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
If you took my suggestion, you should accept my comment as your solution.
sorry for clicking the wrong comment!

thanks for your help

solution was perfect