MS Access VBA refresh printers in Application object

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
LVL 1
jackbensonAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
jackbensonAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
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.

Nick67Commented:
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
jackbensonAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

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
jackbensonAuthor Commented:
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
jackbensonAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you took my suggestion, you should accept my comment as your solution.
0
jackbensonAuthor Commented:
sorry for clicking the wrong comment!

thanks for your help

solution was perfect
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.