asked on

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

Scott McDaniel (EE MVE )

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.

jackbenson

ASKER

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

Scott McDaniel (EE MVE )

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

Nick67

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?

jackbenson

ASKER

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jackbenson

ASKER

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

jackbenson

ASKER

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

Scott McDaniel (EE MVE )

If you took my suggestion, you should accept my comment as your solution.

jackbenson

ASKER

sorry for clicking the wrong comment!

thanks for your help

solution was perfect