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
[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
: