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.
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
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:databasesCentex.mdb")
'objAccApp1.Visible = False
'DoCmd.DeleteObject acTable, "CentexInfo"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, strpath, True <-- This is where I get the error
Set objAccApp1 = Nothing
Thank you veyr much