Need Help with Run-time Error '462' - Programmers Heaven

Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Need Help with Run-time Error '462'

darthmoobeydarthmoobey Posts: 140Member
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

  • darthmoobeydarthmoobey Posts: 140Member
    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
    :

  • ws1ows1o Posts: 5Member
    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!
  • ws1ows1o Posts: 5Member
    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!
  • ws1ows1o Posts: 5Member
    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!
  • ws1ows1o Posts: 5Member
    Thanks very much, The Darthnoob, using AccApp.Docmd instead of just Docmd solved a very annoying pproblem for me!
  • ws1ows1o Posts: 5Member
    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.