Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / July 2005

Tip: Looking for answers? Try searching our database.

VBA Automation - 462 Error - Server Unavailable

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
John Bigness - 22 Jul 2005 20:21 GMT
From Access, I am running Word and doing a Mail Merge - automated with VBA.  
First time it runs fine.  Second time I get a 462 Error - "The automation
server is unavailable".  After that I close the Access app and all the Word
apps and start over. First try is good.  Each one after that is bad.

Is there any way to avoid having to restart the Access application in order
to have the same procedure work again?
david@epsomdotcomdotau - 24 Jul 2005 09:03 GMT
You are doing something wrong.  Have a look at your code
to see what you are doing that is making the automation
server unavailable.

(david)

> From Access, I am running Word and doing a Mail Merge - automated with VBA.
> First time it runs fine.  Second time I get a 462 Error - "The automation
[quoted text clipped - 3 lines]
> Is there any way to avoid having to restart the Access application in order
> to have the same procedure work again?
John Bigness - 25 Jul 2005 16:23 GMT
Here is the code I am using if you care to take a look.  I work around the
error by closing the Access application.  Thanks.

Private Sub PrvLtrBtn_Click()

   ' Preview QA selected Case letter in MS Word and merge data per merge
Fields in letter
   ' Data viewed in form comes from QALetter [a table made by running
QALetterQ],
   '   QALetter [table] is also used for merge to MSWord doc
   
   'Dim stAppName As String
   Dim FileX As String
   
   Dim mydbase As Database
   Dim rs As Recordset
   Dim today As Date
   
   today = Date
 
'New code to Warn user that file will close when procedure complete
Dim intResponse As Integer

msg = "A Microsoft Word Mail Merge Document will be created and saved. At
the end of the procedure this application will close."
msg = msg & " Do you wish to Proceed? Press Yes to proceed or No to abort
this process."

intResponse = MsgBox(msg, vbQuestion + vbYesNoCancel + vbDefaultButton2,
"Run Mail Merge Process")

'if user does not respond Yes then abort this procedure
If intResponse <> vbYes Then
   MsgBox "You have opted not to proceed! Mail Merge Cancelled."
   Exit Sub
End If
'
   
   Dim varX As String   ' ContactDocW - Selected FormLetter from
ContactType table
   Dim varY As String   ' ContactCode of selected letter "##"
                         '     which is also first two characters of
filename of .doc
   
   Dim varZ As String
   
   Dim docDate As Date
   Dim QACaseName As String
   Dim WordApp As Word.Application

   
   Dim WordDoc As Word.Document
   Dim strDoc As String
   
   ' From QALetterForm.LetterCombo
   '   open ContactType.ContactDocW (MSWord doc) as selected by user
   
 
       
       DoCmd.SetWarnings False
       DoCmd.OpenQuery "QALetterQ3"
       DoCmd.SetWarnings True


   ' Lookup filename of form letter to open [varX] and code of form letter
[varY]
   varX = DLookup("[ContactDocW]", "ContactType", "[ContactScope] =
Forms!QALetterForm!LetterCombo")
   varY = DLookup("[ContactCode]", "ContactType", "[ContactScope] =
Forms!QALetterForm!LetterCombo")

On Error GoTo OpenError

Set mydbase = CurrentDb
Set rs = mydbase.OpenRecordset("QALetter")
varZ = rs![QA1-Last]

FileX = Format(varY, "00") & varZ & Format(Date, "mmmddyy") & ".doc"

DoCmd.SetWarnings False
Set WordApp = New Word.Application
Set WordDoc = New Word.Document
   strDoc =
"\\Msccfs0a1aa\apps\OCFSIT_Applications\Development_Prevention_Services\FTHA\"
   strDoc = strDoc & "QALetters.dot\" & varX

Set WordDoc = WordApp.Documents.Open(strDoc)
WordApp.Visible = True

DoCmd.SetWarnings True
With WordApp
       
       ActiveDocument.MailMerge.OpenDataSource Name:= _
           
"\\Msccfs0a1aa\apps\OCFSIT_Applications\Development_Prevention_Services\FTHA\Test\QACases.mdb", _
           LinkToSource:=True, _
           Connection:="TABLE QALetter", _
           SQLStatement:="SELECT * FROM [QALetter]"
       With ActiveDocument.MailMerge
           .Destination = wdSendToNewDocument
           .Execute
       End With
   ' Would like code to automatically 'Save' MSWord doc
   '  to users folder [H:\GG1748\QADocumentation] on the network
'fnpcfs0a1ac'
   '       ChangeFileOpenDirectory strDoc & "QACaseLettersSent\"
   strDoc =
"\\Msccfs0a1aa\apps\OCFSIT_Applications\Development_Prevention_Services\FTHA\"
   strDoc = strDoc & "QACaseLettersSent"
   ChangeFileOpenDirectory (strDoc)
 
   ActiveDocument.SaveAs FileName:=FileX
Windows(varX).Activate
DoCmd.SetWarnings False
ActiveDocument.Close
ActiveDocument.Close

End With
WordApp.Quit

'DoCmd.SetWarnings False

Set WordApp = Nothing
Set WordDoc = Nothing
rs.Close
MsgBox "Mail Merge Complete. New Letter is saved in the QACaseLettersSent
folder.  Application will now close."
Application.Quit
Exit Sub

OpenError:
MsgBox "Application Error - please close application, restart & try again "
& Err.Number

Set WordApp = Nothing
Set WordDoc = Nothing

End Sub

> You are doing something wrong.  Have a look at your code
> to see what you are doing that is making the automation
[quoted text clipped - 12 lines]
> order
> > to have the same procedure work again?
david epsom dot com dot au - 26 Jul 2005 01:09 GMT
Word hasn't closed correctly.  Probably because it hadn't
finished saving the files when it received the .Quit command.

When you try create the next Word Object, the automation
server, which is still running, has problems creating the
new object.

To avoid this problem, turn off background saves, and don't
Quit the word application. Instead, create a word application
object and use it until your program finishes.

The .word automation newsgroup would probably be a better
place to ask questions like this.

(david)

> Here is the code I am using if you care to take a look.  I work around the
> error by closing the Access application.  Thanks.
[quoted text clipped - 150 lines]
>> order
>> > to have the same procedure work again?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.