MS Access Remote Server Connections

[mustardxhead]

I have my Access database create a word document and add a series of tables and other text data to it. This works perfectly for the first run of the code. On the second run I get an error with a remote machine server is unavailable or busy. I have done some investigation on this and found that this is due to some sort of client/server connection between the applications and a direct reference to the word document helps to clear this issue and corrected it in some of my code. Other parts of the code I am not so sure how to do this. I am currious if there is some way to discover and assign the reference to a variable and close it manually as the code finishes execution due to the fact that Microsoft was not able to manage this when they made the applications.

 

[Ed2020]

Can you post the code you have that isn't working correctly?

Ed Metcalfe.

Please do not feed the trolls.....

 

[mustardxhead]

here is the portion of code that builds the word document. All of the variables are declared properly in the declarations section. There is a note in the code indicating where it crashes the lower half of the code was commented out for debugging purposes. Feel free to criticize the code, this is my first attempt at VBA

Sub buildWordDoc()
    
    '  header value
    '********************************************************************************
    header = "My Document Header"
    
    
    'Builds word application and document
    '********************************************************************************
    Set setupWordApp = CreateObject("Word.Application")
    Set setupReport = setupWordApp.Documents.Add
    setupReport.Select
    setupReport.PageSetup.LeftMargin = 60
    setupReport.PageSetup.RightMargin = 60
    setupWordApp.Visible = True
    
    'Sets Font information and inserts header
    With setupWordApp.Selection
    
        .Font.Size = 12
        .Font.Name = "Elephant"
        .Font.Color = wdColorDarkBlue
        .ParagraphFormat.Alignment = wdAlignParagraphCenter
        .TypeText header
    End With
    setupWordApp.Selection.TypeParagraph
    setupWordApp.Selection.TypeParagraph
    With setupWordApp.Selection
        .Font.Bold = True
        .Font.Name = "Tahoma"
        .Font.Color = wdColorBlack
        .ParagraphFormat.Alignment = wdAlignParagraphLeft
        .TypeText "User Information:"
    End With
    setupWordApp.Selection.TypeParagraph
   
   

    
    'Build MSWord Table "User Information"
    '********************************************************************
    
    setupWordApp.Selection.Tables.Add Selection.Range, 2, 3, wdWord9TableBehavior, wdAutoFitFixed
    setupWordApp.Selection.MoveDown wdScreen, 1
    setupWordApp.Selection.TypeParagraph
   
    With setupWordApp.Selection
        .Font.Bold = True
        .Font.Name = "Tahoma"
        .Font.Color = wdColorBlack
        .ParagraphFormat.Alignment = wdAlignParagraphLeft
        .TypeText "Computer Information:"
    End With
    setupWordApp.Selection.TypeParagraph
  
 
    'Build MSWord Table "PC Information"
    '*********************************************************************
 
    setupWordApp.Selection.Tables.Add Selection.Range, 4, 3, wdWord9TableBehavior, wdAutoFitFixed 'builds a table
    setupWordApp.Selection.MoveDown wdScreen, 1 'moves to end of document (pg down)
 
[COLOR=red]<<< This is a note to indicate where the code breaks >>> [/color]
    setupWordApp.Selection.Tables(1).Select
    setupWordApp.Selection.Collapse wdCollapseStart
    With setupWordApp.Selection
        .Font.Name = "Garamond"
        .TypeText "User ID: " & UserID
        .MoveRight
        .Font.Name = "Garamond"
        .TypeText "Name: " & userFirstName & " " & userLastName
        .MoveRight
        .Font.Name = "Garamond"
        .TypeText "Setup Tech: " & technician
        .MoveDown
        .MoveLeft
        .MoveLeft
        .Font.Name = "Garamond"
        .TypeText "Date Assigned: "
        .MoveRight
        .Font.Name = "Garamond"
        .TypeText "Date Due: "
        .MoveRight
        .Font.Name = "Garamond"
        .TypeText "Location: " & Location
 
 
    End With


   ' setupReport.Tables(2).Select 'selects Tables(n) in active document
   ' Selection.Collapse wdCollapseStart 'set insertion point to first block
   ' With Selection
   '     .Font.Name = "Garamond"
   '     '.Font.AllCaps = True
   '     .TypeText "Manufacturer: " & manufacturer
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Model: " & Model
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Service Tag: " & ServiceTag
   '     .MoveDown
   '     .MoveLeft
   '     .MoveLeft
   '     .Font.Name = "Garamond"
   '     .TypeText "Name: " & computerName
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Image: " & imageUsed
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "O/S: " & oSys
   '     .MoveDown
   '     .MoveLeft
   '     .MoveLeft
   '     .Font.Name = "Garamond"
   '     .TypeText "CPU: " & processor
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Memory: " & RAM
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Style: " & fFactor
   '     .MoveDown
   '     .MoveLeft
   '     .MoveLeft
   '     .Font.Name = "Garamond"
   '     .TypeText "Asset Tag: " & assTag
   '     .MoveRight
   '     .Font.Name = "Garamond"
   '     .TypeText "Upgrade Type: " & upgradeType
   '
   ' End With
   ' setupReport.Select
   '
   '
   '
   ' 'Selection.MoveDown wdScreen, 1
   ' 'Selection.Range.MoveEnd
   '
   ' 'num = setupReport.Sections.Count
   ' 'setupReport.Sections(num).Range.Select
   ' Selection.Collapse wdCollapseEnd
   ' Selection.TypeParagraph
   ' 'Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormCheckBox
   ' Selection.Font.Size = 12
   ' Selection.Font.Name = "Garamond"
   ' Selection.TypeText " Set Computer Name To " & computerName
   '
   '
   ' Selection.TypeText ("end")
   '
   '     'Saves Report as temporary document for testing and closes the Word Application
   ' '****************************************************************************
    setupReport.SaveAs ("c:\Test.doc")
    setupWordApp.Quit
    Set setupReport = Nothing
    Set setupWordApp = Nothing
    
    
    
    
    'Starts Word and opens the test document
    '****************************************************************************
    'Shell ("c:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE c:\test.doc")

 

[PHV]

setupWordApp.Selection.Tables.Add [!]setupWordApp.[/!]Selection.Range, 2, 3, wdWord9TableBehavior, wdAutoFitFixed
...
setupWordApp.Selection.Tables.Add [!]setupWordApp.[/!]Selection.Range, 4, 3, wdWord9TableBehavior, wdAutoFitFixed 'builds a table
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mustardxhead]

This handles the server error, however it creates a new error that I am not so sure of. It seems that the first table information gets lost.

Selects the second table in the document, and if I put it before the creation of the second table it comes up with a not found message.  Anyone see what I am missing?

TIA

 MxH