How to reconnect to network printer using vba

Hello,

A client running an Access 2007 application, reports that the print servers occasionally go down and then come back up.  When it does this, users of the access application have to reconnect to the printers.  Is there a way to do this via vba.  I have a created a table that lists  printers used for each access reports.  Since some of the reports use the same printers, I created a query that returns all the absolute path to all the distinct printers in use by the application.  I then use this query in the code below to loop through each file path.  I'm just trying to figure out how to connect to that printer once I retrieve it's file path.

Public Function ResetPrinters()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim intX As Integer
Dim strPrinterPath As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPrinters")
Set rst = qdf.OpenRecordset


rst.MoveLast
rst.MoveFirst

For rst = x To rst.RecordCount - 1
    strPrinterPath = rst(x)
    
Next

End Function

Open in new window

Juan VelasquezAsked:
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.

Juan VelasquezAuthor Commented:
Hello,

I've done some more research, and I think something like the code below may work. However, how do I check to see if a network printer is already connected.

Public Function ResetPrinters()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim X As Integer
Dim strPrinterPath As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPrinters")
Set rst = qdf.OpenRecordset


rst.MoveLast
rst.MoveFirst

For X = 0 To rst.RecordCount - 1
    strPrinterPath = rst(X)
    ConnectPrinter (strPrinterPath)
Next

End Function

Private Function ConnectPrinter(strPath As String)

 Dim WshNetwork As Object
 Set WshNetwork = CreateObject("WScript.Network")


 WshNetwork.AddWindowsPrinterConnection strPath



End Function
 

Open in new window

0
Juan VelasquezAuthor Commented:
I did some more work and I think the following may work. I'd like to get some opinions, as to whether I've missed anything

Public Function ResetPrinters()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim X As Integer
Dim strPrinterPath As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPrinters")
Set rst = qdf.OpenRecordset


rst.MoveLast
rst.MoveFirst

For X = 0 To rst.RecordCount - 1
    strPrinterPath = rst(X)
    ConnectPrinter (strPrinterPath)
Next

End Function

Private Function ConnectPrinter(strNetworkPrinterPath As String)
'Declaring variables - Declare everything!
 ' This ensures we only use variables that we
 ' mean to - do not modify.
 ' ==========================================

    Dim WshNetwork As Object
    Dim wScript As Object
    Dim strPrinterStatus As String

    Set WshNetwork = CreateObject("WScript.Network")
    Set wScript = CreateObject("WSH.WScript")
 
    If IsEmpty(strNetworkPrinterPath) Then
        wScript.Echo "Failed to find printer, please check your printer service 'Spooler' is running."
    Else
        WshNetwork.AddWindowsPrinterConnection strNetworkPrinterPath
        If Err.Number = 0 Then
            strPrinterStatus = "Connected"
        Else
            strPrinterStatus = "Unconnected"
        End If
        wScript.Echo "Name :" & vbTab & strNetworkPrinterPath & vbCrLf & "Connected Status : " & strPrinterStatus
       
    End If


End Function
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
Juan VelasquezAuthor Commented:
Thanks Jim.
0
Juan VelasquezAuthor Commented:
Sorry for the delay in posting my solution.
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.