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
objAccApp1.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, strpath, True
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?
:
:
: 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:\databases\Centex.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
:
:
: Thank you veyr much
: The DarthMoob
:
