Calling Access Report from Word - Run-time Error '462' (1 Viewer)

arkainus

New member
Local time
Today, 02:55
Joined
Apr 26, 2012
Messages
2
Hi everyone,

I'm new to these forums and to vba in general. I have run into an issue that I just can't seem to figure out. The first time I run this macro, it runs fine without issue. The next time I run it, I get an error of:

Run-Time error '462':

The Remote server machine does not exist or is unavailable

If I close word and re-open it, I can get the macro to kick in, but again only the first time I attempt to run the macro does it successfully work 100%. The rest of time it will intermittently spit that same error out to me.

Code:
Sub GenMCL()
'
' GenMCL Macro
' Generates Master Client List Report and Exports that to a PDF to a location of the users choice
'
    'On Error GoTo ProcError
    Dim objAccess As Object
    'Dim objDB As Object
    Dim sPathUser As String
    Dim currentDate As String
    Dim YesOrNoAnswerToMessageBox As String
    Dim QuestionToMessageBox As String
GetFilePath:
    currentDate = Format(Date, "mmddyyyy")
    sPathUser = BrowseFolder(Caption:="Select A Folder To Output The Report To")
 
    If Len(sPathUser) = 0 Then
    QuestionToMessageBox = "Cancel button detected. Did you mean to quit this program?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Quit Program")
        If YesOrNoAnswerToMessageBox = vbNo Then
            GoTo GetFilePath
        Else
            MsgBox "Ok, Goodbye."
            Exit Sub
        End If
    End If
 
    sPathUser = sPathUser & "\[" & currentDate & "] - Master Client List" & ".pdf"
 
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "G:[COLOR=lime]\\[/COLOR]Public\Access Data\MyDB.accdb"
 
   [COLOR=red]DoCmd.OutputTo acOutputReport, "Master Client List", acFormatPDF, sPathUser, True[/COLOR]
    objAccess.Quit
    Set objAccess = Nothing
    'MsgBox "Report Generated and Saved.", vbInformation
    Exit Sub
 
ProcError:
    MsgBox Err.Description, vbExclamation, "Generate Master Client List Report"
    MsgBox Err.Number
    Resume ProcExit
ProcExit:
    Exit Sub
 
End Sub

If I hit debug on the code, the line that is red above is the line that is highlighted as the offending code by the debugger. There is a report called exactly "Master Client List" in MyDB.accdb, if I take it through the debugger I see sPathUser definitely has a valid value. The reason for the line
Code:
objAccess.OpenCurrentDatabase "G:\\Public\Access Data\MyDB.accdb"

is because I saw a posting where another person suggested that the issue may be because G is a mapped drive and putting the extra slash in there would help. Unfortunately it made no appreciable difference in my case.

I am really stuck here. My assumption is that since it works the first time, something isn't getting closed properly, but that is simply a guess. I'm throwing this out here in the hopes that someone much smarter than me can help figure this out. Any help here would be greatly appreciated.

Thank you,

Chris
 

arkainus

New member
Local time
Today, 02:55
Joined
Apr 26, 2012
Messages
2
<link removed because my post count doesn't allow it to be included>QUOTE]

Thank you for your response. I went ahead and read the link. That doesn't seem to be exactly what I'm trying to accomplish, but I do notice that in his script he he has set objCommand = objAccess.DoCmd, something I don't do in my code. I jump straight into docmd.output right after assigning the proper database to be the current database. Is it better if I explicitly go that route? That one also has a line of: objAccess.CloseCurrentDatabase, where as I use objAccess.quit and then set objAccess to Nothing. I am going to lunch right now so I haven't modified my code yet, but is there a fundamental difference to objAccess.quit vs. objAccess.CloseCurrentDatabase? If so, should I just do objAccess.CloseCurrentDatabase rather than objAccess.Quit or should I do both, calling closecurrentdatabase first, then objAccess.Quit? Being new to VBA I am just hopefully looking for some clarification. Thank you again for your response, and for anyone else who may be able to help.
 

Users who are viewing this thread

Top Bottom