Need Help with Run-time Error '462'

Hello, I am importing an Excel Spreadsheet into a Microsoft Access Database. The first initial moment I import it works fine, but when I try to import again I get the error message

Run-time error '462':
The remote server machine does not exist or is unavailable.

I researched on MSDN and found an article that explained this.

CAUSE
Visual Basic has established a reference to Excel due to a line of code that calls an Excel object, method, or property without qualifying it with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than once.
Back to the top Back to the top
RESOLUTION
Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable.

Now this was great to know, but I couldn't quite understand how the resolution was explaining itself. How do I modify my code to keep from that error popping out and not needing to shut down my program every time I want to append an excel spread sheet to hte database through import?

[code]
Dim strpath As String

strpath = DirectoryList.Path & _
IIf(Right$(DirectoryList.Path, 1) = "", "", "") & ListFile.FileName

Dim objAccApp1 As Access.Application
Dim TableName As String
TableName = "CentexInfo"

Set objAccApp1 = GetObject("x:databasesCentex.mdb")

'objAccApp1.Visible = False

'DoCmd.DeleteObject acTable, "CentexInfo"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, strpath, True <-- This is where I get the error

objAccApp1.Quit
Set objAccApp1 = Nothing
[/code]

Thank you veyr much
The DarthMoob

Comments

  • Nevermind everyone, I did some more advanced reading and research and found the problem. It appears with VB controls like such in reference to Office Programs VB uses a hidden global variable for object commands if not specified. I had to adjust my code liek such

    [code]

    objAccApp1.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, strpath, True

    [/code]

    I needed to add objAccApp1 before my DoCmd so that VB would not assign it to the hidden global variable. As such I would need to close my entire program, in order to use that command again. By specifying the variable object, it does not use the global variable and destroys the command variable after use. Again thank you for anyone looking into helping me, and I hope this problem helps anyone else reading along on here.

    The Darthmoob

    : Hello, I am importing an Excel Spreadsheet into a Microsoft Access Database. The first initial moment I import it works fine, but when I try to import again I get the error message
    :
    : Run-time error '462':
    : The remote server machine does not exist or is unavailable.
    :
    : I researched on MSDN and found an article that explained this.
    :
    : CAUSE
    : Visual Basic has established a reference to Excel due to a line of code that calls an Excel object, method, or property without qualifying it with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than once.
    : Back to the top Back to the top
    : RESOLUTION
    : Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable.
    :
    : Now this was great to know, but I couldn't quite understand how the resolution was explaining itself. How do I modify my code to keep from that error popping out and not needing to shut down my program every time I want to append an excel spread sheet to hte database through import?
    :
    : [code]
    : Dim strpath As String
    :
    : strpath = DirectoryList.Path & _
    : IIf(Right$(DirectoryList.Path, 1) = "", "", "") & ListFile.FileName
    :
    : Dim objAccApp1 As Access.Application
    : Dim TableName As String
    : TableName = "CentexInfo"
    :
    : Set objAccApp1 = GetObject("x:databasesCentex.mdb")
    :
    : 'objAccApp1.Visible = False
    :
    : 'DoCmd.DeleteObject acTable, "CentexInfo"
    :
    : DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, strpath, True <-- This is where I get the error
    :
    : objAccApp1.Quit
    : Set objAccApp1 = Nothing
    : [/code]
    :
    : Thank you veyr much
    : The DarthMoob
    :

  • Thanks, [b][italic][color=Red]The Darthnoob[/color][/italic][/b], using [b]AccApp.Docmd [/b]instead of just [b]Docmd [/b]solved a very annoying and persistent error I was seeing!
  • Thanks, [b][italic][color=Red]The Darthnoob[/color][/italic][/b], using [b]AccApp.Docmd [/b]instead of just [b]Docmd [/b]solved a very annoying and persistent error I was seeing!
  • Thanks, [b][italic][color=Red]The Darthnoob[/color][/italic][/b], using [b]AccApp.Docmd [/b]instead of just [b]Docmd [/b]solved a very annoying and persistent error I was seeing!
  • Thanks very much, The Darthnoob, using AccApp.Docmd instead of just Docmd solved a very annoying pproblem for me!
  • Thanks very much, The Darthnoob, using AccApp.Docmd instead of just Docmd solved a very annoying pproblem for me!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories